Designing and Creating Tables - Database Design Manual: Using MySQL for Windows - page 35

Figure 4.6 Weblog with even less redundant data.

•

The client's host name ( cookie.hostname - hb42.noname.com).

•

The client's IP number ( cookie.ipnumber - 192.168.200.29).

•

The script name of the page viewed ( webpage.page - visitor.php).

•

The title of the page viewed ( webpage.title - VisitorBook).

We refer to columns in external tables by giving the table name followed by a period and
then the column name. So you can see from the above example that we have actually stored
the same amount of information, without actually storing any data unnecessarily. When we
want to recreate all of the stored data across several tables, we use an SQL join command.
This will be explained in a later chapter.

Redundant or Not?

Look at Figure 4.6 again. Is there still redundant data in this table? In the cookie column, the
first line and the third line contain the cookieID of 1. If we were to remove this value, as it is
repeated, we would lose the link to the cookie table, and thus lose some information about
the page view. When a foreign key is repeated within a column, it usually does not signify
redundant data. So there is no redundant data within the cookieID column now.
Of course, you may notice that Figure 4.6 still has some redundant data in it, as there are
still repetitions in the referring page column. However, no other information about the
referring page needs to be stored, so creating another table just to log this may well be an
unnecessary overhead. On this occasion we will leave this data as it is and not store it in
another table.

Referential Integrity

Creating relationships between tables using primary and foreign keys to remove redundant
data is reasonably straightforward. However, when you wish to alter or delete records in
related tables you can come across problems with referential integrity.
For example, look at the two tables shown in Figure 4.7. If we were to remove row 2 from
the weblog table, this would be allowable as no other table in our example makes reference
to this row. The referential integrity of our data is maintained. We are removing some of the
data, but the rest of the database remains sound.
If we were to remove row 2 from the cookie table we have a problem. We would lose some
information about the Mozilla client, but what about the rest of the database? In this case