I'm in the process of designing a database and I'm having second thoughts about my initial design decisions...

Product types are as follows... Models, parts, replacement part kits and options.

Option A (first design): I planned on having separate tables for the above product types. I'd say about 75% of the fields would be the same in each table.

I created each product type as separate tables because of the associations I need to create between them. For instance, a Model can have many options and a option can have many models. An option can also have many parts and a part can have many options... and so on...

Option B: Instead of having separate tables I could create a table called Product that encompasses model, part, replacement part kits and options. I could have one field called type to differentiate between model, options, etc. I suppose a down side is several fields would never be used (left null) for certain product types. I'm guessing this is where "not best practices" would come into play..

Option B would greatly reduce the complexity of the db design. I also wouldn't have to worry about referencing a bunch of tables when pulling out data for queries...

  • 2
    At this poin I suggest you create spreadsheets that mimmick your table layout and fill them with data. This will expose any weaknesses that may exist. Aug 1 '11 at 12:39
  • How will you point foreign keys at different products if they are in different tables? Read up on table inheritance please. Jan 22 '13 at 19:10

If this were my design decision, I would probably go with more of an 'Option C' (modified option a).

First, why not 'Option B':

For one thing, I like the clarity that each product has it's own table affords. If it's all one big table with a field to determine the type, the relation isn't as clear.

For another, the indexing strategy would always require that type field to be listed. Since it's only 4 types, the index cardinality is extremely low (SELECT * FROM product_table WHERE type='X' is basically doing a full table scan anyway)

Option C

  • Create a parent table that holds only the columns that all types share
  • Create each product type as it's own table with their individual columns, with one extra: A link to the parent table
  • Create each 'link' table: Product_Option, Model_option, etc with links to the respective keys.
  • For those with reciprocal links (MODEL_OPTION, OPTION_MODEL) go ahead and create those tables as well. This will add clarity in your joins for anyone looking at it.

The downside is the complexity of making sure to avoid orphans when things are updated/deleted, and initially designing the queries that use these tables.

  • 5
    There are only 4 types now, but what if more are added later? I'm sure Amazon's main product table was originally called "Books" but do you think they have a separate table for every product type now? I don't think each type should have its own table but you could use an EAV model for additional properties that each type might have in common. Jul 28 '11 at 1:16
  • 1
    @Aaron Fair point about the future increase of product types. If this scenario could conceivably expand to 10+ types of products, I would reconsider. But, I feel specific product tables is a fair design choice for a small amount of product types. Jul 28 '11 at 1:50
  • 1
    Option C: Is having a link table necessary? I would imagine the Product_Option PK would match the PK of the Product table and that would create the association to link both tables.
    – payling
    Jul 28 '11 at 13:28
  • Using Product_option as an example, the schema would be (in my mind): id, productID, optionID. productID would be a FK to product.id, and optionID is a FK to option.id. That's what I meant by link table. And yes, it's necessary in this design to allow a single product to link to multiple options. Jul 28 '11 at 13:33
  • Ok, I understand. I misread what you typed.. Oops.
    – payling
    Jul 28 '11 at 13:55

I would suggest you start with the "correct" relational model, your option A. If the typical usage of that model leads you toward denormalising in some areas, don't be afraid to do so.

I was discussing with a colleague last week how schema designs are often considered to be something that is set in stone and cannot ever change. Strange, considering how refactoring is in accepted practice in every other layer of an application, that refactoring a database schema is still viewed as impractical.

If the interface to the database is well designed, theres nothing stopping you from adapting the schema as you learn more about the systems usage patterns.


This sounds very similar to the Bills of materials/multiple cardinalities heirarcy that Paul Neilsen describes in Chapter 17 of The SQL Server 2008 Bible.

The entire chapter is a very good read and the specific section that addresses your many-to-many issue is found on pages 416-419.

This is the best discussion I have seen regarding the exploded parts type of data design.

  • This solution looks similar to option B (if I understand it correctly, which I'm not sure I do). I'd have a master table (Products) and a "link" table (aka adjacent table / BillsofMaterials) to create the associations between models, options, kits, etc. Is that correct?
    – payling
    Jul 28 '11 at 18:06
  • I think the issue is clouded because of options. Lets take options out of the discussion for just a bit. Parts are the smallest unit. A group of parts make up a model. A group of replacement parts in the form of a kit makes up a subset of the model. So far so good. Now parts have options lets assume for simplicity sake this encompasses two categories color (black, red, chrome) and material (metal, wood, plastic). You also mentioned that models have options. Are the model options separate from the part options or do the models only seem to have options because the parts make models different? Jul 30 '11 at 20:48
  • Parts don't have "options" in my design. I define option as something that goes on a model that provides it with extended functionality. A option is made up of parts. A model can have many different options. A option can fit on many different models as well.
    – payling
    Aug 1 '11 at 12:11
  • That's not how you phrased your question. Quote: "For instance, a Model can have many options and a option can have many models. An option can also have many parts and a part can have many options... and so on..." At this poin I suggest you create spreadsheets that mimmick your table layout and fill them with data. This will expose any weaknesses that may exist. Aug 1 '11 at 12:48

If you can imaging a likely scenario where there would be frequent queries that go across all four product types (and that seems likely to me), then your option B is best.

Instead of leaving a lot of unused nullable fields in the Product table, why not add a ModelProduct table, a PartProduct table, a ReplacementPartKitProduct table, and have just the fields that are distinct for those types in those tables? Use the same primary key on those tables as your Product table. Join the Product and ModelProduct table when you want to work with Models. Need to determine whether the Product record you have is a Part? Just do an left join from Product to PartProduct, and if the PartProduct.[PrimaryKey] is not null, you have a Part. If it is null, it is not a Part. Alternately, you could add a ProductType field to the Product table.

  • The null fields would be minimal, because roughly 75% of the fields would be used in every table. I guess I'm more worried about the relationships between the product types. I'll have three or so link tables pointing to the same table. Model_has_Option two primary keys, both product ids of product table, if I were to use only one table to represent product types. I'm more concerned if that is the right thing to do or not.
    – payling
    Jul 27 '11 at 18:50
  • While there are many factors that affect the "right" decision, there are two broad factors to consider. 1: overall performance requirements; 2: adaptability/complexity/maintainability. One of those two probably is a little more important than the other. If you need speed, denormalize by sticking to Option A. You'll have duplication; that's expected. If you need to fiddle with the schema on a regular basis and speed is not THE most important factor, then Option B. You get it "right" by knowing your priorities, not by adhering to "somebody else's best practices."
    – Alan McBee
    Jul 27 '11 at 19:02

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.