With all these joins and fancy things, it makes me wonder; what is good MySql practice? I thought that you could just list everything in a row, but no one here seems to do that.

Hi Aaron,

Good MySQL practice is as little redundancy in the tables as possible, optimized queries, getting only what you need... There are numerous books written on best practices for MySQL. Is there something specific that you are thinking about?

What do you mean list everything in a row?

__________________
-- Bill
"Why is it drug addicts and computer aficionados are both called users?" -Clifford Stoll

Theoretically, you could put all the data on one row, but that would make it so hard to maintain it would become inefficient. Plus not all data can be extracted in the same way (row count differences). All good coding is a battle between efficiency, shortness and readability.

__________________
There are two ways to write bug-free code, only the third one works.

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.

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:
What we did here is what you said: we listed everything in a row.
Now with this design we have 2 main issues:

1. 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.
2. 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:
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:
Right, that was easy.. now let's retrieve all the phone numbers associated with contact number 8:
Same thing to get the emails:
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.

DeMo, you deserve a medal for that post. It really helped me out, but when you got into joins I started getting confused. What books would you recommend?

Wow, reading my post again I realized I made a huge mistake!!
Those JOINS are useless if we want to retrieve ONLY the phone numbers (or emails).

The correct query would be simply:
Now let's get back on the JOIN.
As you can see, in my example I used three querys to get the information I wanted, the first one was used to get the main contact information, and the second and third were used to get the phones and emails. This isn't a very good practice because using a JOIN we can get all that information at once.

Joins are used when your data is scattered through multiple tables and you want to return them all at once. To be able to join tables you need to tell the database engine to look for similar data in the tables being joined, in our case this data is the contact_id field.

The basic syntax of a JOIN is like this:
If we want to retrieve our contact's name, address and phone numbers:
<fields> = name, address, number
Our main table is the contacts table, so:
<main_table> = contacts
The table being joined is the phones table:
<joined_table> = phones
The field we're using to make the join is the contact_id field:
<join_field> = contact_id
And since we want only the data from contact #8:
<condition> = contacts.contact_id = 8;

Putting everything together:
If we wanted to get the emails as well, all we need to do is attach another join after the first one, like this:
Notice how I changed the <fields> part to contacts.address and emails.address. This is needed because these fields have the same name, since we want to return both we need to tell this explicitly to the database or else our query will fail. In the first example we weren't joining the emails table, so the only 'address' field that the database engine sees is the contacts.address field, so there's no need to explicitly tell the database that the address field we want is the one in the contacts table.

Another situation where a JOIN can make your life easier is when you have many-to-one relations. Suppose you're creating an application to catalog your DVDs, every DVD will have a title, duration and genre.
If you store the genre as text every Action movie will have the string "Action" stored in that field. If tomorrow you decide that this genre should be renamed to "Action/Adventure" you'll need to update all the records in your database.

In this case we have a many-to-one relation, because one genre can be associated with many DVDs, but each DVD can only have one genre. In this case it's smarter to store the genre in a separate table that works like an index. Changing our design:
The genres table works like an index:

1. Action
2. Comedy
3. Musical

Notice how I changed the dvds table to have a genre_id field. This field will have the same value as the genre_id field in the genres table. This way, if you have an Action movie you only need to store the number 1 in the genre_id in the dvds table. If you decide to change the genre_name from "Action" to "Action/Adventure" you only need to do this in the genre table, all dvds that had genre_id = 1 have now virtually changed to "Action/Adventure".

Now let's suppose you want to produce a list of your DVDs showing their titles, duration and the genre. You can do this by retrieving all the dvds first, and then for each one of them you query the genre table to retrieve the genre. However, as we already know, this is NOT smart!! Both our tables have a genre_id field, so we can use it to JOIN the tables and retrieve all the information we want with just one query.

Here comes the JOIN again.
<fields> = title, duration, genre_name
<main_table> = dvds
<joined_table> = genres
<join_field> = genre_id
<condition> = none, because we are retrieving all records

Put everything in the query and we have:
And this concludes this tutorial-post.
I won't recommend any books to you 'cause I simply don't know anyone, I never really needed it. I learned the concept all by myself after some trial and error, reading other tutorials around the net and asking in forums like you're doing.

Maybe the more experienced guys can suggest a book for you, but I'm confident you can learn it all by yourself too, it just needs a little dedication.