Page 1 of 2 12 LastLast
Results 1 to 15 of 25
  1. #1
    Join Date
    Dec 2008
    Posts
    27

    Relationships. Sure. But How Many?

    Hi Mates,

    I am new to Databse Design and trying to move from spreadsheets to Relational DB. For that purpose I am reading books that are for beginners. The problem is that none of the books have seperate chapters on relationships. They just mention them in passing. On the net also the relationships are not explained properly. I mean, come on, is it not all realy about relationships. I understand the fact that Relational Database and Database Relations are different. The fact that we can represent Data relationaly has nothing to do with the Relational Model (Codd). However to me they seem very important. Different tables for different Entities. Sure. But they are connected through relationships. None of the material I have read explains this important aspect clearly. And than there is Closure. But that is a different toipc so I will not burden you with that for now. I have posted now my question on 5 web sites that claim to be Database Forums. No answers.

    One-to-One.

    Click image for larger version. 

Name:	One to One.gif 
Views:	80 
Size:	10.6 KB 
ID:	9034

    In these examples, the relationships are one-to-one. Fine. Logical. But on what ground we decide which Primary Key becomes a Foreign Key in which table. PK President ID in PRESIDENTS table becomes FK President ID in the COUNTRIES table. While the reverse may be quite possible. Which is the Primiary Table and which is the Child Table?

    Further more in any table that holds the FK, I can not add any new record unless it has a record in the corresponding table. For example I can not add any new country in the COUNTRY table, unless it has a PRESIDENT. One could argue that since no country can exist with out a President, this model is logical. But one can also argue that what if I want to add a new Country that has a relationship with another Entity and the PK Country ID from the COUNTRY table is FK Country ID in that new table. For example in the case of Rooms & Employees the PK Room ID in the ROOMS table becomes a FK Room ID in the EMPLOYEES Table. Now I can not add any new Employee unless he has a ROOM. If I can not add a new Employee, I can not add a new Order.

    Click image for larger version. 

Name:	One to One - One to Many.gif 
Views:	36 
Size:	4.3 KB 
ID:	9035

    However if I now reverse it, I can add new order. But than I can not add new Room

    Click image for larger version. 

Name:	One to One - One to Many - Reveresed.gif 
Views:	31 
Size:	4.3 KB 
ID:	9036

    Any Body

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    in the real world its fairly unusual to have one to one relationships.. often its done for performance or security issues, or if say the db cannot handle the number of columns. There are others (better) but for the life of me I can't remember any right now.

    int he real world you would have as many relationships as you felt were needed to support the real world you are modelling. its unusual to have more than 1 realtionship between tow tables, unless there are two or more separate relationships
    eg you mioght have a table which identified say the signoff on a purchase order (you may then the id of the person who raised the order, the id of the person who signed off the order, the id of the person who entered the order.... each of whihc whould be a FK to a person in say your staff table.
    its legit to have the fk to refer to a column within the same table

    eg you have a person ID, and a manager ID, the person ID is a PK (no nulls), the manager ID is a FK to person Id in the staff table, the manager id is nullable, null indicates that this person has no manager


    have a look at rudy's (r937) site
    and tony marston's site deserves a good read

    the terminology can be a bit tortuous at first
    a PK is something that makes this row unique
    a FK is something in this table that refers to another table's value, a FK value is unique in in that other table

    for what its worth in your president / country model
    "a president" is a distinct entity so If have a table containing details of presidents
    "a country" is a distinct entity, do Id have a countries table (probably using the ISO country code as the PK
    a country can have more than one president*
    A president may be president more than once
    its unlikely that a president can be a president in more than one country

    so to get round that I'd have what's called "an intersection table"
    which associates the values from the other tables countries & presidents plus any other data which is relevant to that intersection

    so I'd have the President ID, the Country ID plus things like
    date became president, date stopped being president. it could include reason became president (eg election, deputy), reason stopped being president (eg couldn't stand again, lost election, got caught out comitting fraud, died, overthrown......) conceivably those reason codes could justify tables in their own right. in which case the reason code in the intersection tabel becomes the FK to the reason code in say PresidentLeavingCode, where the reason code in that table would be unique and hence the PK

    *unless you are an African ex colonial country run by a Marxist trained dictator where you can only have one president
    Last edited by healdem; 12-13-08 at 09:09.
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Dec 2008
    Posts
    27
    Question 1 - I though that "an intersection table" is created to resolve many-to-many relationships as they are really two 1-to-many relationships.

    Question 2 - Are you saying that FK values can be null.

    Question 3 - But how to decide which PK goes in which Table. You can have have


    EMPLOYEES
    Employee ID - PK
    Employee Name
    Room ID - FK from the ROOMS Table.

    OR

    ROOMS
    Room ID - PK
    Room Size
    Employee ID - FK from the EMPLOYEES Table.

    Additional attributes as not a problem here. You can have as many as you want. For example EMPLOYEES Table can have Date of Birth, FIrst Name, Last Name and similarlily so ROOMS Table. That is not the point and does not have any problems. The Problem is

    PRESIDENTS
    President ID - PK
    President Name
    Country ID - FK from COUNTRIES Table

    OR

    COUNTRIES
    Country ID - PK
    Country Name
    President ID - FK from PRESIDENTS Table.

    which one?

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    healdem, you do not need a many-to-many table between presidents and countries

    there has never been any person who has been president of more than one country (you could look it up)

    JAA, the one-to-one relationship is real easy

    of the two entities, one of them will either --
    1. be optional
    2. potentially have multiples

    in your example, a president cannot be a president unless he or she is a president of a country

    on the other hand a country can exist without a president (although typically it does not do so for very long)

    so the FK goes into the dependent entity, the president

    simple, yes?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Dec 2008
    Posts
    27
    r937,

    Can the FK value be NULL?

    Ok it works for PRESIDENTS & COUNTRIES. But does not work for ROOMS & EMPLOYEES. Room can exist with our Employee and Employee can exist with out Room

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Quote Originally Posted by r937
    healdem, you do not need a many-to-many table between presidents and countries

    there has never been any person who has been president of more than one country (you could look it up)
    depends how you look at it.. the president is the head of state, there are several countries with the same head of state. even if you don't share the same head of state, I still think you need an intersection table to handle when a president held office.

    Quote Originally Posted by r937
    JAA, the one-to-one relationship is real easy

    of the two entities, one of them will either --
    1. be optional
    2. potentially have multiples

    in your example, a president cannot be a president unless he or she is a president of a country

    on the other hand a country can exist without a president (although typically it does not do so for very long)

    so the FK goes into the dependent entity, the president

    simple, yes?
    ..which is fine if all you want to do is register who is the current president. you cannot legislate for the eventuality that a president may be president for more than one stint, and those stints are not neccesarily contiguous
    I'd rather be riding on the Tiger 800 or the Norton

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    dude, we both posted at 9:37

    how freaky is that!!!

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Quote Originally Posted by JAA149
    r937,

    Can the FK value be NULL?

    Ok it works for PRESIDENTS & COUNTRIES. But does not work for ROOMS & EMPLOYEES. Room can exist with our Employee and Employee can exist with out Room
    its quite possible for the FK to be NULL, meaning that there is no value in the column which happens to be the FK. to use the presidents analogy it could be because there is no president. its possible however to define that column as saying there must be a value.. thats down to the detail of the design or real world model.
    I'd rather be riding on the Tiger 800 or the Norton

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    okay, healdem, you win

    now, go back and pretend you're going to use a one-to-one relationship between president and country

    where does the FK go?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  10. #10
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Quote Originally Posted by r937
    okay, healdem, you win

    now, go back and pretend you're going to use a one-to-one relationship between president and country

    where does the FK go?
    Its not like Wordscraper.. there doesn't HAVE to be a winner

    if you have a one to one relationship then I don't think it matters, if it had to be a one to one relationship then Id say the country AND the president should be in the same table..... I can see no need for a separate table identifying the president in that event.
    I'd rather be riding on the Tiger 800 or the Norton

  11. #11
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by healdem
    if you have a one to one relationship then I don't think it matters
    it matters to the original poster here, because that's the question he's been asking


    Quote Originally Posted by healdem
    I can see no need for a separate table identifying the president in that event.
    well, if the president had forty-nine columns of data, and the country had seventy-six columns of data, would you still whack them into the same table?

    because when you later need to update your country for a new president, you gots a lot of splainin to do, lucy
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  12. #12
    Join Date
    Dec 2008
    Posts
    27
    I have never seen having a "intersestion table" between two entities that have one-to-one relationships. That will cause a many-to-many relationships to occur.

    There are many reasons that the attribute is moved to become a new entity rather than remain in the same table. Two you pointed out yourself. One is Security/Privicy reasons and second is Database Engine limitations. Third may be as

    PRESIDENTS
    President ID - PK
    President Name
    President Date of Birth
    Country ID - FK

    COUNTRIES
    Country ID - PK
    Country Name
    Country Population
    Country Category ID - FK

    COUNTRY CATEGORIES
    Country Category ID - PK
    Country Category Name (e.g. Terrorist, Allay, Neutral)

    Shoul I have all of these in One Table.

  13. #13
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    can a country be in more than one category?

    also, if all you have is category name, you should use that as your PK and FK instead of the horrible "ID" concept
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  14. #14
    Join Date
    Dec 2008
    Posts
    27
    Ok three things I learned but not understood.

    1 - FK can be Null. If it is Null or not can be set up by Referencial Intergrity. But It can be Null.

    But that Just leaves an ORPHAN Child Record???

    http://www.r937.com/Relational.html

    2 - Here we have a case where Two Table Share the Same PK. Can two Tables have the same PK?

    3 - In a 1-to-many relationships it is obvious where the FK goes. To the many Side for we can not have vice versa as it will cause data redundancy. But it is not clear in 1-to-1 relationships.

  15. #15
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    talking purely in terms of the president / country example
    if you don't care about the presidents over the years then I see no point in distinguishing to two entities.

    if the table containing thr presidents was say a table containing prominent people or politicians then I'd expect a FK in the that table to indicate what country they are "prominent" in
    I think its reasonable in that event to have the president of a country as a FK in the countries table

    if the president had 49 columns of data then thats a good reason to have a one to one relationship, the FK would be country in presidents to the pk of country.

    but as said before my preferred implementation would be a table for presidents a table for countries a table which resolves which president was president in which country for what period.. that caters for knowing who was president over time.

    if all you care about is who is the president of country X then I still don't see the need for a separate table, the president has become an attribute of the country, rather than an entity in their own right.

    I appreciate that there are no presidents who have been president in more than once country, but we don't know if that is a valid constraint. I don't know the details but I wouldn't be surprised if that did hold in the dissolution of Jugoslavia, it could well happen in the dissolution of the UK.

    looking at the next example the OP talks about
    which talks of employees and rooms, again I woudl go down the route of an intersection table, a one to one doesn't cut it

    a room may have none, one or more employees in it
    an employee may work in more than one room

    so I would look to
    a table for employees
    a table for buildings
    a table for rooms (fk in building)
    a table for employees in rooms (with a fk to employees AND rooms)
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

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