A bit of history on it.
Age : over 25 years old (born in 1969)
Taste : Dislike slow computers (25 years ago, computer were REAL slow). Part of why it wasn't as use as the other type of relation (at that time). When the microprocessor was invented, The age of Relational Database was truly upon us.
Name : Derived from the word relation which is part of a set theory. It is not because the table in the database can be related one to another in the database.
Father : Dr. Edgar F. Codd
RELATIONS
A Relational database store data in relations (we perceive it as tables).
We can categorize the relations in 3 different types
One-to-one relationship
the less common type of relationship.
A pair of table bears a one-to-one relationship when a single record in the first table can be related to only one record in the second table, and a single record in the second table can be related to only one record in the first table.
view the image here
Okay, now go back to the image, you should be able to tell something here...
well if you didn't find it I'll tell you
The designer of the database could have put all the info in one table.
So why he didn't?
Theses questions are what makes the difference between a good designer and a very good one.
The very good one though about the facts that not everyone should have access to the compensation a employee makes. Only authorized personnel can view the compensation table, and everyone can view the employees one.(we still could have put everything in one table put just harder to implement).
One-to-many relationship
Let's now talk about the most common type of relationship.
One-to-many relationship exist between a pair of table when a single record in the first table can be related to one or more record in the second table, and a record in the second table can be related to only one record in the first table.
In this example, you have :
the employee of a mega corporation that want to "rulez" over the world. Let's call it Microsoft.
These employee can have each one of them one or more clients attached to them. But, a client can have only one employee that takes care of him.
So you get a one-to-many relationship.
This particular relationship is not the most common for nothing.
-help eliminate duplicate data
-keep redundant data to a minimum
Many-to-Many relationship
Okay, this one is the 2nd most common type of relationship, but a bit more complicated to illustrate.(don't worry, I'll find some good example somewhere
A pair of table bears a many-to-many relationship when a single record in the first table can be related to one or more records in the second table and a single record in the second table can be related to one or more records in the first table.
Guess what, I did find some good example.
Well here's a typical example, when you go to school you have more than one classes to attend to right (hopefully)
And a class can have more then one student attending.
yep yep yep...
Self-referencing relationship
This type of relation is NOT between a pair of tables, it is instead a relationship between the records within a table.
I'll go quickly over this one.(no pictures, only text example)
One-to-one
If you have different members within a organisation, and a given member can sponsor only one other member within the same organisation.
You would have a table with the membersid, name lname sponsor id.
In the sponsorid field you would have the memberid of whom you got sponsored.
One-to-Many
A customers of a given organisation can be referred by (other ppl, you know..other customers.). And one given customer can refer one or more customers.
I have a web host, I can refer people to this host (and get some kind of discount). I can refer more then one customer, so if I refer Feyd and PatrickG to this host, in the table "customers" under they're name in the field referred by there would be my customerID.
many-to-many
Okay god this one is complicated to explain.
Let's give it a shot.
You have different product, each of them are made out of smaller part .
So a particular part can comprise different components part AND be itself component of another part.
Let's say a chain have little bolts on it
part -> chain
components -> bolts
and a bicycle has a chain to be functional
part -> bicycle
components -> chain
So a chain is both a part and a component.
You can by a chain, you can by a bolt and you also can by a bycicle, they are all different part/components of the same store.
Well
That's about the relation in a relational database.
Ps : any question pm me, and give me your feedback, good or bad
enjoy
EDIT : spelling