Как избавиться от лишней таблицы в бд

182

02 октября 2018, 12:30

CREATE TABLE `products` (
  `id` MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `name_product` varchar(180),
  `creator_id` INT UNSIGNED NOT NULL, -- Внешний ключ
  `rating_product` MEDIUMINT UNSIGNED DEFAULT 0 NOT NULL, 
  `url_adress`  varchar(255) NOT NULL,
  `description` text NOT NULL,
  `access` TINYINT(1) NOT NULL, -- 0 = public; 1 = private.
  `complet ` TINYINT(1) DEFAULT 0 NOT NULL, -- 0 = не завершен; 1 = завершен.
  `display_library` TINYINT(1) NOT NULL, -- 0 = не виден в библиотекте, 1 - виден, 0 доступен для с режимом платной подписки
  `ganre_id` TINYINT UNSIGNED  NOT NULL, -- Внешний ключ. 
  `categories_id` TINYINT UNSIGNED NOT NULL, -- 
  PRIMARY KEY (`id`),
  FOREIGN KEY (`creator_id`) REFERENCES users(`id`),
  FOREIGN KEY (`ganre_id`) REFERENCES ganres_products(`id`),
  FOREIGN KEY (`categories_id`) REFERENCES categories_products(`id`)
  );

Суть: данная таблица хорошо подходит для описания моих сущностный, проблемы возникают только из-за 2 полей. Поле ganre_id может быть у одной сущности, при этом у этой же сущности не может быть значения поля categories_id, и наоборот, если есть значение у сущности categories_id его не может быть у ganre_id.

Я вижу следующие решения:

Использовать Default 1 в полях categories_id и ganre_id. В соответствующих таблицах создать занчение с id = 1, которые бы соответствовало значению отсутствия.
Создать отдельнуя таблицу products-2 для categories_id , а ganre_id оставить в текущей. Но это ведь не найс, из - за 2 полей 2 почти идентичные таблицы.

Товарищи, как быть? Может быть есть другие решения?

Answer 1

Чем определяется наличие или отсутствие у сущности того или иного атрибута? Можно ли на основании значений остальных полей таблицы однозначно определить, что у сущности есть, а чего нет?

Если да - решением может быть вульгарный набор триггеров BEFORE INSERT/UPDATE, зачищающий значение неподходящего атрибута. Типа

CREATE TRIGGER check_attr
BEFORE INSERT 
ON products
FOR EACH ROW
BEGIN
IF функция_проверяющая_необходимость_genre THEN
    SET NEW.genre_id := NULL;
ELSE
    SET NEW.categories_id := NULL;
ENDIF;
END;

Если нет - то триггер тоже поможет, но следует решить, какой из двух грохать, если в исходных данных есть оба.

Если запись с ошибочными атрибутами надо не корректировать, а отказать во вставке/корректировке - ну генерите соотв. SIGNAL в триггере, операция отменится по ошибке.

И в любом случае - решить, что делать, если у записи нет ни одного из атрибутов.

Answer 2

Объявить эти поля как NULL. NULL и использовать как нет значения. В частности, NULL является корректным отсутствием значения и при обработке внешнего ключа.

А вот check constraint mysql не умеет, поэтому правило что значение может иметь только или categories_id или ganre_id придётся аккуратно проверять на приложении или городить триггеры на before insert и before update.

PS: что такое ganre? Есть слово genre - жанр.

Answer 3

Я бы вынес такую связь в отдельную таблицу с полем enum для указания типа связи. В основной таблице оставил бы, например, поле атрибут, а в enum перечислил бы допустимые атрибуты. Так всегда можно расширить число полей которые подвержены уникальности подобно вашей в вопросе.

CREATE TABLE products (
  ..
  attr_id TINYINT UNSIGNED NOT NULL,
  FOREIGN KEY (attr_id) REFERENCES attributes(attr_id)
)
CREATE TABLE attributes (
  attr_id INT not null prim key,
  attr_type ENUM('genre', 'category'),
  attr_value CHAR(64) not null
)

Да, хранить значения атрибутов придётся в одной таблице. Будет солянка. Но они на то и в одной т.к. похожи по смыслу. Зато можно добавить новое значение в enum и сразу получить уникальность уже по трём полям, и более.