1

I'm in the process of building a new system which will require an extendable data model. It's quite complex, but the part of the system which requires this structure is very relatable to the product part of an ecommerce system, so I'll use that as a case.

Imagine a set of companies. Each company has a range of products, which has some basic attributes such as name, sku, description, price etc. Alongside these basic attributes, the company should be able to create n custom product attributes, which belongs to the company (i.e. Foo Corp. shouldn't be able to see Acme Inc.'s custom attributes). Beside that, each company should be able to translate these attributes for each product; so each custom attribute value is basically constructed from an attribute, a product, language and a value.

I do understand that the EAV structure is an anti-pattern if you store an fixed amount of attributes and the one in need of extending the amount of attributes isn't the owner of the system.

So my question is - how would you achieve this? It the EAV structure really an anti pattern in this case? Magento is a classic example of someone using this design, and it kinda seems like they built a monster and had to 'fix' it by implementing flat index tables. But are there any other database design pattern that allows for this amount of flexibility?

rdiz
  • 6,136
  • 1
  • 29
  • 41
  • One alternative is dynamically modifying the schema, i.e. running DDL like ALTER TABLE statements programmatically. – reaanb Jul 10 '15 at 18:33

1 Answers1

6

It's useful to understand why EAV is so often considered an anti-pattern, and how well those criticisms apply to your case. It's also useful to understand why so many people are drawn to EAV, and what positive benefits they see in it.

The biggest problem with most EAV databases is that it is nearly impossible to write any kind of systematic and integrated extraction or reporting system, in order to turn the data into useful information.

The same sort of report that can be developed in less than an hour's work when given well managed data in a well designed relational database can take weeks to develop in an EAV database. The reason is that the data is fundamentally unanalyzed data that has been stored without regard for its inherent logical structure.

This turns out to be very closely related to the reason why some other people are so drawn to EAV. You can bypass data analysis completely. And logical database design is practically automatic, since all EAV databases have the same table structure. When you've built one, you've built them all.

This gives you a handle on a few things to look for in any case you might consider. Is the logical structure of the database truly dynamic and unknowable? Or are people just jumping to that conclusion, because the schedule allows no time for data analysis and database design?

How will the data be used? Are people expecting to do the kind of retrieval that is classically expected of data in a database? Or are they planning on treating each attempted retrieval as an exploration into uncharted territory?

In your particular case, I would ask how long it's going to be before somebody "important" asks for product data to be combined across companies, even though each of them maintains its own knowledge base of products and attributes? If that's really never going to happen, maybe you will be ok. If not, you'd better move on before management realizes how hard it is to use this data.

Walter Mitty
  • 18,205
  • 2
  • 28
  • 58
  • Thank you, Walter, for taking your time to write such a comprehensive answer. I guess I already thought about the conclusions you made above, but it's good to get another view on it. At this time, I am actually thinking a lot about the analysis and database design at this time, as it's the foundation of the entire application and IMO the primary driver for scalability and maintainability. Consider a SaaS ecommerce application like Shopify we're not reinventing the wheel, trust me - it's just easier to establish a shared frame of reference that way)... – rdiz Jul 12 '15 at 12:00
  • ... The clients (which I called companies in the original post) should be able to create an unknown amount fo custom attributes for their range of products, which I think is what draws me toward the EAV structure as we cannot possibly guess every the attribute needs for every client. There are some default ones - such as SKU, name, description etc. - but from there on, we really won't know the attributes. Are you aware of any "cleaner", common alternative to EAV? Now matter how i try to think of it, I always end up with an EAV(-ish) structure. – rdiz Jul 12 '15 at 12:00
  • As you describe it, the customer specific attributes are inherently unknowable. I don't know of any better alternative to EAV in that situation. You might make a real effort to expand the list of common attributes, based on what other people have done in e-commerce, perhaps in some particular industry. – Walter Mitty Jul 12 '15 at 12:08