CODEWITHSUNDEEP
mysqldatabase-designlaravel-5relational-databaseentity-attribute-value
Willem-Aart
Willem-Aart

Reputation: 2440

EAV is 'bad', but how about the maintainability of class table inheritance?

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?

Upvotes: 2

Views: 824

Answers (1)

Michael
Michael

Reputation: 445

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.

Upvotes: 1

Related Questions