Page 1 of 2 12 LastLast
Results 1 to 15 of 24
  1. #1
    Join Date
    Aug 2007
    Posts
    32

    one-to-many relationship design...

    Hi. I am a neewbie to this forum, and need a little help in designing my database for a an assignment. I am in the process of normalising the sample data that was given to me, and am having a little trouble getting my head around relationships.

    My case study is to create a database for a video shop.

    In normalising to 2NF, do we only create a new table where their is a many-to-many relationship? Example from my design...

    I have a TITLE entity, and a TITLE-GENRE entitiy which has the following attributes (titleID*, genreID*, genre_name). Is this a many-to-many relationship? Im really struggling to get my head around which is a many-to-many and which are one-to-many. Do I create a new table so I have the following...

    TITLE
    GENRE(genreID, genre_name)
    TITLE-GENRE(titleID*, genreID*)...

    Would this be correct?

    Sorry if you dont understand any of this

    Many thanks in advance

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    To establish if a relationship is many-to-many or one-to-many you can ask yourself a series of questions:
    Can one genre apply to one or many titles?
    Can one title belong to one or many genres?

    Join the two answers up with "-to-" and there ya go

    BTW - your first definition of TITLE-GENRE is wrong.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Aug 2007
    Posts
    32
    Quote Originally Posted by pootle flump
    To establish if a relationship is many-to-many or one-to-many you can ask yourself a series of questions:
    Can one genre apply to one or many titles?
    Can one title belong to one or many genres?

    Join the two answers up with "-to-" and there ya go

    BTW - your first definition of TITLE-GENRE is wrong.
    thanks mate.. i think i have it worked out now..its a many-to-many.

    could you please tell me what was wrong about my definition?

    and also... i am having trouble moving to 3NF... i have learnt that to go to 3NF, you ask yourself "if one column changes, will another column require a change". I have looked at my 2NF, and it doesnt really seem that any changes would require a change from another column... does this mean that 3NF is just the same as 2NF??

    MAny thanks

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Many-to-many relationships are broken down with what is called an interim table, which is a table with a composite key made of the primary keys from each table.

    Example:
    One employee can be assigned to many projects.
    One project can have many employees assigned to it.

    Employees(employeeID, Fname, Sname, ...)
    Projects(projectID, title, descript, ...)
    EmployeeProjects(empliyeeID, projectID)
    George
    Home | Blog

  5. #5
    Join Date
    Aug 2007
    Posts
    32
    Yeh thanks guys i understand now and think I have it done. I was wandering.. if its not too much to ask, if someone could look at the stages I have taken to normalise, and perhaps point out any problems they see, before i create the logical and schema just so i dont run into any problems later.

    many thanks
    Attached Files Attached Files

  6. #6
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Where did the employee table come from?

    This article is well worth a read!
    George
    Home | Blog

  7. #7
    Join Date
    Aug 2007
    Posts
    32
    i added the employee table because in the TITLE table, I have an attribute called "added_byID", which is a foreign key (i think thats the correct terminology) to the corresponding employee that added that title to the database so i created a new table for the empoyee details. Probably should add a few more details such as address, phone etc....

  8. #8
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Ahh, I did not notice that, well done!
    Ask yourself this - do you need to know the employees address/phone/etc if you are simply using this for audit purposes?

    Imagine:
    employeeID = 1
    employeeName = 'georgev'

    If you know that I added the record, what more would you like to know about me?
    If the answer is nothing, then this raises a question of whether added_byId should be added_byUserName instead? The arguement could go either way...

    But then again, maybe I'm just playing devil's advocate.
    George
    Home | Blog

  9. #9
    Join Date
    Aug 2007
    Posts
    32
    thanks mate.. yeh i was thinking that exact thing to be honest.. i mean im sure (making assumptions) that this theoretical video store would have employee records somewhere else, rather than in the same database as the movies are in....

    in this case.. how should i approach this? just keep it the way it is? because if i got rid of the employee table, then sometimes when a user enters the employeeID, some might enter it in different ways, such as JohnSmith or John Smith or just John....

    when you say add my username.. i just realised thats a much better idea.. a username can be a Primary Key as long as its unique right? I cant remember if thery have to be integers or not...

    i think to fix that i should just leave it the way it is

    i dont seem to see any problems myself with the rest of it so i think im gonna go ahead and make my logical design

    thanks mate

  10. #10
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by lockmac
    a username can be a Primary Key as long as its unique right? I cant remember if thery have to be integers or not...
    Q1 - yes. Q2 - no
    Testimonial:
    pootle flump
    ur codings are working excelent.

  11. #11
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Can all employees access this system?
    Again, the answer is contextual - it depends on the business requirements.

    Here's a tricky one for you... What if an employee leaves?
    added_byID would still equal 1, but the record where employeeID has been removed - see a problem?

    But it may be a business requirement to not delete employee acounts/details.

    You're not going to be wrong with your answer, you just have to justify why you chose this method (or in fact, why you cannot justify this method).
    George
    Home | Blog

  12. #12
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by lockmac
    could you please tell me what was wrong about my definition?
    Look at the first definition you had of TITLE-GENRE. Then look at the link George gave you paying particular attention to 2NF. Then go back to the definition. Then 2NF. Notice owt?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  13. #13
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Quote Originally Posted by lockmac
    a username can be a Primary Key as long as its unique right?
    Yep, this is known as a "natural key"
    Poots rasies a good point. I did not pick up on that!
    George
    Home | Blog

  14. #14
    Join Date
    Aug 2007
    Posts
    32
    ok cheers boys.. had a goood 20 minute read of that whole document (very good document i might add!) but still cannot understand where my problem is... ive gone back and forth, any chance of a little hint??

  15. #15
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Put 2NF in your own words. What does it mean?
    Also please repost your table definition for that table as it was in your first post (to make ure we are on the same page).
    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
  •