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 ...)

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?


2 Answers 2


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.


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.

  • 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, 2013 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, 2013 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. Sep 8, 2013 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. Sep 8, 2013 at 7:50

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