Page 1 of 2 12 LastLast
Results 1 to 15 of 25
  1. #1
    Join Date
    Nov 2009
    Posts
    3

    Question Quick question... very new!

    Hello, I have just started doing database design, and one thing that I am wondering is about table relationships. One to one, one to many, and many to many.

    In many cases it seems simple, but if i think about it for a while, tables relationships can use either of these.

    For example, you have a customer tables and an address table. I would think one customer can have one and only one address, so it would be a one to one relationship. But the opposite is not the same, as an address can belong to one or many customers (if two or more customers are living in the same house)

    How do I classify this relationship? is there an easy way I can make the connection in the future, as many examples seem to be confusing me.

    Thank you

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Most of the time you can simply store address information as an attribute of person, or as a subtable if people can have more than one address, and not worry about storing the same address twice because 95% of the time we are much more interested in tracking people than tracking houses.
    But if you are developing a real-estate application, or a building maintenance application, then you would want buildings to be their own entity and establish a many-to-many relationship with the tenant records.
    If it's not practically useful, then it's practically useless.

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

  3. #3
    Join Date
    Nov 2009
    Posts
    3
    Thanks, I will usually use address with customer information then.

    But, the relationship idea is the thing that kind of bugs me. Lets say with Customers and Order. I know right now that one customer can have one order, and by that logic I will state that its a one to many relationship. one customer can have many orders.

    But what stops me from saying, many customers can have many orders? That is how many to many relationships confuse me. I seem that I can use it anywhere (i know that I should use one to many, but many to many seems to also fit)

    So my question is, how do I tell if something is one to many or many to many? I understand the terms, I just can't seem to make sense of it.

  4. #4
    Join Date
    Aug 2009
    Posts
    68
    Quote Originally Posted by spooks View Post
    ...one customer can have many orders.
    So my question is, how do I tell if something is one to many or many to many? I understand the terms, I just can't seem to make sense of it.
    Just exchange relation terms: "one order can have many customers ?". If false, this is many to one, if true, many to many.

    Quote Originally Posted by spooks View Post
    But what stops me from saying, many customers can have many orders? That is how many to many relationships confuse me. I seem that I can use it anywhere (i know that I should use one to many, but many to many seems to also fit)
    Nothing stops you. It would work too, but you would have one more table in your design and more code to manage it. It would be useless complexity.

    HTH
    Laurent

  5. #5
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    You're not alone in finding the terms confusing - so does C J Date (one of the leading relational database theorists). His article on the subject may help.

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by andrewst View Post
    just had a quick look at this paper, and all i gotta say is, that guy may know his shiznitz, but he *sucks* at explaining things simply
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    CJ Date is very precise with the language he uses and will not compromise by using "layman's" terms when there is a domain specific or expert term that is exactly what he means. Quite a lot of his writing actually is clarifying exactly what he means by statement XYZ.
    IME most of this texts must be read in the context of some of his fundamental texts (though I'll admit I've not read him widely).
    Testimonial:
    pootle flump
    ur codings are working excelent.

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by pootle flump View Post
    CJ Date is very precise with the language he uses and will not compromise by using "layman's" terms when there is a domain specific or expert term that is exactly what he means. Quite a lot of his writing actually is clarifying exactly what he means by statement XYZ.
    IME most of this texts must be read in the context of some of his fundamental texts (though I'll admit I've not read him widely).
    so basically you're agreeing with me, he sucks at explaining things simply
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    He excels at explaining things precisely and unambiguously.
    I don't believe his intent is to explain things simply (at least not the way you mean it) so I'm not sure if he could be said to "suck" at it.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  10. #10
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by pootle flump View Post
    He excels at explaining things precisely and unambiguously.
    I don't believe his intent is to explain things simply (at least not the way you mean it) so I'm not sure if he could be said to "suck" at it.
    fair point

    in that case, what sucks is recommending cjdate to someone who is struggling with simple concepts and wants simple explanations, especially if you gots to go get a university education before you can read him
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  11. #11
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    There's probably some truth in that. His more fundamental texts are intended for undergraduate study (the stuff you study during your degree, in case I am using UK terms here), the rest (as far as I have seen) build on those.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  12. #12
    Join Date
    Aug 2009
    Posts
    68
    Quote Originally Posted by r937 View Post
    fair point

    in that case, what sucks is recommending cjdate to someone who is struggling with simple concepts and wants simple explanations, especially if you gots to go get a university education before you can read him
    I think you're right, but the paper points something important IMO: the three kinds of relationships we use in databases covers much more different relations, and maybe we should better not to use them in our designs, only in implementations.
    My two cents...
    Laurent

  13. #13
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Thought I'd dip in to the paper
    Quote Originally Posted by C.J.
    clarity, accuracy, and precision are surely paramount;
    without them we're doomed.
    I LOLed. It's as though he read our exchange before writing the paper.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  14. #14
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Quote Originally Posted by r937 View Post
    in that case, what sucks is recommending cjdate to someone who is struggling with simple concepts and wants simple explanations, especially if you gots to go get a university education before you can read him
    Oh, stop being such an inverted snob about education! Yes, some of what Date says gets quite complex and academic, but he does give some very clear examples of each possible relationship type e.g. "In a given company at a given time, each employee has exactly one department and each department has at least one employee". I would assume anyone who posts a question on this forum is actually interested in learning about databases and doesn't want "Janet has a dog. See the dog run" explanations!

  15. #15
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    As a rule of thumb though Laurent's post in #4 is a good technique.
    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
  •