Как убрать дублирование свойств в SQL?

Question

[Kermet123]

Доброго времени суток.
Пишу базу данных для интернет магазина, и столкнулся с такой проблемой.
Есть несколько таблиц - тип товара, брэнд, сами товары, свойства и значения этих свойств.
Такие свойства, как например цвет и вес, есть практически у всех товаров. У меня сущность value (свойства товара) ссылается на id товара (product_id) и на id свойства (property_id). И получается, что свойства в таблице property могут повторяться (например свойство цвет есть для футболок и для носков).
Пример: свойства цвет и размер повторяются (для каждой категории/типа товаров)
mysql> select * from property;
+----+--------+---------+
| id | name | type_id |
+----+--------+---------+
| 1 | size | 1 |
| 2 | color | 1 |
| 3 | fabric | 1 |
| 4 | size | 2 |
| 5 | color | 2 |
+----+--------+---------+
5 rows in set (0,00 sec)

Как можно избавиться от этого дублирования?
Я пробовал сделать так, чтобы сущность property не ссылалась на конкретный тип товара, и при создании новой записи в таблице value, мы просто указываем property_id и значение. Но тогда теряется функционал, с помощью которого мы может получить все свойства для конкретного типа (select * from property where type_id=2)

Вот мой sql код:

DROP DATABASE IF EXISTS test_db;

CREATE DATABASE test_db;

USE test_db;



create table type(
  id int auto_increment primary key,
  name varchar(255) unique not null
);

create table brand(
  id int auto_increment primary key,
  name varchar(255) unique not null
);

create table product(
  id int auto_increment primary key,
  name varchar(255) unique not null,
  type_id int,
  foreign key(type_id) references type(id),
  brand_id int,
  foreign key(brand_id) references brand(id)
);

create table property(
  id int auto_increment primary key,
  name varchar(255) not null,
  type_id int,
  foreign key(type_id) references type(id)
);

create table value(
  id int auto_increment primary key,
  product_id int,
  property_id int,
  value varchar(255) not null,
  foreign key(product_id) references product(id),
  foreign key(property_id) references property(id)
);


-- create types

insert into type(name) values ('футболки');
insert into type(name) values ('носки');

-- create brands

insert into brand(name) values ('Nike');
insert into brand(name) values ('Adidas');

-- create property
insert into property(name, type_id) values('size', 1);
insert into property(name, type_id) values('color', 1);
insert into property(name, type_id) values('fabric', 1);

insert into property(name, type_id) values('size', 2);
insert into property(name, type_id) values('color', 2);

-- products
insert into product(name, type_id, brand_id) values ('Футболка', 1, 1);
insert into product(name, type_id, brand_id) values ('Носки', 2, 1);

-- values
insert into value(product_id, property_id, value) values (1, 1, 46);
insert into value(product_id, property_id, value) values (1, 2, 'red');
insert into value(product_id, property_id, value) values (1, 3, 'cotton');

insert into value(product_id, property_id, value) values (2, 3, 46);
insert into value(product_id, property_id, value) values (2, 4, 'red');

Я предполагаю, что надо для типа товара хранить массив с айдишниками свойств товаров.
Но тогда, насколько я понимаю, нельзя будет работать с этим массивом на уровне sql, надо будет это парсить с помощью, например js, но я не уверен.
Буду благодарен за любую помощь.

Comments

[Adamos]

Свел все цвета всех товаров в одну таблицу, потом сел делать фильтр.
И в списке вариантов цветов ноутбука увидел "прозрачный", "в горошек", "нет цвета"...

А чтобы работать с массивами на уровне SQL - просто надо создавать массивы на уровне SQL.
То есть применять нормализацию, а не сваливание значений в кучу.

[Akina]

См. напр. "EAV pattern".

[Adamos]

А заодно, для взвешенного решения - "EAV antipattern" :)

[Akina]

Наименования свойств - отдельная таблица. А property, связующая между свойством и товаром, хранит только два ID.

Answer 1

[ThunderCat]

Сто раз обсуждалось. Для атрибутов применяется EAV и связывающие таблицы, для больших массивов данных так же добавляют фасетный поиск.