The Business of Technology

Having a solid information infrastructure is key to a running a well oiled website. This involves organizing data in a way that makes retrieval fast and reliable. In order to organize information efficiently, a process called database normalization must be used to keep data both separate and descriptive. Database normalization uses linking tables to connect related information together. Linking tables are used to avoid common pitfalls found with poor database design.

Here is a list of poor database design choices:

1. Storing more than one value in a database table cell using a comma delimited list.
2. Having too many column fields in a single table.
3. Storing foreign keys inside a main table.

I explain the problems with these design choices. The first one deals with a single cell containing several values. The problem with this is that if these values are commonly reused in other records, then they must be repeated constantly throughout a table. Redundant data is a common issues with database design and must be minimized whenever possible. Linking tables are a good solution to this problem. A table with too many columns is disorganized and can be divided into smaller tables that better represent smaller groups of data. Foreign keys are better represented in linking tables because they keep main tables isolated and prevent referential integrity. Referential integrity is a best practice that keeps primary and foreign keys connect between records. When primary and foreign key relationships dissolve because of deleted records some of these keys become orphaned because the relationship between the keys has been severed.

This example will help illustrate poor database design:

Customer Table

Product Table

….

The first problem with the customer table is it contains product data even though this is the customers table. The products column illustrates both points (2) and (3) because this table contains data that is not solely related to customers, which are the product items. Also, product ID#s are stored in this table which are better moved to a linking table. This table violates database normalization because each product cell contains more than one product ID#. The next part will show how linking tables are used to improve database design.

CustomerProducts Table (Linking Table)

…

The linking table is a table that only contains foreign keys that link to the main tables for the customer and products. Now there is a table specifically for determining which prouducts customers have bought. With this new table, each cell contains only a single value and abides database normalization. Now the products column field from the customer table can be removed since the product IDs for that table have been moved to this new CustomerProducts table.

This example illustrates the usefulness of linking tables and is crucial for good database design.