Hi Aaron, relational databases (like MySQL) have this name because you can create relations between the table. The types of relations are one-to-one, one-to-many/many-to-one and many-to-many.
Quote:
|
I thought that you could just list everything in a row
|
That depends on the case, and this is where the relations between tables come in.
Let me give you a simple example, suppose you are creating a simple contact application where you can store your friend's name, address,
phone numbers and
emails. We will also suppose that each contact can have 3 phone numbers and 2 e-mail addresses, your table would look like this:
Code:
------------
|contacts |
------------
|contact_id|
|name |
|address |
|phone1 |
|phone2 |
|phone3 |
|email1 |
|email2 |
------------
What we did here is what you said: we listed everything in a row.
Now with this design we have 2 main issues:
- Our database will be bigger because every contact will have space for 3 phone numbers and 2 emails. Some of your contacts may have only 1 phone number and 1 email, which means that the other 3 fields are useless for that contact and will be left blank.
- What happens if you need a 4th number or a 3rd email field for a contact? Surely you can add a phone4 and email3 fields... but you're adding them to ALL the other contacts as well.
How can we solve this problem? Here's where the power of relational databases come in, we'll store the phone numbers and email addresses in separate tables and link them to the contacts table.
You have to think like this:
one contact can have
many phone numbers and also
many emails. This means that the contact->phone_number relation is a one-to-many relation, the contact->email_address is also a one-to-many relation. Everytime you have a one-to-many relation you'll be creating separate tables to store information.
Let's modify our database design to reflect this:
Code:
------------ ------------ ------------
|contacts | |phones | |emails |
------------ ------------ ------------
|contact_id| |phone_id | |email_id |
|name | |contact_id| |contact_id|
|address | |number | |address |
------------ ------------ ------------
The contacts table has a field called
contact_id, this is a unique number that identifies a single contact. Similarly, the phones table has a
phone_id field and the emails table have a
email_id field, these fields are also unique numbers that identify each phone number and each email address.
Now if you look closer you'll notice that the phones (and emails) tables also have a
contact_id field. This field is what links the tables so you know which contact is the owner of which phone(s) and email(s). The number stored in phones.contact_id is the same of contacts.contact_id. This is also the field we'll be using to join the tables together and retrieve all phone numbers and email addresses of a given contact.
Ok, let's say you wanna retrieve information about contact number 8, first you retrieve the main information from the contacts table:
Code:
SELECT name, address FROM contacts WHERE contact_id = 8;
Right, that was easy.. now let's retrieve all the phone numbers associated with contact number 8:
Code:
SELECT number FROM phones LEFT JOIN contacts ON phones.contact_id = contacts.contact_id WHERE contacts.contact_id = 8;
Same thing to get the emails:
Code:
SELECT address FROM emails LEFT JOIN contacts ON emails.contact_id = contacts.contact_id WHERE contacts.contact_id = 8;
See how I used the contact_id field in both tables to join them.
The concept of relational databases and how to design your tables in this form is a little confusing for starters. But if you practice a little and read some material on the subject you'll see that it's not all that complicated.
I hope this post helps you.
