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

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.

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.

Just exchange relation terms: "one order can have many customers ?". If false, this is many to one, if true, many to many.

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

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.

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

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).

pootle flump ur codings are working excelent.

so basically you're agreeing with me, he sucks at explaining things simply

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.

pootle flump ur codings are working excelent.

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

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.

pootle flump ur codings are working excelent.

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

Thought I'd dip in to the paper
I LOLed. It's as though he read our exchange before writing the paper.

pootle flump ur codings are working excelent.

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!

As a rule of thumb though Laurent's post in #4 is a good technique.

pootle flump ur codings are working excelent.