0

It's been a while since I last touched MySQL, but I'm now trying to figure out the best way to structure my database, plus I'm still a n00b at MySQL.

The database is going to be storing product details, which vary extremely from product to product, of four different products.

There is a little bit of overlap between one product and the other three, but almost zero overlap amongst the other three. This is due to the fact that the one product that overlaps with the other three has so many more data points than any other individual product.

Below is the inherent structure of the data -- aside from transaction info (e.g. price, type of transaction, shipping from, and the like) that will be common to all types of products -- whereby the letters represent product detail data points.

Product 1 | Product 2 | Product 3 | Product 4
----------+-----------+-----------+----------
A         | A         |           | 
B         | B         |           | 
C         | C         |           | 
D         | D         |           | 
E         | E         | E         | E
F         | F         | F         | 
G         | G         |           | G
H         |           | H         | 
I         |           | I         | 
J         |           | J         | 
K         |           | K         | 
L         |           | L         | 
M         |           |           | M
N         |           |           | 
O         |           |           | 
P         |           |           | 
Q         |           |           | 
R         |           |           | 
S         |           |           | 
T         |           |           | 
U         |           |           | 
V         |           |           | 
W         |           |           | 
X         |           |           | 
Y         |           |           | 
Z         |           |           | 
AA        |           |           | 
BB        |           |           | 
CC        |           |           | 
DD        |           |           | 
EE        |           |           | 
FF        |           |           | 
GG        |           |           | 
HH        |           |           | 
II        |           |           | 
JJ        |           |           | 
KK        |           |           |
----------+-----------+-----------+----------

I'm going to be displaying the posts chronologically by default. I'm also going to be implementing search, filter, and sort functionalities, so the structure has to be considerate of this.

Honestly I'm not sure what to do. Part of me is saying lump all of the products together into one table, but another part of me isn't so sure that's the most efficient approach.

Does anybody have any ideas or advice?

Rick James
  • 135,179
  • 13
  • 127
  • 222
oldboy
  • 5,729
  • 6
  • 38
  • 86
  • 1
    I would store attributes that are common to all products in one table, and then use an EAV for the rest. I like to split my EAV up by datatype, so I have one table for integer type things, and another for strings, etc. It's still a pain to work with, though. – Strawberry Jun 02 '19 at 06:01
  • 1
    I suggest reading [this link](https://stackoverflow.com/questions/2945045/database-design-products-attributes-what-is-better-option-for-product-attri) – Behzad Ghanadi Jun 02 '19 at 06:02
  • whats an EAV? like i said, im a n00b at mysql. notably, 99% of the data points will be text other than a few boolean values – oldboy Jun 02 '19 at 06:02
  • Those sorts of questions are better directed elsewhere – Strawberry Jun 02 '19 at 07:39
  • 1
    My advice is contained [_here_](http://mysql.rjweb.org/doc.php/eav) – Rick James Jun 03 '19 at 05:06
  • @RickJames appreciate your feedback as always. although the products might not be searchable for all of attributes, im definitely going to be allowing filtering for all of the attributes, which is the one of two major reasons why im storing all of those attributes for each product – oldboy Jun 03 '19 at 06:26

0 Answers0