Various product options in the online store. Advantages and disadvantages of implementations

Question

Database design

Hello. I will give what implementation methods I came up with / spied on. There may be much better solutions.
Different variants of goods in the online store can be displayed in the following ways
- rozetka.com.ua/acer_liquid_mt_s120_silver/p144068/ - the pages are practically different. Each has its own price, its own images (name, may have its own promotions, etc.)
- shop.dinternal.com.ua/textbook/excellent/ - there is no product as such. It is just a set of other goods (although it can also have its own price. For example, for all goods together.) Another feature is the division of options into groups. In a simpler version, it is very often used in machine parts catalogs.

I have not seen any other options for displaying goods.
Now let's talk about solutions.
1. Products are completely different and are linked many to many through an adjacent table
Advantages
- anything can be implemented
Disadvantages
- a lot of information is duplicated
- it is difficult to implement information that applies to all products (general comments, product ratings by users, etc.)
- if the description the product has changed, you need to change it in all its variations.
2. Some kind of inheritance. There is a main product. Other goods are inherited.
By default, they do not have a single field filled in except for the id of the product from which inheritance occurs. When we display a product and it has a parent id field + the output field is empty, the value of the main product is taken.
Advantages
- comments, user ratings can be put on the parent product.
— information is filled in different for the goods. At the same time, we do not lose in flexibility
Disadvantages
- A product can have 30 different fields. It turns out that the goods-variations will all be empty. The database will be mildly clumsy
- In ORM, getting the fields of the parent should not differ from the usual getting of the fields of the product (for transparent work). On the other hand, when editing, the object should behave completely differently, giving out the real field values.
— In a database, some fields may be mandatory. Although, according to the current logic of the system, they should be empty.
3. Display the price of the product separately from the product and add the variation name field to it.This is the method I've come across the most. And he's the best in most cases.
Benefits
- No redundant information is stored. And the database is populated without spaces.
- Implements most of the tasks .
Disadvantages
- Requires significant modification of the code if you suddenly need to make an output like in a socket. And in general, it is poorly suited for this method of grouping goods
- Work with product prices becomes much more complicated.
— It practically eliminates the search for a specific product modification and also the display of several modifications in the catalog as different products.
I will be glad to any comments and advice. The goal is to create such a database structure that with minimal gestures it would be possible to implement the maximum number of ways to display goods.

Answer 1

since I am writing for the 2nd time, I will be brief;) I will not scare you with the number of implemented “warehouses”, I will immediately give an example of a fairly universal directory of goods (of course, in a simplified form;)
Table 1 - goods:
id, Name
Table 2 - params:
id, Name(for example - Manufacturer, Length, )
Table 3 - goods_params:
id, goods_id(foreign key), param_id(foreign key), Value
in such a scheme, the product has a name (in some cases it can be generated automatically from the filled data from goods_params - it will be like a general description of the product in the outlet), and all its characteristics are in goods_params, there are no empty fields, adding a product with new characteristics will add records to goods, params, goods_params. The width of the table will not grow. Hope the diagram is clear. Keys/indexes are not specified... Then it can be done for a long time... there are no problems with selection, search and comparison of goods here - it is solved tritely via SQL (something like an extended version of this approach works fine in an Oracle database under 1.5TB). well, how much this fantasy will fall on your orm is another question - you didn’t ask that ;)
well, where to store the price is usually a separate question;) although, in some cases, it will be included in the already described scheme ... in general, go for it;)

Answer 2

I couldn’t stand it, I came again ...;) I read what they write to me and you don’t even read yourself;) if it says “For a regular search by the name of the product, you will have to do a lot of gestures” and something else is meant, then only telepaths will help you guess;) and I don’t bother my telepaths on trifles;) so I answered what is written and not implied;)
you again demonstrate a lack of understanding of working with the database at a fairly free level;) this, as they say, no offense, but a fact;) in order to demonstrate part of your misconceptions, I will draw attention to the fact that the word EAV you like does not fundamentally prevent you from working with data in the future as if they were stored in a flat table;) i.e. you can get a selection like:
goods_id, goods_name, param1, param2, pram3,…
where the same empty fields will be if the product does not have this attribute. you can use a banal select to such a data set;) flexible storage is flexible, so as not to limit further work (greetings from K.O.;). the answer to the question of how to make such work more comfortable depends somewhat on the DBMS used and tasks, but usually view is suitable (have you tried using them at all?) ...
well, you can also make a smart face and ask about the performance of such a scheme;) knowing the expected size tables, the type of subd, the hardware used, and in some cases the number of users - usually it's easy to answer - can you handle it? :)

Answer 3

What prevents you from implementing all three options at once and using the most suitable one for each group of goods?

Answer 4

unfortunately, the right answer is to read about database design in general and normalization in particular… one of the criteria for a “correct structure” of a database is that your tables don't grow in width as you add new properties to your objects (in your case, it's va goods). Accordingly, one of the criteria for "bad structure" will be a lot of empty fields in most records, made "in reserve". Well, what about storing data in a database, relationships between tables, etc. Should NOT limit the display in any way - this is easy to understand by looking at the description of the MVC pattern. In general, this approach is usually justified - we normalize the database to the limit, having thought through the growth (if possible, of course;). Then, with sufficient knowledge of SQL, there is no problem finding something;) If you have a relational database and you want to work only through ORM, without customizing queries anywhere, then you will not be able to implement everything ...
PS all your 3 options, how to say it, are not feng shui ;)

Answer 5

pipets wrote for half an hour and everything was gone :( oh, these web editors ... I'll be back ...

Answer 6

This storage of characteristics is commonly known as Entity Attribute Value. I use this approach, but for "non-standard" fields. Although somewhat modified and sharpened specifically for the store. But the removal of all characteristics according to this principle is even more evil than hundreds of empty goods. For a regular search by product name, you will have to do a lot of gestures (and for searching by name and filtering by price, you can’t do without HAVING or a nested query at all). Moreover, the design of adding and editing a product should also not be a set of fields to fill out. And for this, we must go in one of three ways: score and display simply as a list, add for each characteristic a field responsible for the type of input (but what if the location of these inputs should also not be standard), make these fields static (leave what they are now). I believe that if headlong chasing the flexibility of the system (and EAV is undoubtedly the most flexible option), you can achieve such complexity in editing all this that we again come to the usual creation of tables, only in our implementation.