A key principal of any database is correct structure so that the interrelationships between the information stored therein is apparent and can be leveraged to good advantage.

These days I find myself working with more and more people and companies who, especially in this economic climate, are working on setting up their own product management databases for the purpose of populating websites and catalogs.  In most product lines there is a natural grouping or family that is often overlooked. Think multiple sizes or colors or packaging variations. Not taking these interrelationships into account is the the single most common mistake we encounter.
The tendency is to create a very flat database structure with the “base element” being the individual SKU. In so doing many such databases then require duplication of data across SKUs that share a lot of characteristics. This duplication leads to the inevitable situation where some related SKU descriptions, and most notably pricing, fail to get updated when changes occur.
This structure also makes it more difficult to market effectively and suggestively on the web and in print.

This is especially true when it comes to optimizing catalog publishing using any of a host of database publishing plugins. They all depend on “clues” to tell them which products belong together. For instance, which products should share a single picture and/or description and which bits of differentiating information should be listed separately in a list or table?
The best way to address this shortcoming is to use a basic relational database model that involves at the very least two tables:

1. The Parent. This is the table that contains the main product group or family information — the content that is common to one or more items. Typically this would include the main product name, base description, some of the product attributes, and possibly pictures or diagrams that are representative of the whole group of SKUs. This is also the record where you might store or attach key indexing or categorization information. Sometimes there is a “base product number” that is common to all of the related SKUs. If so, then this is another bit of data that should be captured in this record. If your product line is such that all of the individual SKUs share the same price, then put the price in the parent record. This, and all database tables, should include a field for the unique record ID which is often referred to as the key field.
2. The Child. This is the table that contains the individual SKU or line items that may be differentiated by a whole host of details such as packaging, sizes, colors, finishes. This table would contain fields for the product number, the price, as well as these differentiators. It also would contain the parent record ID. This is the “key” to how these child records get correctly related to their parent record. Think of it as something akin to a last name that no one else besides your brothers, sisters, and parents share with anyone else.

Setting up your product database using this structure will not only help avoid lots of copy-and-pasting of duplicate data, it will actually give you a critical leg up when setting up your product website or trying to automate your print catalog pages using database plugins.