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

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.