a brought question, but still. There is an online store of computer equipment. The well-known problem is properties, technician parameters. Everyone has its own meaning to the same table. After Google and hundreds of articles, came to the conclusion that the UDA is the optimal option.
3 Tables:
- Products (general information about the product, name, price, etc.)
- prod_attributes (properties, parameters)
- prod_values (communication properties and product characteristics).
Thinking can break the properties and characteristics on tables with a progress to the type of products Ala Notebook_attr and Notebook_Values, because Product types no more than 10.
What will advise experts? Maybe someone has already developed the architecture of the relational base for the standard simple online store Ala www.techno-city.by. And share your experience.
ps. I ask about Nosql not to write. I can not believe that all small online shops on MONGO, etc.
Answer 1, Authority 100%
Look the structure dotplant2 is CMS for online stores on Yii.
If briefly, the structure is approximately as follows:
- Product – the basic properties of the product, the price, a bunch with subsidiaries (modifications) via Parent_id
- Product_EAV – what can be stored as EAV and why it is hardly necessary to search / filter
- Product_Property – Characteristics of goods that do not accept multiple values. The columns are added dynamically when a new possible property is specified in the database. In essence, Table Inheritance. Minus approach – redundancy with a large number of different types of product properties.
- property_static_values - a list of possible values for the properties of the “Select from the list”
- Object_Static_Values - Matching goods and ID value from the previous table
In my opinion, it is the most optimal structure for heterogeneous data, when there is nothing except MySQL.
But if it is possible, it is better to use a simple storage structure (like yours) and some Elastic Search or Mongodb for filtering / search.
Answer 2
What you describe is the most common solution to this task in terms of relational databases.
But it, in my opinion, is redundant.
I’ll tell you more – for this purpose, the database is not needed. Recently came across a similar problem. Thought-thinking which database, what a structure.
and then they stopped thinking and counted: several thousand goods, the description of the properties of each occupies any other (I do not remember) bytes on average. And in general, all our descriptions of properties (an analogue of your product prod_attributes) of goods are laid in less than 200 kilobytes.
Thoughts further: the data is changed extremely rarely, the transaction isolation is not necessary, the data is used extremely often (in fact, it is the most demanded information for the online store site).
Data can be organized in the form convenient for a specific programming language structure (array, list, tuple, list of lists, etc., etc.) and it will take it quite a bit of place (well, for modern servers hundreds of kilobytes of RAM).
implemented such a system: when loading a web application from a disk from a regular text file, the properties of goods are read and placed in RAM in the form of a convenient structure convenient for future work.
When updating properties (which occurs once a day), a special URI jershits and re-read the file with data and the overwroking this structure.
Repeat: Database for this task is not needed at all.