Проектирование реляционной базы данных

#mysql #sql #база_данных

Например, есть таблица Car. Поля - id, type, name. Type может быть много разных.
И для каждого Car по type могут быть дополнительные данные. А могут и не быть. 

Допустим есть type big. Для него нужно указать еще year. А для type light указать
shop и address. Получается, что сами Car должны быть в одной таблице, и все иметь id,
type и name. Но в определенных ситуациях они же должны иметь и доп. данные. 

Вариант 1 - добавить все нужные столбцы в таблицу Car. Получится, если учесть наш
пример - id, type, name, year, shop, address. Но для type big поля shop и address будут
всегда пустыми. А для type light пустым всегда будет year. А если для каждого типа
будет по ~10 - 100 доп. полей, то будут получаться записи с ~100 полями, ~90 из которых
пусты. 

Вариант 2 - основную инфу хранить в Car. Для дополнительной создать CarBig, и CarLight.
Но в этом случае  одним запросом не выберешь сразу все нужные данные, т.к. изначально
ведь не знаешь с какой таблицей объединяться.

Вариант 3 - дизайн как и в варианте 2. Для запросов делать left join для всех дополнительных
таблиц. Но если у нас допустим есть type middle, которому вообще отдельная таблица
не нужна, то для такого случая придется создавать таблицу и ему. CarMiddle. И она всегда
будет пуста. А если у нас появятся type middle1...100, то для всех и таблицы создавать
придется. Ну и объединяться со всеми соответственно тоже. 

Т.е. идея в том, что есть общие данные и есть дополнительные, которые существуют
только для определенных записей. И вот вопрос, как лучше организовать это все. Есть
ли еще варианты? И если нет, то какой из существующих самый приемлемый?
    

Ответы

Ответ 1

Сталкивался с такой же архитектурой. Суть была в следующем: есть рынок весов, весы
бывают разные(автомобильные, железнодорожные, медицинские). В зависимости от подкатегории
весов - разные свойства(так например для медицинских добавлялось поле "тип чаши").
Решение было следующее: все свойства(цена деления, предельный вес, тип чаши, типа калибровки
и тд) были вынесены в отдельную сущность типа справочника. Далее, от подкатегории(автомобильные,
медицинские) идет связь один-много к сущности "свойства подкатегории"(в ней 2 ключа
- ID подкатегории и ID свойства). В свою очередь, свойства подкатегории связана один-много
с сущностью "данные о товаре", в которой еще лежит форейн от товара и поле "значение"(значение
этого самого свойства, которое мы берем из справочника всех свойств)


Ответ 2

Возможно вам стоит посмотреть в сторону вертикального хранения аттрибутов.
Для каждого типа данных создать таблицу свойств.
Покажу пример для строк.

Упрощённый вариант:

StringProps(CarId, Type, Value)


Тогда запрос будет выглядеть примерно так:

SELECT C.Id, C.Type, 
  MAX(CASE WHEN S.Type='shop' THEN S.Value ENS)shop,
  MAX(CASE WHEN S.Type='address' THEN S.Value ENS)address,
  MAX(CASE WHEN S.Type='year' THEN S.Value ENS)year
FROM Car C
  LEFT JOIN StringProps S ON S.CarId = C.Id
GROUP BY C.Id, C.Type


Или если вам нужног конкретное свойство, то так:

SELECT C.Id, C.Type, S.Value shop
FROM Car C
  LEFT JOIN StringProps S ON S.CarId = C.Id AND S.Type = 'shop'


Есть чуть более сложный вариант, но более надёжный.
Создать две таблицы, свойств и типов свойств.

StringProps(CarId, TypeId, Value)
StringDesc(Id, CarType, Name)


Тогда запрос будет выглядеть так:

SELECT C.Id, C.Type, 
  MAX(CASE WHEN SD.Name='shop' THEN S.Value ENS)shop,
  MAX(CASE WHEN SD.Name='address' THEN S.Value ENS)address,
  MAX(CASE WHEN SD.Name='year' THEN S.Value ENS)year
FROM Car C
  LEFT JOIN StringProps S ON S.CarId = C.Id
  LEFT JOIN StringPDesc SD ON SD.CarType = C.Type AND SD.Id = S.TypeId
GROUP BY C.Id, C.Type

SELECT C.Id, C.Type, S.Value shop 
FROM Car C
  LEFT JOIN StringProps S ON S.CarId = C.Id
  LEFT JOIN StringPDesc SD ON SD.CarType = C.Type AND SD.Id = S.TypeId AND SD.Name='shop'


Смысл второго подхода в том, что мы не сможем запихать в свойства белиберду. А сможем
только те свойства, которые перечислены в StringDesc.Конечно, если создать необходимые
внешние ключи.

Подробнее можете почитать А. Тенцер БД - хранилище объектов.


Ответ 3

Если вам не нужно делать выборку по дополнительным полям, и сами данные потом обрабатываются
каким-либо приложением, вы можете собрать эти дополнительные данные в, например, JSON
объект и сохранить в поле meta таблицы Car. 

id, type, name, meta
1, CarBig, CarName, { attr1: value1, attr2: value2}


Поле meta может иметь как тип TEXT, так и тип JSON для MySQL 5.7.8 или выше. Во втором
случае вы можете производить выборки по полю meta.


Ответ 4

В одном запросе можно объединить сколько хочешь таблиц. Где-то уже давал такую подсказку.  

   select c.*, b.*, l.* --c.id, c.name, c.type, b.year, l.shop, l.address
     from Car c
     left join CarBig b on b.car_id = c.id
     left join CarLight l on l.car_id = c.id
    where c.id = 1


получив результат такого запроса смотрим c.type:  

если он равен big, то используем поля b.*: b.year часть l.* не нужна (и по-хорошему
там все поля == null)  

если c.type равен ligh то используем l.*: l.shop, l.address часть b.* игнорируется
(и тоже все поля из этой части == null).

Собирается всё что можно, и потом, исходя из полученного типа, используется только
нужное.  

Это хорошо работает при не очень большом количестве доп. таблиц и не планируется
добавление новых таблиц для новых характеристик.
Если список характеристик большой и будет ещё пополнятся, то обратите внимание на
другие ответы.