Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question
1  
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. –  Cape Cod Gunny 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. –  Neil McGuigan Jan 22 '13 at 19:10
add comment

4 Answers

up vote 3 down vote accepted

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.

share|improve this answer
2  
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 Jul 28 '11 at 1:16
    
@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 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. –  Derek Downey Jul 28 '11 at 13:33
    
Ok, I understand. I misread what you typed.. Oops. –  payling Jul 28 '11 at 13:55
show 1 more comment

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.

share|improve this answer
add comment

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.

share|improve this answer
    
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
add comment

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.

share|improve this answer
    
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? –  Cape Cod Gunny 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. –  Cape Cod Gunny Aug 1 '11 at 12:48
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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