Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I'm working on a business-to-customer project that has variety of product types. There are a few properties like name, description, brand_id that each product has but there are also many specialized properties for different products.

I'm looking for a reasonable solution for handling different types of products like cell phones and air conditioners. For example a cell phone has a property called CPU whereas an air condition doesn't have that property. Also an air condition has a property called BTU whereas a cell phone doesn't have a property like BTU.

The thing I want to do is a classic category based product system. Users will be able to create categories that contains different fields like CPU of BTU and when they want to create a product, they will be able to enter values for these fields to that product.

The SQL world, I see that many projects use a schema like this one:

products -> (id, name, brand_id, description, category_id)
categories -> (id, name)
category_fields -> (id, category_id, field_name, field_type)  
product_fields -> (product_id, category_field_id, value)

However I wonder that if there is a better solution for this problem. There are two different solutions came into my mind.

products -> (id, name, brand_id, description, category_id, (json) category_values)
categories -> (id, name)
category_fields -> (id, category_id, field_name, field_type) 

As you may noticed there is a column called category_values in products table. This column is JSON type (which Postgresql has) and I will index the keys that I need to search.

The other schema:

products -> (id, name, brand_id, description, category_table)
products_cellphones -> (product_id, memory, cpu, gps ...)
products_...

In this schema, when a user creates a category the system will create a new table with its fields on database in runtime. And I will create a view that generates the sql selects on the fly and executes them.

Which way should I choice, or do you have any other solutions?

share|improve this question

closed as primarily opinion-based by Chris Travers, dezso, Mark Storey-Smith, bluefeet, RolandoMySQLDBA Sep 15 '13 at 0:33

Many good questions generate some degree of opinion based on expert experience, but answers to this question will tend to be almost entirely based on opinions, rather than facts, references, or specific expertise.If this question can be reworded to fit the rules in the help center, please edit the question.

2 Answers 2

up vote 2 down vote accepted

You are much better off using the EAV (entity-attribute-value) approach for a product feature catalog. The approach is transportable to any SQL platform as it doesn't rely on a particular feature of Postgresql (JSON columns).

All of the usual arguments against EAV don't apply to a product feature catalog, since product feature values are generally only regurgitated into a list or worst case into a comparison table.

Using a JSON column type takes your ability to enforce any data constraints out of the database and forces it into your application logic. Also, using one attributes table for every category has the following disadvantages:

  • It doesn't scale well if you eventually have hundreds of categories (or more).

  • If you change the allowable attributes on a category you have to change a table definition instead of just adding or removing rows in category_fields

  • You may still end up with sparsely populated tables if the product has many potential features only a small subset of which are known.

It is not especially difficult to retrieve data about a product with specific features. It is arguably easier to create a dynamic SQL using the EAV model than it would be using the table-per-category model. In table-per-category, you need reflection (or your JSON) to find out what the feature column names are. Then you can build a list of items for a where clause. In the EAV model, the WHERE X AND Y AND Z becomes INNER JOIN X INNER JOIN Y INNER JOIN Z, so the query is a little more complicated, but the logic to build the query is still totally table-driven and it will be more than scalable enough if you have the proper indexes built.

There are a lot of reasons not to use EAV as a general approach. Those reasons don't apply to a product feature catalog so there is nothing wrong with EAV in this specific application.

share|improve this answer

You actually have two options here.

The first is to go with your second schema. This would work very well when you have a limited number of categories, and it makes things easier to manage or enforce data constraints.

However you have another, simpler option as well if the extended data isn't really critical. You could replace the join tables with a json field for storing the additional information. This has the advantage of being quite simple to maintain, but will become really full-featured only with PostgreSQL 9.3 or with a willingness to code pl/perlu functions.

share|improve this answer
    
What do you think about the classic way that I mentioned before my solutions? Does this approach scale when there is a few thousands of products? –  burak emre Sep 7 '13 at 16:18
    
BTW, I couldn't understand "if the extended data isn't really critical" part. Why does JSON type affect reliability? –  burak emre Sep 7 '13 at 16:20
    
@burakemre, what you lose with JSON is an ability to do real type enforcement on a db schema level, so you can't be sure that all the required fields are filled in on the db level. –  Chris Travers Sep 8 '13 at 7:47
    
Also regarding the classic way, I think your problem is that you end up with a lot of the problems of EAV modelling. This can be used, but it means that queries like "show me all hard drives with a capacity of 500GB, using an SATA interface, and with a speed of 7200 RPM" becomes an amazingly complex query which does not scale. –  Chris Travers Sep 8 '13 at 7:50

Not the answer you're looking for? Browse other questions tagged or ask your own question.