Normalising Choices

One of the normalization rules states that all fields in a table should relate to that table's primary key. However, sometimes that's a tough call--just how much normalization is enough? For instance, if you're working with a customer database, should you have a table just for customer names, another for addresses, another for phone numbers, and so on? Or should you use just one table to store all your customer information? For the most part, there's no clear advantage to separating all your customer data. There are two situations when you'll want to consider creating a second table:

* When a field is optional, meaning many fields may remain blank.
* When a field has more than one possible entry.

Let's consider the customer's phone number data. Some customers may not have a phone (not likely, but it is possible). Other customers may have more than one phone, or type of phone. For instance, many businesses have dedicated fax lines and cell phones. In this case, you have three design possibilities:

* Store all the customer information, including the phone numbers, in one table.
* Create a separate table for phones with a one-to-one relationship.
* Create a separate table for phones with a one-to-many relationship.

Now, what are the pros and cons for each solution? Well, a single table will produce quicker data retrieval since you're accessing only one table. However, you may have blank fields and you may end up duplicating data if the customer has more than one phone. In fact, if that's the case, you really have no choice but to create a second table. If you care about storing only one telephone number say, the customer's direct business line then the second option will work. The third option is more flexible than the second, but if the additional numbers aren't needed, it's wasted effort. In the end, the choice is yours being familiar with your data is the fastest ticket to good normalization.

Access Index

Main Index