Lesson 4 - Creating relationships
How to link tables
You have now created a few tables. Some of those tables are related. You willl now have to identify how the tables are related and code that information into the database so that it will control the flow of information.
After Lesson 3, these are the tables you should have:
Note that there are several fields in both tables that refer to Links to other table. The reason for that is that Relationships are very powerful in database design.
Let's look at the case of credit cards in the Customer table.
If you have to keep a record of the customer's credit card name, number and expiry date you will create 3 fields in the table. Obviously, card number and date will be individual but, you may have 2,000 customers with Visa. When you enter the customer's record you could just type "Visa" for the card name. But what if the person keying the data keys "Viza" instead, or " Visa", or "Vissa", or ...... If, later on you have to have a list of all customers with a "Visa" card, all those entered incorrectly won't show-up.
And what if Visa decided to change its name to VizaCard? You would have to go through the records and change 2,000 customers.
The solution to those problems is: don't key the card name into the record, select the name from a list, and the list will come from another table.
In this case, we create a CreditCard table and it will contain only a list of credit cards that we will accept.
The customer record won't actually contain the credit card name, it will contain a pointer to the CreditCard table. If ever a card company changes its name, we only need to change the name in the CreditCard table and all customers who point to that name will automatically be changed.
The CreditCard table doesn't have to be complicated, as you can see:
To create the Relationship, hit the Relationships button on the toolbar:
Now, apply the same idea to the Movies table.
The fields: Category, Rating, Language, Format can all be stored in other tables and linked to Movies.
The same principles will apply. If you add a new category, you add it only once to the Categories table. If a category name changes, you change it once, in the Categories table.
The only difference in those tables is that we've given each item in the list a number: 1 = Action, 2 = Romance, etc. The list will still work the same as the CreditCard table.
For example, the Language table:
Here's what you will eventually get when all the tables and relationships are set-up. Don't worry about all the extra tables, we'll get to those in the following lessons.
Next week: Entering data into tables. See you then!