My teacher returned my ERD! I need more then two tables, or "you don't need a database!"

SO What I did was this:

I had the two tables but I took hobbies 1 and 2 out of the People table and created a third table.

People
PersonID - Primary Key
Name
HmAddress1
HmAddress2
HmAddress3
HmCity
HmState
HmZip
HmPhone
CellPhone
WkPlace
WkAddress1
WkAddress2
WkAddress3
WkCity
WkState
WkZip
WkPhone
FavoriteColor
Comments

Event
PersonID - Primary Key
EventName
EventMonth
EventDay
EventYear

Hobby
PersonID - Primary key
Hobby

Is this right? I feel like I am being taught backwards! We know nothing of the database language or setup and yet we have to come up with a proposal! It makes no sense! This is already passed due and the last day of class is officially Sunday, I have to have it by then and I am no where near ready! I am ready to scream! SOMEONE...ANYONE...HELP!

the primary keys in the Event and Hobby tables are wrong

if PersonID is the primary key, there can be only one row per person, and clearly, that's not right, because the whole purpose of splitting them off is to allow a one-to-many relationship

You may have to clarify just a little. I am flying blind on this! So should I make the primary key in Event, EventID and HobbyID for Hobby?

May I post my favourite Link? Rudy might know this one:
The Link

pootle flump ur codings are working excelent.

Ok that link helped, I think. Thanks! Ok I attached my new ERD, so this is either really wrong or really right. The one thing I am not sure about is the relationships for the new tables I created. The People to Address and Work Address tables, I put as a 1 to 1 relationship. I read the rules on it and I am thinking it is not right, but what do you think? I was thinking one to one because one person will only have one work and home address. But according to the rules you can have one row for every row in the first table. Not sure! And I put every row in WkAddress except the primary key as allowed to be null. Can you do that for a whole table?

Also I added PersonID which is the primary key in People as a Foriegn Key in all the tables. Is that right?

I have not updated the normalization yet, want to make sure the tables are right first.

Neither But it is getting better.

Consider your new hobby table and the Third Normal Form. There is an example just like this in The Link

pootle flump ur codings are working excelent.

OHHHHHHHHH! See I am catching on!! So I could have a Hobbies table with HobbyID as the primary key and then description. Then change my Hobby table to pull HobbyID and PersonID ? Am I on the right track? You may have to dumb it down for me!!!!

ok I am confusing myself again!! should I have two tables for hobby? One with id and description? Or should I add hobbyID to the people table?

Your hobbies table should have:

HobbyID
PersonID,
HobbyName

HobbyID is optional. It is a surrogate key, meaning it is an artificial value used to uniquely identify the record.
PersonID and HobbyName together form the natural key, meaning no person may have two hobbies of the same name, though a person may have more than one hobby and a hobby may be shared by more than one person.

PersonID is your link from the People table to the Hobbies table in a one-to-many relationship. You do not need, and should not have, a HobbyID column in your person table.

Ok, that helps me some more! I am going to take a break, I have been at this all day! I am going to a friends house to actually make this database of mine in access, she seems to think it will help me understand it. I am willing to try anything! I will post what I have later to see if it is correct!

Thanks!!!

People
PersonID - Primary Key
Name
HmAddress1
HmAddress2
HmAddress3
HmCity
HmState
HmZip
HmPhone
CellPhone
WkPlace
WkAddress1
WkAddress2
WkAddress3
WkCity
WkState
WkZip
WkPhone
FavoriteColor
Comments

Event
EventID - Primary Key (artificial to uniquely designate an Event)
PersonID - Foreign Key (one to many relationship with PersonID in People - i.e. many Events (same PersonID can be repeated) to 1 PersonID in People)
EventName
EventMonth
EventDay
EventYear

Hobby
HobbyID - Primary Key (artificial to uniquely identify a Hobby)
PersonID - Foreign key (one to many relationship with PersonID in People - i.e. many Hobbies (same PersonID can be repeated) to 1 PersonID in People)
Hobby

Now if you don't want 1 Person (PersonID) to have the same Hobby listed twice in the Hobby table, You instead establish a clustered Primary Key where PersonID and Hobby are clustered together as the Primary key - HobbyID thus becomes just your Record Identifier (autonumber/surrogate key - not a Primary Key) for that table (nothing more) and the rules (called Primary key constraints) will then never let you have the same PersonID and same Hobby in 2 records. Same with the events table if you don't want 1 Person (PersonID) to have 2 of the same EventNames. Foreign key is used for terminology saying it is related to PersonID in a "foreign" sense. In the actual relationship diagram (say you're using MSAccess), you would draw a link from PersonID in the People table to PersonID in the Hobby table (and another link from PersonID in the People table to PersonID in the Event table.) You would click on that link (in MSAccess) or just designate in whatever db you're using and make it as a 1 to many relationship with the 1 side being the PersonID in the People table.

So as an example:

People table
PersonID = 3
Name = John Smith
etc...

Hobby table
HobbyID = 1
PersonID = 3
Hobby = Computers

Hobby table
HobbyID = 2
PersonID = 3
Hobby = Crafting

Not Allowed with a clustered Primary Key on PersonID and Hobby would be trying to add another record like:
HobbyID = 3
PersonID = 3
Hobby = Crafting

(Notice that you can't have the same PersonID and Hobby in the Hobby table - you would get an error trying to add the above record stating something to the effect that the record could not be added because it violates the rules of integrity/create a duplicated primary key..or something like that). HobbyID does nothing but act as a record identifier but the rules (constraints) of the clustered Primary Key don't allow duplicates of PersonID and Hobby. Notice also that I have "many" of the same PersonID in the Hobby table - thus the many side.

The term Primary key becomes a confusing term but I like to think of it as "unique" meaning you can't duplicate it. You can combine 2, 3, 4, etc. fields together to make clustered Primary key where you can never duplicate the values in those fields. Primary Key is also something YOU designate in the design of your table as well as the relationships.

Now here's a quiz - what if the teacher tells you that 1 person (PersonID) can have multiple different addresses. What would you do? Would you add an Address1, Address2, etc. in the People table (similar to what you have) or would you create another table similar to the Hobby table. You'd probably get different answers from everyone here on how they think it should "correctly" be done. But that's getting a little more in-depth but it's something a dba has to face (remember - a person could have 5,6,7 addresses - if you only have 3 address fields, would you really want to add a 4th, 5th, and 6th address field with city4/state4/zip4, city5/state5/zip5, etc.). Then you start getting into more complex situations but I just wanted you to be prepared in case your teacher asks you the "what if" type of question. Just something to think about where they might ask what a specific PersonID might have more than 1 of and how you would construct it. A good answer for that is that it depends on the company and what the user's requirements are (in my opionion). Like I mentioned earlier though - there's many different ways you can do it. My last program design dealt with people who often had 5-6 addresses and 1 person could work for multiple companies. I don't think your teacher expects your design to get that complex though (if she does, then she's asking too much of you in my opinion (and shame on her).)

Hope that helps a little and I didn't confuse you more.

Sorry guys but are you seriously advocating this table design:

HobbyTable
-------------
HobbyID
PersonID,
HobbyName

??? Surely not.
You just failed the assignment

donnarae79 - your post of 16:09 sounds spot on to me. Personally I would call the Hobbies table Hobby and your junction table (the one with HobbyID and PersonID) PersonHobby. You have a many to many relationship and you described the way you should implement it.

There is another way to do it (without the Hobby table and swapping the HobbyID field with HobbyName in the PersonHobby table) but I don't like to use that sort of design.

pootle flump ur codings are working excelent.

Ohhhhhhhhhhh - you mean HobbyID as PK for the PersonHobby table! Gotcha. I would have called it PersonHobbyID myself and then you wouldn't confuse people like me

Sorry donnarae79 - I have confused the issue.
I prefer the m:m method you desrcibed as you don't have to worry so much about typos. I have had to deal with some garbage data with designs like Blindman has advocated because the data is more difficult to constrain. However since this is an academic exercise I'm probably being a bit fussy.

pootle flump ur codings are working excelent.

No, I wasn't suggesting a many-to-many design. The Hobbies table I suggested was for a one-to-many relationship with the People table. I personally would not bother with a surrogate key on a table the just establishes a many-to-many relationship.

Sorry Blindman - I confused things with my own misunderstanding of your post. Your design was 1:m (Person -> HobbyPerson). The OPs idea was m:m. I was stating I prefer m:m (Person -> HobbyPerson <- Hobby) since (IMHO) it makes data constraints very simple. Really either are applicable in this case.

Additionally, like you I too am not a fan of surrogate keys that are not propogated to other tables as foreign keys but as you say it is optional.

pootle flump ur codings are working excelent.