Which schema is better for a shopping project? 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 -&gt; (id, name, brand_id, description, category_id)
categories -&gt; (id, name)
category_fields -&gt; (id, category_id, field_name, field_type)  
product_fields -&gt; (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 -&gt; (id, name, brand_id, description, category_id, (json) category_values)
categories -&gt; (id, name)
category_fields -&gt; (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 -&gt; (id, name, brand_id, description, category_table)
products_cellphones -&gt; (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?
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.