0

Summary - Our products are very configurable and have many valid options such as model, color, height, activationtype, etc...

This is not difficult to model. I have used an EAV model but what is tripping me up is the dependency metadata. Based on the product model some colors may not be available. So the application DEV only wants the colors for that model. 1 dependency is easy enough. However, oftentimes an attribute is only valid based on a combination of previously selected values.

If model = 123 and color = Blue and ShipToCountry = USA then height can be 54 to 154 inches

If model = 123 and color = BLue and ShipToCountry = Canada then height can be 54 to 175 inches

If model = 123 and color = Black and ShipToCountry = Canada then height can only be 96 inches

I have seen up to 5 dependencies used to dictate what the next attribute's valid list of value is.

Question - What would a schema look like to hold the dependency metadata. I have tried a cross ref table that links possible value combinations based on dependencies. It works but the SQL result set is messy. Hoping for a suggestion that pushes me in the right direction.

How we currently store data

Visual of Application to Choose Product Options

This previous post is close but I am not sure it solves my issue.

Peter Paasch
  • 1
  • 1
  • 1
    You got the summary alright but forgot the question.. – holder Aug 27 '20 at 07:10
  • `This is not difficult to model.` quite the opposite. EAV is a *very bad idea*. You gain no flexibility while losing types, indexing, validation and performance. And writing a query becomes a *lot* harder. It's not needed either - if you really want to store some non-standard values per entity/table you can use sparse columns, XML fields or JSON strings – Panagiotis Kanavos Aug 27 '20 at 12:45
  • You are right. End of the day and I didn't complete my thoughts. – Peter Paasch Aug 27 '20 at 12:50
  • @PanagiotisKanavos So the suggestion is to flatten this out and build out multiple columns to house all the dependencies. Then shove JSON into the possible values list. So If I start with 10 Sparse columns and need an 11th, I would add a new sparse column. Then tell the dev to include the new column. – Peter Paasch Aug 27 '20 at 13:01

0 Answers0