8.10 Using ALTER TABLE to Normalize a Table
8.10.1 Problem
You have a table
that's not in normal form.
8.10.2 Solution
ALTER TABLE can help you
normalize it.
8.10.3 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 in every record, even though you
really need that information only once for any given client:
+----+------+---------------+------------+---------+------------------+
| id | name | address | date | minutes | description |
+----+------+---------------+------------+---------+------------------+
| 21 | John | 46 North Ave. | 2001-07-15 | 48 | consult by phone |
| 21 | John | 46 North Ave. | 2001-07-19 | 120 | court appearance |
| 43 | Toby | 123 Elm St. | 2001-07-13 | 12 | office visit |
| 43 | Toby | 123 Elm St. | 2001-07-14 | 60 | draft proposal |
| 43 | Toby | 123 Elm St. | 2001-07-16 | 180 | present proposal |
+----+------+---------------+------------+---------+------------------+
The
way to fix the problem is to split the information into two tables
and associate the records in them using the id
values:
One table (client_info) holds the information that
is unique to each client, one row per client: the ID, name, and
address.
The other table (bill_item) holds information
about each billable item: date, number of minutes, and the
description of what was done. Each row also should include the client
ID number so that the item can be associated with the proper
client_info table record.
In other words, the client information from the
client_billing table can be separated as follows
into the client_info and
bill_item tables:
client_info table:
+----+------+---------------+
| id | name | address |
+----+------+---------------+
| 21 | John | 46 North Ave. |
| 43 | Toby | 123 Elm St. |
+----+------+---------------+
bill_item table:
+----+------------+---------+------------------+
| id | date | minutes | description |
+----+------------+---------+------------------+
| 21 | 2001-07-15 | 48 | consult by phone |
| 21 | 2001-07-19 | 120 | court appearance |
| 43 | 2001-07-13 | 12 | office visit |
| 43 | 2001-07-14 | 60 | draft proposal |
| 43 | 2001-07-16 | 180 | present proposal |
+----+------------+---------+------------------+
To accomplish this redesign, first create the
client_info and bill_item
tables using the following statements, where each
table's columns are defined the same way as the
corresponding columns from the original
client_billing table:
CREATE TABLE client_info
(
id INT UNSIGNED NOT NULL, # client ID number
name CHAR(20) NOT NULL, # client name
address CHAR(20) NOT NULL # client adddress
);
CREATE TABLE bill_item
(
id INT UNSIGNED NOT NULL, # client ID number
date DATE NOT NULL, # billable item date
minutes INT NOT NULL, # number of billable minutes
description CHAR(60) NOT NULL # what was done
);
Next, use INSERT INTO ...
SELECT to copy the appropriate columns from the
client_billing table into the two new tables. For
the client_info table, the client information can
be copied like this:
INSERT INTO client_info (id,name,address)
SELECT id,name,address FROM client_billing;
Copying information to the bill_item table is
similar:
INSERT INTO bill_item (id,date,minutes,description)
SELECT id,date,minutes,description FROM client_billing;
Records in the two new tables are
associated based on id values, so
it's a good idea to index that column in each table
to allow them to be related efficiently. However,
that's not simply a matter of issuing an
ALTER TABLE
tbl_name ADD
INDEX (id) statement for each
table. For one thing, the client_info table at
this point has multiple records per client that should be collapsed
down to a single record for each client. That means we should create
a PRIMARY KEY or
UNIQUE index on the id column,
using the IGNORE keyword to tell MySQL to discard
duplicate records. In addition, queries on the
bill_item table are likely often to be date-based,
so we can include the date column in the index.
ALTER TABLE statements to
create these indexes look like this:
ALTER IGNORE TABLE client_info ADD PRIMARY KEY (id);
ALTER TABLE bill_item ADD INDEX (id, date);
After performing the procedure just described, the
client_billing table no longer is needed and can
be removed:
DROP TABLE client_billing;
With billing records stored in multiple tables, queries to retrieve
information become somewhat more complex, but relating tables is what
relational databases are good at, after all. For example, to show the
client name and address from the client_info table
along with a sum of the billable minutes listed per client in the
bill_item table, the query looks like this:
mysql> SELECT client_info.id, client_info.name, client_info.address,
-> SUM(bill_item.minutes) AS 'total minutes'
-> FROM client_info, bill_item
-> WHERE client_info.id = bill_item.id
-> GROUP BY client_info.id;
+----+------+---------------+---------------+
| id | name | address | total minutes |
+----+------+---------------+---------------+
| 21 | John | 46 North Ave. | 168 |
| 43 | Toby | 123 Elm St. | 252 |
+----+------+---------------+---------------+
Multiple-table queries are discussed further in Chapter 12.
The preceding example illustrates how to normalize a table for which
data values are repeated across rows. Another
kind of non-normal form occurs when you have multiple columns
within rows that record the same kind of
information. For example, if you're running a study
that involves administering two tests to subjects and recording the
date and score for each test, you might use a table that has the
following structure:
CREATE TABLE test_subject
(
id INT UNSIGNED NOT NULL AUTO_INCREMENT,
name CHAR(20) NOT NULL, # subject name
date1 DATE, # date and score of first test
result1 INT,
date2 DATE, # score of second test
result2 INT,
PRIMARY KEY (id)
);
Information in the table looks like this:
+----+--------+------------+---------+------------+---------+
| id | name | date1 | result1 | date2 | result2 |
+----+--------+------------+---------+------------+---------+
| 1 | Fred | 2001-07-13 | 78 | 2001-07-14 | 85 |
| 2 | Barry | 2001-07-12 | 79 | 2001-07-14 | 82 |
| 3 | Portia | 2001-07-16 | 82 | 2001-07-18 | 95 |
+----+--------+------------+---------+------------+---------+
When the information is contained in a table of this form, if you
want to do something such as computing each
subject's average score, you can do so with the
following query:
SELECT id, name, (result1 + result2) / 2 FROM test_subject;
If you decide you need to add a third test, and a fourth, you can add
columns to the table easily:
ALTER TABLE test_subject
ADD date3 DATE, ADD result3 INT,
ADD date4 DATE, ADD result4 INT;
But the query to compute average scores becomes more complex:
SELECT id, name, (result1 + result2 + result3 + result4) / 4
FROM test_subject;
In fact, if scores can be NULL to signify that a
subject hasn't taken a test yet, the query is much
messier than that, because you must add only the
non-NULL values to avoid getting a
NULL sum. This ballooning complexity is a clue
that the table design could be improved. The problem is that the
structure of the table is dependent on the number of tests you plan
to give. If you change that number, you must change the table. A way
around this problem is to use two tables, structured so that they
need not be changed if you decide to administer additional tests:
The first table records information unique to each subject.
The second table records test results, one row per test. Each row can
be associated with the proper subject using the subject ID number.
This is very similar to the approach used in the preceding example,
where we used two tables to hold client billing information. However,
instead of creating two new tables and then removing the original
table, we'll use a different procedure that
preserves the original table, albeit in modified form:
Create a table test_result to hold test dates and
scores, as well as the subject ID number. To allow scores to be
ordered, we can also add a test number column. (The date might serve
for ordering, unless it's possible for subjects to
take multiple tests on the same day. An explicit test number does not
have that problem.)
Copy the subject ID, date, and score information from
test_subject to test_result.
Remove the date and score columns from
test_subject, leaving only subject IDs and names.
Begin by creating a table to hold test results:
CREATE TABLE test_result
(
id INT UNSIGNED NOT NULL,
test_num INT NOT NULL,
date DATE,
result INT
);
Copy the test information from test_subject to
test_result. It's necessary to do
this separately for each test, because each result is copied from a
different set of columns:
INSERT INTO test_result (id,test_num,date,result)
SELECT id,1,date1,result1 FROM test_subject WHERE result1 IS NOT NULL;
INSERT INTO test_result (id,test_num,date,result)
SELECT id,2,date2,result2 FROM test_subject WHERE result2 IS NOT NULL;
Each INSERT INTO ...
SELECT statement specifies the
test_num value
"manually," because the appropriate
value is not present in the test_subject table and
cannot be derived directly from its contents. The
WHERE clause serves to copy only rows with
non-NULL test results. This handles the
possibility that your test_subject records might
be incomplete, such that a NULL score indicates a
subject hasn't taken a given test. (If the
test_subject table has no such missing test
results, the WHERE clauses are unnecessary and can
be omitted.)
The test_result table is populated now and can be
indexed. Note that although the id column is a
unique-valued column in the test_subject table, it
cannot be so in test_result because there will be
multiple rows per subject. However, it's still
possible to create a unique index by using both id
and test_num, assuming each test is given to each
subject only once:
ALTER TABLE test_result ADD PRIMARY KEY (id, test_num);
The test result columns in test_subject are now
unneeded and can be removed:
ALTER TABLE test_subject DROP date1, DROP result1, DROP date2, DROP result2;
The advantage of using a two-table layout like this is that queries
to perform operations such as score-averaging become independent of
the number of tests administered:
SELECT id, AVG(result) FROM test_result GROUP BY id;
To show subject names, too, join the test_result
table to the test_subject table:
SELECT test_result.id, test_subject.name, AVG(test_result.result)
FROM test_subject, test_result
WHERE test_subject.id = test_result.id
GROUP BY test_result.id;
You also can easily identify subjects who have not completed all the
tests. For example, if you've presented a total of
four tests, you can find subjects who don't have
four scores in the test_result table like this:
SELECT test_subject.id, test_subject.name, COUNT(test_result.result) AS count
FROM test_subject LEFT JOIN test_result ON test_subject.id = test_result.id
GROUP BY test_subject.id
HAVING count < 4;
This query uses LEFT JOIN to
make sure that a count for every subject is generated, even those to
whom no tests have yet been administered. (A regular join will fail
to identify a subject who has a record in the
test_subject table but no records yet in the
test_result table, because it will find no match
between the tables.) LEFT JOIN
is discussed in Chapter 12.
| 
No comments:
Post a Comment