designing for repetitive data

Question

I am designing a product database and I have a design question.

There are several different categories of products, say books, video games, home electronics, and pet supplies. There are some things in common, say the manufacturer, and price, while other things are unique to each category, say power consumption.

The individual products will be updated periodically, the price could be volatile, while the manufacturer will remain fairly constant (I suppose a manufacturer could be bought by another company and the brand name absorbed into the purchasing company). Updates could happen on an hourly basis. Requests for each product could be made frequently (depends on the number of clients, so unbounded).

I am much more concerned with the speed of accessing the data for the clients than I am for the speed at which I cam perform updates to the data.

Which makes more sense and why?:

• one table for all categories with columns allowed to be null (for example pet supplies would have null for the power consumption)
• one table for each category with columns repeated (for example price would be in each table)
• one table for the common features (price, manufacturer, etc...), and one table for the unique properties

Answer 1

I'd say one table for the common features, and another with unique properties.

You could simulate something like the decorator pattern, where the extra properties are just tags associated with the products.

You will probably want groupings of tags, to make it easier to add new products.

With the above configuration, you'd probably find it easier to expand with new tags, and adding/removing tags as classifications change.

---

I could see the following issues with the other approaches.

If everything was in one table you'd have to know everything up front and constantly change the table as new field were thought up and leave many fields as NULL.

With the table per category, you'd end up having to create many different queries for each type of product and moving forward, it might get hard to maintain.

Answer 2

Take a step back and pull your head out of the database. How are you going to solve this in your application? Usually, you will use inheritance. Super classes will define the common properties, while subclasses will define the special traits.

So your question can be rewritten as: How do I implement inheritance in a database?

First of all, try to avoid data duplication. If you make a simple mistake in your transactions (or in your code), data could become inconsistent and no one would know which price is the correct one.

The one big table is probably not a good solution since you will eventually want to add a new feature. This leads to more and more wasted space in your database. Plus you will either have to build queries per class or retrieve lots of NULLs from the DB.

This leads to a multi-table apporach. The common base class maps to the central table which gives IDs to instances. All subclasses use smaller, special tables which have a ID column which is filled from the base class.

When loading data, join all the tables for a class together and load all data in one go (using the ID in all tables). This is very effective since the data lookup goes over the unique primary key and the simple ID=ID join won't cost much.