Designing the basis of an e-commerce site

[maher]

Hello, 

I would like to create a e-commerce site with different products. In particular: 
If I add an X product, this product will have a variable attribute and the price of this product will be related to this attribute. 
Example: 

If the product X1 has a "weight" attribute, I can put this in place: 
1 / weight = 0.5kg ==> price = 2usd 
2 / weight = 1kg ==> price = 3usd 

If the product X2 has two attributes "Size" and "type", I can set up this: 
1 / size = 0.5m & type = t1 ==> price = 10usd 
2 / size = 0.5m & type = t2 ==> price = 13usd 
2 / = 1.5m & type = t1 ==> price = 11.5usd 

I hope I'm clear enough ...
My question is: how can I put this in place with a MySQL database? What will be the model? (the attributes are dynamic, the administrator chooses the attributes and sets the price).

 

[Helsa]

Hi @maher

The price depends on the weight (product X1) and the size and type (product X2).
You make two tables, one of which is dedicated to product X1 and the other to product X2.
For product X1, your primary key will be weight and price information, depending on this primary key.
For product X2, your primary key will be composed of size and type, and price information, also dependent on this primary key.
If we stop there, the solution is obvious and easy to implement.
But I guess your model does not just stop at two products.

If we generalize to several products (how many?), It also depends on the complexity of your functional dependence.
It would then be a single table with a multicriteria, consisting of:
-> product name X1, X2, ...
-> first criterion: "weight".
-> second criterion: "size".
-> third criterion: "type".
-> information: "price".

The name of the product + the criteria will constitute your primary key.

Advantage: only one table!
Disadvantage: too many criteria to NULL, so increase the volumetry of your table with columns not filled in.

The primary key can be replaced by an auto increment, or a technical key.
But you should use as many indexes as you have of criteria groups.
For example, an index composed of product + first criterion.
Another index composed of product + second criterion + third criterion.
And so on, to manage your selection by multicriteria.
In this case, your indexes are sargable, but this does not solve the problem of columns not filled in which will cause a greater volume.

To solve the problem of volumetry, you have to burst your table in as much product (1 table = 1 product), because each product will be a special case.

We go back to the first case described above.
But if you end up with thousands of different products, you will have too many tables to manage and therefore great complexity.
Imagine one table per product, with only three lines. What a waste!

Solution, use the meta-data, except that MySql does not know how to do it!
Basically, the meta-data principle is to create a data model that will handle other data models.
Schematically, your database will have to cohabit in the same table, several different structures of data.