-1

So I want to design tables and relationships for the product module of an e-commerce solution. For clarification purposes, I will use an example of a highly customisable product that will have:

  • Several "classical" attributes like color or size (selected through a normal dropdown menu, with several options, e.g. red, blue or black for color or small, medium and large for size).
  • But in addition, some custom attributes (basically text fields or numeric fields, of course selected through text or number input fields) which the users will freely change and define.

The price will depend on every attribute (dropdown + custom), so I will have to write additional logic in both client and server side when appropriate.

My tries so far match the solution given in this question. It works good with "normal" attributes (dropdown ones), but it does not scale well to custom text or number fields because in my scenario, a variant_value table just does not have any sense.

Any ideas? Thanks in advance.

Rick James
  • 135,179
  • 13
  • 127
  • 222
andcl
  • 3,342
  • 7
  • 33
  • 61

1 Answers1

0
  1. Learn about the EAV schema pattern (I added a tag).
  2. Implement it.
  3. Then come back an whine about all of its problems.

In particular, the approved Answer in the mentioned Question suggest normalizing variant_value. This is a way to make EAV worse. Also the Answer there has a number of unnecessary AUTO_INCREMENT ids on tables that have perfectly good "natural" PRIMARY KEYs. That slows down some queries.

WordPress implements EAV. Their "postmeta" table is poorly implemented; here are suggestions on improving it: http://mysql.rjweb.org/doc.php/index_cookbook_mysql#speeding_up_wp_postmeta

Here is my blog on EAV, some of its problems, and some partial solutions: http://mysql.rjweb.org/doc.php/eav

Rick James
  • 135,179
  • 13
  • 127
  • 222
  • Thanks, but I am asking you just not to have to implement EAV and finally realise that it does not fit my needs because of its drawbacks... So, would you use it with your experience? – andcl Jan 05 '19 at 13:03
  • @andcl - My eav link discusses an alternative using a mixture of MySQL columns and JSON; the former for most searching, the latter for miscellany. – Rick James Jan 06 '19 at 01:30