Results 1 to 13 of 13

Thread: Relationships

  1. #1
    Join Date
    Feb 2004
    Posts
    86

    Relationships

    What is the easiest way to workout a one-to-one, one-to-many, mandatory-one-to-many and optional-one-to-many etc from a database?

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171

    Re: Relationships

    If the database was well designed, the constraints will tell all. Consider 2 tables A and B:
    • optional one-to-one: B has a foreign key F to A and there is a unique constraint on the columns of F and the columns of F allow NULLs.
    • mandatory one-to-one: B has a foreign key F to A and there is a unique constraint on the columns of F and the columns of F do not allow NULLs.
    • optional one-to-many: B has a foreign key F to A and there is no unique constraint on the columns of F and the columns of F allow NULLs.
    • mandatory one-to-many: B has a foreign key F to A and there is no unique constraint on the columns of F and the columns of F do not allow NULLs.

    A good CASE tool could reverse engineer the entity model from such a database.

    If it was not well designed, you will have to infer the relationships from the data, and verify them with a knowledgeable user (e.g. "it appears that each FOO may have 0 or many BARs - is that correct?")

  3. #3
    Join Date
    Feb 2004
    Posts
    86
    So if the table Customer has a primary key of cusno
    and the Order table has a primary key of orderno

    The Order table has a foriegn key of customer.cusno what does it make the relationship? There are no constraints supplied?

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Clearly one customer may have many orders, and each order is for exactly one customer.

  5. #5
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Originally posted by andrewst
    Clearly one customer may have many orders, and each order is for exactly one customer.
    Clearly ?!?!

    -PatP

  6. #6
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Originally posted by Pat Phelan
    Clearly ?!?!

    -PatP
    Well, I thought so!

  7. #7
    Join Date
    Sep 2003
    Location
    UK
    Posts
    122
    It is clearly only if you are sad like me and spend all day doing databases.

    For normal people it might be an idea to toy with a good diagram method (ERD-Object Modling methods for example) that let you "see" the relationships.
    Matt the Hat says: "what!?"
    A child of five could understand this! Fetch me a child of five!
    SCARY HARD CHALLENGE: http://www.dbforums.com/showthread.php?threadid=988682

  8. #8
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Originally posted by Matt_T_hat
    It is clearly only if you are sad like me and spend all day doing databases.

    For normal people it might be an idea to toy with a good diagram method (ERD-Object Modling methods for example) that let you "see" the relationships.
    What about joint-purchase covenants, which prohibit a single legal entity (person, corporation, etc) from owning property? That would require multiple customers for a single invoice.

    I guess the point that I was trying to make is that we as data modelers can't assume anything until it is verified by the problem-domain experts (the end users).

    -PatP

  9. #9
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    My assessment was based on the information given that "The Order table has a foriegn key of customer.cusno what does it make the relationship?"

    You can only put one customer in a row in the Order table, which means the order hasonly 1 customer.

    Remember, this question was about working backwards from the database to the ERD...

  10. #10
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Ah, good point. I should have re-read the whole thread. Sorry.

    -PatP

  11. #11
    Join Date
    Feb 2004
    Posts
    86
    Thanks for the great answers, but what about a many-to-many relationship, how do you calculate this type?

  12. #12
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    A many-to-many relationship would be implemented by an "intersection table" like this one (between employee and project):

    create table emp_project
    ( emp_id references employee
    , proj_id references project
    , ...
    , constraint emp_project_pk primary key (emp_id, proj_id)
    );

  13. #13
    Join Date
    Sep 2003
    Location
    UK
    Posts
    122

    What he said...

    but with a picture

    This is a many to many relationship worked out in a database

    This is simply a picture of what andrewst is saying.
    Attached Thumbnails Attached Thumbnails many-to-many.jpg  
    Matt the Hat says: "what!?"
    A child of five could understand this! Fetch me a child of five!
    SCARY HARD CHALLENGE: http://www.dbforums.com/showthread.php?threadid=988682

Posting Permissions

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