The most generic e-shop DB schema

Architecturedatabase-design

Let's say I create a schema for an e-shop what potentially sells everything: laptops, phones, tires, snacks etc.
Considering this, I cannot create a new table/entity for each good category the shop sells and describe the good's properties by new attributes.

So, I went to more generic model to describe each product

To me, it seems fine but as soon as I've tried to implement this solution, it's appeared to impossible to execute any query like

select names and prices of all laptops where ssd capacity >= 512 and weight < 2kg and price <= 2000 money

How is possible to change this schema and keep the ability to store information about any product?

Best Answer

Your query would look like this

SELECT Name 
FROM ARTICLE 
WHERE Id_Article_CATEGORY IN (SELECT ID FROM Article_category WHERE Name = 'Laptop')
AND ID IN (SELECT id_ARTiLCE FROM Article_Property WHERE id_PROPERTY IN (SELECT ID FROM Property WHERE (Name =  'SSD' AND Unit  >= 512) OR (Name = 'weight ' AND Unit < 2)))
AND price <= 2000;

article_property is a bridge table, you need there two columns atleast id-article and id_property to link both tables. when you have a property you need to get its id and see if there are articles that have this property.

that is valid for all bridge tables in your eer.

at this point you start with a schema and change it when the need arises. Your structure is flexible yur programming too, you you can realize almost anything.

if you find a weak pint you can change the structure by adding columns, removing is more complicated, because tigger functions and so on have to be checked.

you should still go back and examine existing e-commerce solutions, for your basic needs there a some tutorials to implement it, also they have a gui in php or python with a functioning framework and even when you want to implement your own look for laravel and other frameworks,that help you with a lot of functions, there is no need to invent the wheel all over again

Related Question