The PRODUCT DETAIL entity can represent any number of characteristics about a product. Characteristics such as size, measurement, color, material, and gender are part of the PRODUCT ATTRIBUTE entity. The ERD isn't intuitive because we're used to looking at flat-file representations of catalog data like Table 1 shows. To give you a better idea of what table structures resulting from this ERD would look like, I've simulated a set of populated tables, which Figure 2 shows.
The two most important tables in this example are PRODUCT and PRODUCT DETAIL. The PRODUCT table contains the product code, which Todd assigns as an internal tracking number for inventory control; the catalog number, which identifies each product as part of the set of products that the catalog number represents; the product category, which is essential for organizing the product inventory for a catalog layout; the manufacturer code, which lets Todd easily determine which products come from which manufacturers; the manufacturer's product code, which Todd needs so that he can talk to the manufacturer about a product; and the reorder point, which differs for each product (even though all the products in this example table are sweaters).
The PRODUCT DETAIL table will have the greatest number of rows in Todd's database. Each product attribute appears as a separate row in the table. The pink sweater (the first row in Table 1) is now represented by one row in the PRODUCT table and eight associated rows in the PRODUCT DETAIL table because it has eight characteristics that describe it. Despite what seems to be a one-for-nine trade-off, the redesign is a viable alternative because of the huge reduction in data redundancy in the overall table design and a two-thirds reduction in number of bytes of storage space. (In Table 1, you need 4848 bytes to store data for just the lady's crewneck sweater, catalog number PP44888, in four colors and four sizes—not counting SQL Server file system overhead. You need only 1445 bytes to store the same information as Figure 2 shows.) Plus, even though Todd has to join tables when he searches for all items of a particular color or material type, he'll get better performance because the rows in the PRODUCT DETAIL table are shorter than the rows in Table 1, thus maximizing the efficiency of each I/O operation. For example, if Todd wanted to find all products that come in any shade of pink, he could write a simple query such as
SELECT ProductCode, ProductCategory, CatalogNumber
FROM PRODUCT
WHERE ProductCode in
(SELECT ProductCode
FROM ProductDetail
WHERE ProductDetailValue like '%Pink%')
Todd can optimize the JOIN operation by creating indexed views and striping the data across multiple spindles. Todd now has the core of a solution to his problem. He'll have to make adjustments for requirements that we haven't discussed, but he has a foundation to build on. This solution is well normalized and minimizes the nonkey data redundancy that was prevalent in the old design. Todd can search the tables for any product attribute with a minimum of coding and by joining just two or three tables. And because the width of these tables is limited, Todd might be able to create covering indexes on them, enhancing data retrieval even more. The new table structure is more efficient for managing and updating data and performs better than the old table. Todd has a workable solution that will serve the company even as it grows.
End of Article
Prev. page
1
[2]
next page -->
