0

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?

8
  • hm can you provide more deatils about your tables especially where the columns are on your SELeCT also have you decided which rdms. to behonest there a a lot of e-commerce solutions have you tried one of them
    – nbk
    Commented Mar 20, 2021 at 12:42
  • @nbk I've looked at a lot of solutions on this forum and elsewhere on the Internet and the problem is either e-shop sells only one 1 type of goods/provides only 1 service and there is no need to have general solution or the product has only basic properties like color, weight, size and the most stupid - description. I guess people suppose to store all specific info in text format??? but rather first that they do not suppose to have big amount of products
    – 63350541
    Commented Mar 20, 2021 at 12:55
  • @nbk for now I'm tasting it in postgresql with DataGrip but it doesn't matter now. I'm interested in the ER diagram and the concept of such e-shop
    – 63350541
    Commented Mar 20, 2021 at 12:57
  • I would have a relation between properties and categories. Seems odd to be able to store ssd capacity for a snack. Commented Mar 20, 2021 at 14:24
  • @GerardH.Pille I've thought about this but if you have the relation between category and property how do you store values for a specific product? Do you create an associative table for entity Article and another associative table Property_Category? I guess no. It seems to me that is the matter of an owner to not allow a bottle of water to have a property HDD capacity: 8TB Moreover, even products from a same category can have different attributes like you've got a normal IPhone but at the same time you have a phone for paranoiac ppl without micro and camera only for chatting
    – 63350541
    Commented Mar 20, 2021 at 15:28

1 Answer 1

0

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

3
  • My bad. I didn't mention that the price is actually a property too. Anyway it would not be working good since it's impossible to select values of properties from Article_Property. Yes, we can ask for laptops with ssd >= 256 but we won't figure out how many GBs have the shown laptops. The same thing is with the price since it's a property. I suspect, if such a simple query takes so much space and time to type it - the DB has a bad structure.
    – 63350541
    Commented Mar 20, 2021 at 15:53
  • I swear I've, certainly, tried to find a solution on the Internet before write here. Mb I was looking for it bad but I really didn't find anything adequate. Either it's for only 1 type of product/service or you have only text field for text description of attributes
    – 63350541
    Commented Mar 20, 2021 at 15:55
  • no, that kind of structure is a normalized database and it is compltely normal. that kind of searches a re higly structorized and can be build in code. depending on the selected criteria., so you have todevelop a search page only once, but that can do woocommerce or others cloudways.com/blog/opensource-ecommerce-platforms for one person it is alot of work, so opensirce solutions are common. depending on your prefered language
    – nbk
    Commented Mar 20, 2021 at 16:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.