26

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...

Improve this question
2
  • 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.
    – Michael Riley - AKA Gunny
    Commented Aug 1, 2011 at 12:39
  • How will you point foreign keys at different products if they are in different tables? Read up on table inheritance please.
    – Neil McGuigan
    Commented Jan 22, 2013 at 19:10

4 Answers 4

Reset to default
9

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.

Improve this answer
7
  • 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.
    – Aaron Bertrand
    Commented Jul 28, 2011 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.
    – Derek Downey
    Commented Jul 28, 2011 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
    Commented Jul 28, 2011 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.
    – Derek Downey
    Commented Jul 28, 2011 at 13:33
  • Ok, I understand. I misread what you typed.. Oops.
    – payling
    Commented Jul 28, 2011 at 13:55
7

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.

Improve this answer
2

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.

Improve this answer
6
  • 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
    Commented Jul 28, 2011 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?
    – Michael Riley - AKA Gunny
    Commented Jul 30, 2011 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
    Commented Aug 1, 2011 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.
    – Michael Riley - AKA Gunny
    Commented Aug 1, 2011 at 12:48
  • Can you summarise here please
    – niico
    Commented Dec 23, 2022 at 15:30
0

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.

Improve this answer
2
  • 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
    Commented Jul 27, 2011 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
    Commented Jul 27, 2011 at 19:02

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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