The key to all this normalizing business is that the tables we create are related to one another. By having tables relate to each other we can reduce duplication and optimize retreival. For example, in a website that sells products there is a good chance that a single patron will have multiple transactions. If we were to place the transactions in the same table as our users information (firstname, last, accountID) every transaction would require a record to record the transaction. This would duplicate the user's information (firstname, last, accountID) for every transaction. If we wanted to update the users account info, we would have to update each instance. This leads to space hogs and increases the chance of data compromise.
Instead, we can seperate the users table from the transactions table, yet tie them together with a common link. Typically the Primary Key identifier. So, if the users table has an userID, this can be linked in the transactions table. This way each transaction is related to the userID from the user table, and if the users account info is updated - all transactions that are related to that userID will not need to be updated.
So just how do these tables relate? We talked about one aspect, where the transactions table relates to the users table through the userID primary key in the users table. To do this, the transactions table must have a field that holds onto this relationship. This field is cosidered a foreign key. So the users table and transactions table might look like this:
users
userID (primary key)
firstName
lastName
userName
transactions
transID (primary key)
trans_userID (foreign key attached to users table)
transCost
transDate
Many RDBM Systems outside of MySQL use foreign keys as a part of the table design (meaning that they are flaged in the DB). In MySQL it is currently implied rather than specified. Future versions, however, will most likely offer the ability to identofy these foreign keys.
Essentially there are no rules accept that they have to match the ID they link to. If the ID in the linked table changes, a link to the foreign would break.
There are three different ways in which tables relate to each other: One to One (1:1), One to Many (1:∞), or Many to Many (∞:∞).
One to One (1:1)
One to One means that each table in the relationship holds on a single record. 1:1 relationships are rarely used in database design, but they can occur when two tables rely on singluar information but need to remain seperate. For example, you may have a table for users and a table for social security numbers. Each US citizen can only have one Social, and each social can only be tied to one citezen. When designing a small site, having the social security number in the users table would make more sense. However, really large sites where many web applications may be tied to a social security table may encourage a seperation and a 1:1 relationship.
One to Many (1:∞)
By far the most common and reliable of the three relationships, and is an example of the users to transactions written above. You would have a single user to many transactions.
Many to Many (∞:∞)
A relationship is said to be many to many when multiple records in one relate to multiple records in another. For example, in a music database, an album can have songs by mutliple artists and artists can produce mutliple albums.
It is a good idea to avoid many to many relationships because they lead to redundancy and integrity problems. Instead, we can use intermediary tables to join the two together. This is all explored during the normalization process.