|
This week's tip is a continuation of last week's tip.
In part 1, I created the following table, called Item:
| ItemID |
ListID |
Item |
| 1 |
1 |
lettuce |
| 2 |
1 |
carrots |
| 3 |
1 |
sausages |
| 4 |
2 |
apple |
| 5 |
2 |
lemon |
| 6 |
2 |
pineapple |
| 7 |
3 |
cinnamon |
| 8 |
3 |
apple |
Whilst this is undoubtedly an improvement over my original design, it still has a level of redundancy:
"apple" is listed twice.
My real goal, is to create a separate table for ItemTypes, which will hold the names of each type
of item. The Item table will then contain only numbers:
| ItemID |
ListID |
ItemTypeID |
| 1 |
1 |
1 |
| 2 |
1 |
2 |
| 3 |
1 |
3 |
| 4 |
2 |
4 |
| 5 |
2 |
5 |
| 6 |
2 |
6 |
| 7 |
3 |
7 |
| 8 |
3 |
4 |
The first step to achieve this is to execute the following:
SELECT DISTINCT(Item) As Description
INTO ItemType
FROM Item
This will insert the following entries into a new table:
| Description |
| apple |
| carrots |
| cinnamon |
| lemon |
| lettuce |
| pineapple |
| sausages |
To be useful, this table needs an identity column, which can be added after the table has been created,
using ALTER TABLE.
ALTER TABLE ItemType
ADD ItemTypeID int identity primary key
ItemType now contains:
| Description |
ItemTypeID |
| apple |
1 |
| carrots |
2 |
| cinnamon |
3 |
| lemon |
4 |
| lettuce |
5 |
| pineapple |
6 |
| sausages |
7 |
The Item table also needs an ItemTypeID column to be added (but without the identity or primary key
constraints):
ALTER TABLE Item
ADD ItemTypeID int
UPDATE Item
SET ItemTypeID=ItemType.ItemTypeID
FROM Item INNER JOIN ItemType ON Item.Item=ItemType.Description
Lastly, the now redundant Item column can be dropped from the Item table:
ALTER TABLE Item
DROP COLUMN Item
Everything I've done here can be done through Enterprise Manager, or whatever tool your RDBMS provides. There are
however some advantages to scripting design changes in SQL.
For starters, the changes can be scheduled to be applied at midnight or some other time when the database is least
needed. More importantly, it is possible to test a script on a test database prior to execution on the "live" database.
Adelle.
|