by
Brian Haveri aka bwh2 |
2 October 2006
Most design portfolios separate works into several
categories. For instance, a design portfolio might have
these categories: websites, icons, logos, and banner
ads. Inexperienced database designers will just add a
categories column (or columns) to tbl_works because the
solution seems simple. Unfortunately, this table is now
inflexible. With such a structure, assigning multiple
categories to a work will be unnecessarily difficult.
Because category names are repeated, any changes in
spelling or wording will require more updating and are
more likely to result in both system and user error. In
general, this is a poor design. Fortunately, there is a
better solution.
/***** CREATE tbl_categories *****/
CREATE TABLE `tbl_categories` (
`category_id` INT NOT NULL AUTO_INCREMENT,
`category_name` VARCHAR(20) NOT NULL,
`category_dscpn` TEXT NOT NULL,
PRIMARY KEY (`category_id`)
)
TYPE = myisam;
/***** End CREATE *****/
/***** INSERT sample data into tbl_categories
*****/
INSERT INTO `tbl_categories` (
`category_id`,
`category_name`,
`category_dscpn`
)
VALUES (
'',
'Websites',
'Websites can be static or dynamic. Some involve
database functionality.'
);
INSERT INTO `tbl_categories` (
`category_id`,
`category_name`,
`category_dscpn`
)
VALUES (
'',
'Logos',
'Logos are done in vector based applications so
that they can be reproduced at different sizes
without reducing quality.'
);
/***** End INSERT *****/
Experienced database designers will instead create a
separate table to hold categories: tbl_categories. The
only purpose of this table is to hold information about
categories. Ultimately, this design is simpler because
each table makes sense on its own, independent of other
tables. Each table will have one job and only one job.
tbl_works will only hold information about works.
tbl_categories will only hold information about
categories. tbl_categories is structured as follows:
By assigning only one job to each table, we can simplify
the data and enable ourselves to better manage the data.
For instance, if we need to rename the category "Logos"
to "Identity", we now only need to update one row in
tbl_categories. If our categories were a column in
tbl_works, we would have to go through the pain of
searching every record in tbl_works for "Logos" and
updating each of those rows - an unnecessarily difficult
process. In fact, all of our SQL statements will become
easier to write and faster to process.
Onwards to the
next page!
|
Relational Database Design - Page 3
