2

I'm building a product catalog with hundreds of different product types. These product types have a lot of different attributes. Some are shared, but most are specific for the product type.

My initial thought was to go with an EAV-type structure, but I understand why that might be a bad choice. Especially since I want my database to enforce correctness and consistency, which is going to be a mess with EAV.

The alternative in my case would be class table inheritance. I'm a bit worried about the maintainability though... how am I going to maintain hundreds of migrations and models? Is that really a desirable situation? I understand the benefits, but isn't maintainability a huge downside?

Willem-Aart
  • 2,200
  • 2
  • 19
  • 27
  • Sorry, but this is too opinion-based for Stack Overflow. I think that in our case, EAV could be appropriate. Why would it become a mess? EAV has referential integrity. It's nothing but a many-to-many association between entity and attribute in which a junction record is a value. It makes a couple of thing *harder*, esp. querying with search conditions, but that's not the same *messy*. – Gert Arnold Aug 28 '16 at 22:13
  • Maybe messy is not the correct term. Some of the requirements are hard to accomplish with EAV, such as enforcing correct attribute values, data types and non-null values. These are all well described downsides of EAV. If I would use EAV, I would accept those consequences. My question is specifically about the maintainability of class table inheritance, though. – Willem-Aart Aug 28 '16 at 22:26
  • 1
    Possible duplicate of [Entity-Attribute-Value Table Design](http://stackoverflow.com/questions/11779252/entity-attribute-value-table-design) – Joel Brown Aug 28 '16 at 22:48

1 Answers1

1

Here's an alternative, similar to EAV but with special considerations to "consistency" between products of the same type.

List of tables: Products Product-Types Attributes ProductTypes-Attributes-Allowed Product-Attributes

Product-Attributes-Allowed has an FK to product types, FK to attributes. If an entry exists in the table for a specific combination of product type and attribute, that product type can have that attribute.

Product-Attributes directly references the Product and the ProductTypes-Attributes-Allowed table with FKs. The Product-Attributes table will hold the information specific to that product, while the Attributes table holds information about the attribute in general (display names, units, etc). You will have to jump an extra table to link the attribute values with the attributes "meta data," but you can at least enforce product type similarity.

EDIT, would not fit as a comment below:

@Willem-Aart These can all be properties of an Attribute, stored in the "Attribute" table. For example, you can have a string "DataType" that holds information about the data type of the value that is to be stored in the "Product-Attributes" table. This would require the data to be stored as a blob (or some other universally castable data type like char[]) in the Product-Attributes table. Or, you could have separate columns for each foreseen data type, and leave the "wrong" data types for the attribute blank. You can have a constraint to force at least one of the columns to be non-null.

To enforce a range of values, at least for numeric attributes, you can set those as columns in the Attribute table as well. "Max_Allowable_Value," for example.

Adding functionality/flexibility to your database often results in added complexity.

Michael
  • 425
  • 2
  • 9
  • While it's an improvement upon EAV, it still leaves me with solving the problem of enforcing correct attribute values, data types and non-null values. I know that an application can handle these things, but I'd rather rely on the database to enforce correctness and consistency. – Willem-Aart Aug 28 '16 at 21:53
  • I agree with Michael - A database is a data store. An application is where you put "business logic". The database can keep the "rules" (eg, AllowedAttributes) for the business logic, but it is limited and/or clumsy at providing type checking, range checking, etc. – Rick James Aug 28 '16 at 22:46