3

Given a product, a product may have variations (e.g. size: Small, Medium, Large or color: White, Black). If a product has more than one variation, then the total variations is the combination of the individual variations (e.g. Small White, Small Black, Medium White, Medium Black, Large White, Large Black). Each combination of variations will be assigned its own SKU, price, stock level, etc. Each variation will also be associated with general product details (e.g. product name, product description, etc.).

What's the best way to model product variations when you don't know what the variations can be? I'm thinking it may be EAV, but I've had problems with that in the past. Wondering if there is a better way.

UPDATE 1:

This is not a duplicate of How to design a product table for many kinds of product where each product has many parameters.

After reading the accepted answer, I'm still at a lost. It seems like my reluctance around EAV is well founded. So maybe I stay away from that solution. Furthermore, if I were to use EAV, what about attributes that are always there, such as price, SKU, and stock levels? I know those must always exist -- and, as such, they don't need the flexibility of EAV.

@Bill Karwin says that his first choice would be to use Class Table Inheritance. I'm not sure if that'll work for me. I need to store tens of thousands (maybe hundreds of thousands) of records. I do not know upfront what the variants are going to be (my code will know as it parses XML feeds and spreadsheets on the fly -- but no sooner). So, trying to predict at this point what other tables I'll need is impossible.

So - I'm still lost. How do I model product variants?

Community
  • 1
  • 1
StackOverflowNewbie
  • 39,403
  • 111
  • 277
  • 441

1 Answers1

0

I'm no defender of EAV, but in some cases it's necessary, and it sounds like yours is one of those cases. If you don't know what attributes you will need at design-time, your options are fairly limited.

Using a schema-less design might be an option now that NoSQL is growing more mainstream, but will still involve the oddity of allowing objects/documents that have n dynamic attributes.

If it meets your needs, also consider simply having a BLOB or XML column that described the dynamic part of each product. This would simplify your solution quite a bit over EAV, but also limits your options for data retrieval (depending on your requirements).

Furthermore, if I were to use EAV, what about attributes that are always there, such as price, SKU, and stock levels?

I would absolutely recommend that you model as much as you can explicitly, and isolate your solution (XML, EAV or whatever) to the areas that require it. No reason to limit yourself more than you have to.

Phil Sandler
  • 27,544
  • 21
  • 86
  • 147
  • If I were to do an EAV type solution, would I have the following tabes: `products` (for the product name, description, etc.), `product_variants` (for all the unknown EAV stuff), and `product_variant_details` (for the pricing, SKU, etc.) of each combination of product variants. This would also mean that each entry in `product_variants` would have a FK to `product_variant_details` (and it would be repeating in a lot of cases)??? Not sure about this approach. As for the BLOB/XML solution, it would mean that I couldn't, say, sort by lowest priced variant? – StackOverflowNewbie Jul 22 '14 at 22:46
  • Probably better to google for an EAV design that for me or someone else to try to describe it here. – Phil Sandler Jul 23 '14 at 00:34
  • I think I understand the basics of EAV, but the common attributes is a bit confusing to me. – StackOverflowNewbie Jul 23 '14 at 00:51