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

Figure 4.2 Extract from Figure 4.1.

Reducing Redundant Data
You may think that with the size and speed of modern hard drives there is little point wor-
rying about storing too much text for each row. To an extent that is right, however it is not
just the size of the database that may become an issue. The bigger the database is the longer
it will take to utilize the data. If you are implementing a page counter on your webpage, and
the whole of the log database is going to be counted every time a webpage is viewed, having
a huge log table may well slow the site down considerably. Each time a page is viewed, the
table gets larger, and so the site gets slower.
With the exception of the datecreated column, all of the other columns could have
redundant data removed. Take for example the webpage column. This will store the name
of the webpage that the log entry refers to. You will note that we immediately have a dupli-
cate entry in the first two rows. Every time that the index page of the website is viewed, the
current table will have the text “index.php” inserted into a new row.
To stop repeatedly storing redundant data, we need to move the text description of the
webpage into another table, and find some way of relating the two entries to each other.
Figure 4.3 shows what could be stored in the second table.

The Primary Key

If you examine Figure 4.3 you will see that we have created a new table which stores infor-
mation about the webpage. We will call this the webpage table. It contains the name of the
page, as well as the title, but it also contains a row of unique numbers, the ID column. This
is known as the primary key. Each row in the webpage table can be identified by using its
key. For example, if we were to see mention of ID 3 of the webpage table, we would know
that it was referring to the links.php script, which is the Links page. This unique reference
to the row in a table greatly reduces the chances of redundant data within your database
tables. Figure 4.4 shows the website log table again, but this time we have changed the first
webpage column.

Figure 4.3 Example webpage table.