Database Design For Multiple Product Types

Question

Lets say I have a database containing four different product types. Each type contains field that differs greatly with each other. The first type of product, is classified in three categories. The second type of product, is classified in three categories. But the third and the fourth one, is not classified in anything.

What is the best way to build the structure for this database? Should I just make four different tables? And also, how can I structure the product transaction detail with those different products?

Answer 1

First it depends wether your types are dependent on each other, and if you store type related information.
But you should for sure use a single products table.

Answer 2

There are several ways you can do this... the easiest is to just have a field for each classification, and allow them to be NULL. This would account for the third and fourth types of items, but could lead to rows that have NULLs in those columns (nothing wrong with that.)

A harder way would be to use a inheritance-like structure. In this, you have a base table that stores all common properties. Then you have "child tables" for each type of object that link to the base table and store properties for those types of objects. To get a full list of properties, you would join the parent and child tables together in a view. If you know what kind of object you are looking for, you can just link the appropriate child table. If you want to view the products together, then you LEFT JOIN all of the child tables together, but this will still result in rows with NULLs in the view that you need to interpret as being this type or that type.

LLBLGen handles both of these methods natively.

Answer 3

There is more than one way to do this, but the most straight-forward would be to:

Create a product table with the common attributes:

Product
-------------
PRODUCT_ID    NUMBER  (PK - This is what you'll foreign key against in other tables)
PRODUCT_NAME  VARCHAR(n)
PRODUCT_DESC  VARCHAR(n)
PRODUCT_TYPE  NUMBER  (FK into PRODUCT_TYPES)

Product_Types
-------------
PRODUCT_TYPE  NUMBER     (PK)
DESCRIPTION   VARCHAR(n)

Create something along the lines of this for each product type matching the custom fields to the columns needed for that type of product.

Car_Products
-------------
PRODUCT_ID     NUMBER (PK, FK - to Product)
NUM_DOORS      NUMBER
NUM_CYLINDERS  NUMBER
MPG            NUMBER
... CUSTOM FIELDS ...

You can then create views for the different product types to get all the information needed

Cars
-------------
CREATE VIEW Cars AS (SELECT * FROM Car_Products cp JOIN Products p ON (cp.product_id = p.product_id));

Answer 4

You may also want to take a look at a bit more complex model. This model takes any number of different products and assigns them properties. Each product can have any number of different properties.

PropertyType table contains various property types, like [Name] = Height, Width, Color, MaxSpeed, Volume, etc. Trait is a descriptive property like color; measurement is a numeric property, like height.

Each product can belong to many categories; categories can be nested.