Product Attribute System While Avoiding EAV

Question

I'm working on building an attribute system for products. The issue that I'm running into is that various products can have very different attribute requirements.

Some E-commerce websites such as Magento use an EAV system. That's something that I'd like to avoid due to performance issues, database cleanliness/complexity/control.

So far I'm leaning towards using a variety of tables. For example I might have a table of attributes that pertain to medical devices and then a different table that is for toys and games. Any attributes that are generic would just fall into the actual product table. If I do choose to go with this option, then I suppose my product table would have a column which represents which attribute table is used.

I'm not actually a Database Administrator, so I don't really know what's best. I hope that someone is able to give me some insights into a good implementation or acknowledge that my current thought process is in the right direction.

Thank you

Answer 1

As a horse_with_no_name suggested, I would also go with hstore if you are using PostgreSQL becuase I think its the easiest to morph back to regular table structure if you decide you need that.

There are a couple of features that make hstore pretty nice for your problem:

1) Indexing is pretty fast

2) You can query hstore column with something like

SELECT p.product_name, properties->'color' As color, properties->'size'::numeric As size
     FROM products;

So makes it good for reporting simply by wrapping your different product lines in a view

3) The main downside with hstore is keeping track of properties specific to a particular product type, however you can easily compensate for this with PostgreSQL type system

as demonstrated here: https://stackoverflow.com/questions/16092890/how-can-i-translate-a-postgresql-hstore-column-into-a-row

So you can define say a properties type for shoes called CREATE TYPE shoe_properties(color text, size numeric);

or just use a lookup table of property types and their corresponding data types for editing purposes. Then if you decide later you really want to go with the separate table for properties approach, your job would be simple by just creating a typed table from your type and using popular_record to morph your hstore into a row for insert into table.

Answer 2

Your question looks like an instance of a very widespread pattern. This pattern goes by the name "generalization and specialization" in Extended ER modeling. It also goes by the name "classes and subclasses" (or "types and subtypes") in object modeling.

When designing an object oriented data structure, the concept of inheritance plays a key role in the design. When designing a relational data model, the designer is faced with the reality that the relational data model does not include the concept of inheritance.

So the designer is forced to implement inheritance through a workaraound, by designing tables that work more or less the way inheritance works. This situation has been studied before, and some fairly good designs have been used with fairly good results.

Two of these designs go by the names "Single Table Inheritance" and "Class Table Inheritance". These two designs are well presented by Martin Fowler in one of his books. You can see a summary at these two web pages:

http://www.martinfowler.com/eaaCatalog/singleTableInheritance.html http://martinfowler.com/eaaCatalog/classTableInheritance.html

You can choose which one depending on the specifics of your case.

In conjunction with class table inheritance, there is a technique known as Shared primary key. Shared primary key involves defining a key to the subclass tables that is both a primary key and also a foreign key that references a row in the class table. This does two things: it enforces the one-to-one nature of these relationships and it makes the relevant joins simple, easy, and fast.

You can see this technique illustrated over in the StackOverflow area:

https://stackoverflow.com/questions/tagged/shared-primary-key

These three designs or techniques are not perfect, and they require a little work on the part of the application programmer. But they go a long way to meeting a very commonplace need.