Skip to main content
Database Administrators

Return to Question

replaced http://stackoverflow.com/ with https://stackoverflow.com/
Source Link
Community Bot
Community Bot
  • 1

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 (postpost) 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?

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?

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?

Source Link
szabkel
szabkel
  • 264
  • 2
  • 11

Product attribute schema design, mixing solutions

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?