C
C
Cyril2017-09-14 21:21:50
PostgreSQL
Cyril, 2017-09-14 21:21:50

How to properly store filtering parameters in the products table?

There is a certain table products , which contains all kinds of products. These products belong to a wide variety of categories - from irons to audio subwoofers.
The question arose, how to store all these products in one table if each product has its own set of filters? That is, for example, for the category "iron" in the products table there should be fields "maximum temperature", "wool can be ironed", etc. And for the "audio subwoofer" category, the products table should contain the fields "output power", "speaker cabinet material", etc.
Obviously, it turns out to be a mess. For example, for a product in the category "iron"
How to store products in a relational database? Like PostgreSQL? Do you really have to create your own set of filters for each category?
Is it possible, as an option, to leave only the most important (common for all categories) fields in the products table , and store the rest in the filters field as a string? For the web application to "parse" a string from this field. That is, the table looks like this:

CREATE TABLE `products` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `category_id` INT(11),
  `name` VARCHAR(255),
  `type_id` INT(11),  -- Общий для всех фильтр "Тип"
  `vendor_id` INT(11),  -- Общий для всех фильтр "Производитель"
  `filters` VARCHAR(255),  -- Тут все специфические фильтры
)

PS: We cannot change the DBMS. PostgreSQL only.

Answer the question

In order to leave comments, you need to log in

1 answer(s)
G
gill-sama, 2017-09-14
@belyaevcyrill

The best option is to create 2 separate tables - 1 for products, and 2 for features, as well as 1 table for linking them storing id

table products (id serial, category_id small int, name text, vendor text)
table property(id serial,name text,  value)
table product_property(product_id big int, property_id big int)

Didn't find what you were looking for?

Ask your question

Ask a Question

731 491 924 answers to any question