I'm fairly new to database design. I've come across a design problem and
I'm not finding many solutions online or in my library of books.

When I designed our ERP system we had a single family of products, they
shared 95% of the same properties and were easy to model with a single
table.

We have several new projects in development and I'm finding that they have
very different properties. To give an extreme example, let's say I need to
represent the following products:
Shirt
Truck
Tree

All are "products" we sell, but they have very different properties. I'm
really not sure how to approach this. One option is to make a massive table
that has all the properties of each product, but that doesn't seem like a
good design. ;)

Another option I thought of was to categorize the products (EG: Clothing,
Vehicles, Plants) and create a table for each category of product.
Something like:
prod_clothing
ID
Color
Material

prod_vehicles
ID
Weight
Wheelbase

prod_plants
ID
Region


I'm curious how others have handled this. Changing this area of the schema
will require a MASSIVE amount of work on the client application, so I want
to get it correct the first time.

Any suggestions will be very much appreciated.
Thanks for reading,
Steve

sleett,

First of all, I believe you might need to corral the users' requirements. If
one were to examine all the attributes of all categories of products, then
the list would be overwhelming. Major retailers have been struggling with
this challenge for decades. Take clothing for example, just size and price
could have many different types of attributes requiring different columns
and constraints in a relational database:

Size: S,M,L,XL,XXL,XXXL, petite, hefty, long, short, inseam, waist, chest,
2, 4, 6 ...
Price: mfr. suggested retail, sale price, cost plus, percent off, your
choice, BOGO (buy one get one), package (pc, monitor, printer), from $n to
$n ...

What is the product information going to be used for? Inventory, Point of
Sale, Web Catalog, Promotion Planning ...? It makes a difference in how you
need to store different attributes: 1) relationally structured in separate
columns, or 2) relationally unstructured such as XML.

Are you really going to have any kind of product whatsoever? If so, the
permutations are astronomical.

Product attributes should be set up in an inheritance hierarchy. For
example, at the top of the hierarchy all products may have the following
identically defined attributes:

All Products
------------
Category
Subcategory n
Subcategory n+1
Subcategory ... (lowest level)
(or have a separate recursive hierarchy for the categories and you will only
need the lowest level category as a product attribute)
Brand
Model
Description
UPC
SKU

Now let's drill down into the hierarchy using clothing as an example:

Clothing
--------
inherits all attributes from "All Products" plus:
Style
Color
Material

Men's Dress Slacks
--------------------
inherits all attributes from "Clothing" plus:
Waist
Inseam

Men's Casual Slacks
---------------------
inherits all attributes from "Clothing" plus:
Size (S,M,L ...)

The structure you are discribing is a normalized Type/Subtype structure
verically partitioned on product category. If you go down this path, how
many normalized tables will you have? It could be thousands. In effect what
you are trying to do is emulate an object database in a relational
structure.

The first thing to do is establish a standard product hierarchy. You can
find a community-managed directory (hierarchy) at the DMOZ Open Directory
Project: http://rdf.dmoz.org/. Or, the United Nations Standard Products and
Services Code: http://www.unspsc.org/. Tweak them to your own needs or come
up with your own.

I suggest you determine a set of core product attributes and maintiain them
as separate columns in one table (or a couple of tables at the most,
partitioned at a very high level in the hierarchy). The rest of the product
attributes could be stored as XML in one column, or in an EAV structure (may
get a lot of responses to this one, hard core DB therorists cringe at). This
approach would have to be configurable with business rules enforced in the
middle tier or on the client side.

Regardless of your approach, keep it configurable and only hard code the
highest levels of the hierarchy. The product classifications and associated
attributes will continue to be highly dynamic. Think hybrid (car), plasma
(tv), iPod compatible (audio) as product attributes that have been added in
recent years.

"Getting it right the first time" probably will not work if you are trying
to build a "fits all" relational physical structure.

Good luck,

-- Bill











[quoted text, click to view]

You should start spending some time on the Relational Theory of Data.

This should help get you started.

http://www.datamodel.org/NormalizationRules.html

http://www.seas.upenn.edu/~zives/03f/cis550/codd.pdf

http://www.oreilly.com/catalog/databaseid/

Sincerely,


Anthony Thomas


--

[quoted text, click to view]