Как создать несколько один к одному

Question

У меня есть база данных с множеством таблиц, и все выглядит хорошо, за исключением одного бита...

Inventory Table <*-----1> Storage Table <1-----1> Van Table
                              ^
                              1
                              |-------1> Warehouse Table

Таблица хранения используется, поскольку таблицы Van и Warehouse похожи, но как мне создать связь между таблицами Storage и Warehouse/Van? Было бы разумно, чтобы они были от 1 до 1, так как объект хранения может быть только 1 местом хранения и типом. У меня была ссылка на таблицу Van/Warehouse с первичным ключом StorageId, а затем я добавил ограничение, чтобы убедиться, что таблицы Van и Warehouse не имеют одинаковый StorageId, но, похоже, это можно сделать лучше.

Я вижу несколько способов сделать это, но все они кажутся неправильными, поэтому любая помощь будет хорошей!

Answer 1

Вы используете наследование (также известное в моделировании отношений сущностей как "подкласс" или "категория"). В общем, есть 3 способа представить его в базе данных:

1. "Все классы в одной таблице": иметь только одну таблицу, "покрывающую" родительский и все дочерние классы (т.е. со всеми родительскими и дочерними столбцами), с ограничением CHECK, чтобы гарантировать, что правое подмножество полей не равно NULL (то есть два разных дети не "смешивают").
2. "Конкретный класс на таблицу": есть разные таблицы для каждого дочернего элемента, но нет родительской таблицы. Это требует повторения родительских отношений (в вашем случае Inventory <- Storage) для всех детей.
3. "Класс на таблицу": наличие родительской таблицы и отдельной таблицы для каждого дочернего элемента, что вы и пытаетесь сделать. Это наиболее чисто, но может стоить некоторой производительности (в основном при изменении данных, но не так сильно при запросах, потому что вы можете присоединиться напрямую от дочернего элемента и пропустить родительский).

Я обычно предпочитаю третий подход, но применяю как присутствие, так и исключительность ребенка на уровне приложения. Выполнение обоих на уровне базы данных немного обременительно, но может быть сделано, если СУБД поддерживает отложенные ограничения. Например:

CHECK (
    (
        (VAN_ID IS NOT NULL AND VAN_ID = STORAGE_ID)
        AND WAREHOUSE_ID IS NULL
    )
    OR (
        VAN_ID IS NULL
        AND (WAREHOUSE_ID IS NOT NULL AND WAREHOUSE_ID = STORAGE_ID)
    )
)

Это обеспечит как исключительность (из-за CHECK) и наличие (из-за комбинации CHECK а также FK1/FK2) ребенка.

К сожалению, MS SQL Server не поддерживает отложенные ограничения, но вы можете "спрятать" всю операцию за хранимыми процедурами и запретить клиентам изменять таблицы напрямую.

---

Только исключительность может быть применена без отложенных ограничений:

STORAGE_TYPE это дискриминатор типа, обычно целое число для экономии места (в приведенном выше примере 0 и 1 "известны" вашему приложению и интерпретируются соответственно).

VAN.STORAGE_TYPE а также WAREHOUSE.STORAGE_TYPE могут быть вычислены (иначе говоря, "рассчитаны") столбцы, чтобы сохранить память и избежать необходимости CHECKs.

--- РЕДАКТИРОВАТЬ ---

Вычисляемые столбцы будут работать под SQL Server следующим образом:

CREATE TABLE STORAGE (
    STORAGE_ID int PRIMARY KEY,
    STORAGE_TYPE tinyint NOT NULL,
    UNIQUE (STORAGE_ID, STORAGE_TYPE)
);

CREATE TABLE VAN (
    STORAGE_ID int PRIMARY KEY,
    STORAGE_TYPE AS CAST(0 as tinyint) PERSISTED,
    FOREIGN KEY (STORAGE_ID, STORAGE_TYPE) REFERENCES STORAGE(STORAGE_ID, STORAGE_TYPE)
);

CREATE TABLE WAREHOUSE (
    STORAGE_ID int PRIMARY KEY,
    STORAGE_TYPE AS CAST(1 as tinyint) PERSISTED,
    FOREIGN KEY (STORAGE_ID, STORAGE_TYPE) REFERENCES STORAGE(STORAGE_ID, STORAGE_TYPE)
);

-- We can make a new van.
INSERT INTO STORAGE VALUES (100, 0);
INSERT INTO VAN VALUES (100);

-- But we cannot make it a warehouse too.
INSERT INTO WAREHOUSE VALUES (100);
-- Msg 547, Level 16, State 0, Line 24
-- The INSERT statement conflicted with the FOREIGN KEY constraint "FK__WAREHOUSE__695C9DA1". The conflict occurred in database "master", table "dbo.STORAGE".

К сожалению, SQL Server требует, чтобы вычисляемый столбец, который используется во внешнем ключе, был PERSISTED. Другие базы данных могут не иметь этого ограничения (например, виртуальные столбцы Oracle), что может сэкономить место для хранения.

Answer 2

Каким-то образом мне кажется, что предметы инвентаря могут поменять местами, поэтому я бы пошел с чем-то вроде этого.

Answer 3

Как вы говорите, есть много решений. Я бы порекомендовал начать с самого простого решения, а затем оптимизировать его, если проблемы с производительностью или памятью станут проблемой. Простейшее решение (но не оптимальное с точки зрения хранения) будет иметь таблицу хранения, в которой есть столбец для типа хранения (указывающий, представляет ли строка фургон или склад), а также столбцы для атрибутов фургона и атрибутов склада. В строке, которая представляет Van, столбцы для атрибутов Warehouse будут нулевыми. В строке, которая представляет Склад, столбцы для атрибутов Van будут нулевыми.

Таким образом, вы сокращаете количество таблиц и делаете ваши запросы красивыми и простыми. Будьте готовы пересмотреть свое решение, если хранение станет тесным.