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?