Modelling products with multiple variations

Question

I'm currently working on a project where the database will contain products with variations and I an wondering the best way to model this in the database.

By 'products with variations' I mean things like t-shirts, where a single shirt will come in a variety of sizes and colours, but is still essentially the same item.

These will be grouped into product 'families' which made me think it would be simple enough, as I could just create a table for the families and have a linking familyproducts table to identify the products in each family - using the assumption that each variation is an individual product itself.

The problem arises that some attributes of these variable products will be a set amount/value - for example, colour will be a particular set of colours, but size could be anything as the t-shirts (following the analogy) are custom made so are based on a measured chest size.

Because of the nature of the project, whenever a product is created, even if part of a 'family' and marginally different, all the information is needed for that product.

tl;dr:

• Product variations can be either set (ie, of a limited number) or on a sliding scale (ie, essentially an infinite number)
• Because of this I need to store 'families' of products in such a way that:
  • Products can be defined as 'set' or 'sliding scale' on the attributes that vary
  • New products within the family, even sliding scale, can show all information
  • Makes sense!

I'm not necessarily looking for a database model to be provided to me, just a pointer in the right direction will be a massive help as I'm pretty lost on the best way to do this effectively.

Answer 1

This is a fairly common problem - it's often referred to as "how do I map object inheritance into a relational structure". There are a number of options.

The one Joe describes is commonly known as "Entity/Attribute/Value" or EAV. It's very flexible - you can cope with a wide variety of data attributes without having to re-design your database - but it quickly runs out of steam with complex queries - imagine asking for all t-shirts whose colour is red, size in (M, S, L), and neck size greater than 44.

The following alternatives are described in Craig Larman's book "Applying UML and patterns" - grab a copy for more detail. or see here.

Firstly, you can model the common data between each subclass in one table, and have a separate table for each subclass. In your example, you'd have a table for "product" with SKU etc., and a table for "t-shirt" with colourID, chestMeasurement etc. This means lots of joins to retrieve multiple different product types, but gives you a fairly "clean" data model.

The next model Larman describes is "super table" - you have one table that simply contains all possible columns. Ugly and unwieldy, but if you only have a few sub types, possibly the easiest and fastest.

Finally, the "one table per subtype" option - you simply map each type to its own table - e.g. "t-shirts", "trousers", "hats" etc. Very clean and predictable, but it's hard to query across product types.

Outside the obvious RDBMS solutions, you could also look at a document-oriented solution, e.g. by using your database engine's support for XML.

Answer 2

Assuming the feature set is not identical across your products (this seems evident from your question), this structure makes sense:

• Table of products; Contains Product ID/SKU, Product Name, Product Family ID. One row/product.
• Table of product families; contains Product Family ID, Product Family Name, and any details specific to product families. One row/product family.
• Table of product features; contains product ID, feature ID, possibly feature type, value, possibly units for value. Multiple rows/product, one row/feature.
• A feature lookup table linked to product features by feature ID; contains feature ID, feature description, feature type, and possibly units for value (both of those latter two may be be better off in product feature table, depending on how you use the table).

That gives you fairly immediate access to those features/etc. that you want without having to worry about 'sliding scale' or whatnot. You can also set up a lookup table for the values in the feature table for your non-quantitative values (I think you wouldn't store this in the feature table, as that table should be 1 row/feature). If your features are fairly close to unique per product, the you can skip the 1 row/feature lookup table and just have the multiple row feature value table, but it doesn't sound like that's the case.