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

  • 2
    I think most of the fears of EAV are just fear-mongering. I've used it quite extensively and I found it to work better than managing sub-types etc. through a variety of specific tables. Some of the cleanliness that you claim goes the other way, too, for example in your preferred approach, if you need to add a new product type you need to add a new table and new access methods, if the same attribute gets added to multiple products (but not all) you have to make the same tedious change to all of those tables and access methods. – Aaron Bertrand Jan 31 '15 at 1:48
  • I've blogged about my experiences with EAV: sqlblog.com/blogs/aaron_bertrand/archive/2009/11/19/… That article is SQL Server specific, but the core concepts are the same on any platform. Be careful not to discard a design based solely on articles by people with bias against that design. You need to weigh the pros and cons of all approaches, not just the cons of one. – Aaron Bertrand Jan 31 '15 at 1:50
  • Check out the hstore data type in Postgres. It's a fast and efficient key/value store: postgresql.org/docs/current/static/hstore.html – a_horse_with_no_name Jan 31 '15 at 8:43
  • The trouble with EAV is that it's difficult to use the data in standard reports, extracts, and other typically useful outputs. EAV is great for capturing data whose structure is yet to be defined. – Walter Mitty Jan 31 '15 at 11:56
  • 1
    I've discussed using EAV for product catalogs on Stack Overflow here: stackoverflow.com/questions/11779252/… - EAV is evil, except when it is actually the best approach. Attributes of products in a catalog is one where EAV is often the most efficient and suitable design. – Joel Brown Jan 31 '15 at 13:38

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.

| improve this answer | |
  • I think I'll try going with this.. at least for now. Thanks. – user35632 Feb 27 '15 at 22:29

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:


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.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy