MySQL Cookbook

Using ALTER TABLE to Normalize a Table

Problem

You have a table that’s not in normal form.

Solution

ALTER TABLE can help you normalize it.

Discussion

The preceding sections describe how to use ALTER TABLE in fairly general terms. This section shows a more concrete application of the statement by demonstrating how ALTER TABLE can help you redesign tables that you discover to contain unnecessarily redundant data and therefore to be in non-normal form.

Suppose you have a client_billing table for recording billable items that is defined as follows:

CREATE TABLE client_billing
(
    id          INT UNSIGNED NOT NULL,  # client ID number
    name        CHAR(20) NOT NULL,      # client name
    address     CHAR(20) NOT NULL,      # client adddress
    date        DATE NOT NULL,          # billable item date
    minutes     INT NOT NULL,           # number of billable minutes
    description CHAR(60) NOT NULL       # what was done
);

When you have just one row of information per client, the table looks perfectly fine:

+----+------+---------------+------------+---------+------------------+
| id | name | address       | date       | minutes | description      |
+----+------+---------------+------------+---------+------------------+
| 21 | John | 46 North Ave. | 2001-07-15 |      48 | consult by phone |
| 43 | Toby | 123 Elm St.   | 2001-07-13 |      12 | office visit     |
+----+------+---------------+------------+---------+------------------+

But when you add more records and have multiple rows per client, it becomes apparent that some of the information is redundant. In particular, names and addresses are stored ...