Product attribute schema design, mixing solutions

Question

I am working on a product database in MySQL and the products can be multiple types with different attributes. The number of attributes and there datatype vary. I read about some design patterns (post) used in this situation, but none of them looks promising. I am thinking about mixing them somehow.

Lets say, I have these product types (D - dimension, but could be attribute or entity...):

Product_type1: D1 - int, D2 - int, D3 - string, D4 - date, D5 - string

Product_type2: D1 - int, D2 - string, D3 - date, D4 - int, D5 - string, D6 - int

Solution 1:

I store a fix number of attributes of every datatype for a product, let's say 3 int field, 2 date, 2 string. The most important attributes stored here (important regarding indexing and searching). The rest of the attributes are stored in a JSON field or an array format.

Solution 2:

Store everything in indexed JSON field and search in the table with 'LIKE' queries.

How fast is a Like query in MySQL when you have a big product database (20 000 - 30 000 products). Is solution 1 better or not?

Answer 1

Put common things (price, SKU) in MySQL columns; index them if needed.

Put the rest in a JSON column. Do not try to use LIKE to search that.

Instead, first filter on what you can from the other columns, then use your application language to finish the filtering.

To save on space and bandwidth, compress (in the client) the JSON string and put it into a BLOB column.

Write your user-interface to encourage them to filter on the real columns, only reluctantly let them specify the other things.

See also MariaDB's "Dynamic Columns; it's been available for several releases. In the future, 5.7 promises to have a bunch of JSON tools.