The organization of the database structure

Hi all.
As a hobby working on my small project which is online shop for RoR. Faced with the problem of the organization of the database. It is necessary that the goods were different options, depending on their category or type (how better?). On found 4 solution of the problem, the easiest of which offers the following structure:
the Products table for the data from all positions is the same for each type of goods still on the plate with the pointer to the parent parameters.
I don't like this method, and others worse. The base is bulky, the structure is complicated well and the code, therefore, too. Already going to do so because anything sensible and not come up.

What can the smart people tell? Thank you very much.
October 8th 19 at 02:05
5 answers
October 8th 19 at 02:07
Normally designed database is in fourth normal form will help You.
Kept up to hundreds of millions of rows and the sampling was done properly.

PS: why nobody does not read data, but each database uses?
Thanks for the tip - lavon_Cremin commented on October 8th 19 at 02:10
October 8th 19 at 02:09
I like seeing the structure:
1) the table with the goods
2) table to properties (list of properties, names, types, etc. need data)
3) a table of bundles of goods and properties (ID item, ID properties, the property value)

Any additional tables and quite transparent and not bulky
The approach is used quite frequently is called a UDA(user defined attributes) but has obvious drawbacks:

1) to display the list of products with their attributes in table form, will have to do the same joynow how many goods attributes. This can be a very difficult request.
2) the Request will have to build dynamically, i.e. his plan at the stage of developing is not known.
3) user-defined attribute Values can not be used by the application logic, because their codes are in the process of operation can change.

Usually apply this approach in order to allow the user to satisfy their wishlist without rewriting the application, if this wishlist is certainly not known. Typically, these parameters are not shown in tabular form only in the form of extended properties for each specific product. They are not based logic of the application.

Probably shouldn't this approach be used if the attributes of a product are notoriously known. The author's decision, though looks at first sight bulky, in fact, is far more simple and practical to use. If to use only as a Supplement to the proposed by the author. It's like the text field "comment" to the record. No effect, and the user can use it on your own system, do not care, user pleasant. - lavon_Cremin commented on October 8th 19 at 02:12
4) not Very convenient to select these properties, applying the operation to the selection predicates on the values of different properties. - Logan_Reilly commented on October 8th 19 at 02:15
October 8th 19 at 02:11
The most common method is to store all properties in a separate table (product code, code soitsu, value). But it is slow, a little faster is to store the bundles of properties for different product categories in the tables for those products. But he does not allow without the union s request the products from different categories. The fastest and most effective but "non-standard" way is to use a document-oriented database, the supplying of a collection of objects with different properties, such as MongoDB.
But he does not allow without the union s request the products from different categories.

There seems to asked to use the master table. From it are selected, the external connection pull the attributes. Without Union. - lavon_Cremin commented on October 8th 19 at 02:14
Unions for sampling on the properties of the items together from different categories, for example choose all tablets or ebook reader that weighs less than 600g. In Mongo is possible to build indexes on properties only for those objects which they are. - Logan_Reilly commented on October 8th 19 at 02:17
As I understand the author's idea, it is common for all the product categories attributes are intended to store in the master table. Mass-dimensional parameters can definitely characterize any product. In separate tables to make only the properties that are specific to a certain category of goods.

If you still can find a few categories of goods shall be entitled to one property, then Yes, you will have to use a Union so that the index could be used. But I think that in a situation when the practice is not the exception but the rule, just by itself suggests a multi-level categorization. Take for example the same Yandex market. Filtering in the advanced settings there is available only after selecting a specific category. It's easy, it works. - Logan_Reilly commented on October 8th 19 at 02:20
October 8th 19 at 02:13
Documents without a clear schema usually stored in NoSQL.
But the search will still have to compile multiple schemas for categories, a La the characteristics of the railway, features of monitors,...
And not just for search, they usually are used to validate the set of properties of the document type. - lavon_Cremin commented on October 8th 19 at 02:16
October 8th 19 at 02:15
Without search these properties for a storage option. So I have additional user fields are kept user_id, var VARCHAR(55), val VARCHAR(255), search for it happens very rarely and only in the admin panel, so it everything works fine.
But if you do the search, then as already said, NoSQL(e.g. mongodb) would be more correct to use. The search will work at least quickly with nested objects, and even arrays.

Find more questions by tags DatabasesRuby on RailsAlgorithmsMySQL