Results 1 to 15 of 15
  1. #1
    Join Date
    Sep 2006
    Posts
    18

    My ERD needs more than two tables (was "Someone Shoot Me Now!!")

    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!

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Sep 2006
    Posts
    18
    Quote Originally Posted by r937
    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?

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    May I post my favourite Link? Rudy might know this one:
    The Link
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Sep 2006
    Posts
    18
    Quote Originally Posted by pootle flump
    May I post my favourite Link? Rudy might know this one:
    The Link

    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.
    Attached Files Attached Files

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by donnarae79
    this is either really wrong or really right.
    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
    Testimonial:
    pootle flump
    ur codings are working excelent.

  7. #7
    Join Date
    Sep 2006
    Posts
    18
    Quote Originally Posted by pootle flump
    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

    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!!!!

  8. #8
    Join Date
    Sep 2006
    Posts
    18
    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?

  9. #9
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    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.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  10. #10
    Join Date
    Sep 2006
    Posts
    18
    Quote Originally Posted by blindman
    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!!!

  11. #11
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    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.
    Last edited by pkstormy; 09-15-06 at 22:45.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  12. #12
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Sorry guys but are you seriously advocating this table design:

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

    ??? Surely not.
    Code:
     
    INSERT INTO HobbyTable (HobbyID, PersonID, HobbyName)
    VALUES (1, 1, 'Pootle Flumping')
    INSERT INTO HobbyTable (HobbyID, PersonID, HobbyName)
    VALUES (2, 2, 'Pootle Flumping')
    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.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  13. #13
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    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.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  14. #14
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    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.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  15. #15
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by blindman
    No, I wasn't suggesting a many-to-many design.
    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.
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •