Previous page Previous     TOC Contents     Next pageNext


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.






Relationships and data types

One crucial point you have to keep in mind when describing relationships: the link is always from the primary key of one table to a field in another table. The primary key can be any type field - in CreditCards it's a Text(50) field. The other end of the relationship must contain a field of the same type and size. So, in the Customers table, c_CardName must also be a Text(50) field.

In the other tables we'll look at in a minute, the primary key is defined as an AutoNumber field. AutoNumber is not a type, it's a function. The data type is Number and the Length is Long integer. In those relationships the other end of the link must also be defined as a Number field.

In all relationships involving an AutoNumber, the other field must be a Number. In the Language table l_No is AutoNumber so, m_Language in Movies is Number Long integer.

A common mistake is to try to link two AutoNumber fields together.





The CreditCard table doesn't have to be complicated, as you can see:

          



To create the Relationship, hit the Relationships button on the toolbar:









Referential integrity: this is important; it means that you can't enter a credit card that is not in the CreditCard table; this keeps the data honest; you almost always want to do this.

Cascade update: if a card name changes, all customer records will be updated automatically to reflect the change; that's OK - usually you want this to happen.

Cascade delete: if you delete a credit card name from the CreditCard table, all customer records holding that card will be deleted; you don't want that to happen; don't check this box.








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!