sql - How to create multiple one to one's

Question

I have a database set up with many tables and it all looks good apart from one bit...

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

The Storage table is used since the Van and Warehouse table are similar but how do I create a relationship between Storage and Warehouse/Van tables? It would make sense they need to be 1 to 1 as a Storage object can only be 1 Storage place and type. I did have the Van/Warehouse table link to the StorageId primary key and then add a constraint to make sure the Van and Warehouse tables dont have the same StorageId, but this seems like it could be done a better way.

I can see several ways of doing this but they all seem wrong, so any help would be good!

Answer 1

You are using the inheritance (also known in entity-relationship modeling as "subclass" or "category"). In general, there are 3 ways to represent it in the database:

1. "All classes in one table": Have just one table "covering" the parent and all child classes (i.e. with all parent and child columns), with a CHECK constraint to ensure the right subset of fields is non-NULL (i.e. two different children do not "mix").
2. "Concrete class per table": Have a different table for each child, but no parent table. This requires parent's relationships (in your case Inventory <- Storage) to be repeated in all children.
3. "Class per table": Having a parent table and a separate table for each child, which is what you are trying to do. This is cleanest, but can cost some performance (mostly when modifying data, not so much when querying because you can join directly from child and skip the parent).

I usually prefer the 3rd approach, but enforce both the presence and the exclusivity of a child at the application level. Enforcing both at the database level is a bit cumbersome, but can be done if the DBMS supports deferred constraints. For example:

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)
    )
)

This will enforce both the exclusivity (due to the CHECK) and the presence (due to the combination of CHECK and FK1/FK2) of the child.

Unfortunately, MS SQL Server does not support deferred constraints, but you may be able to "hide" the whole operation behind stored procedures and forbid clients from modifying the tables directly.

---

Just the exclusivity can be enforced without deferred constraints:

The STORAGE_TYPE is a type discriminator, usually an integer to save space (in the example above, 0 and 1 are "known" to your application and interpreted accordingly).

The VAN.STORAGE_TYPE and WAREHOUSE.STORAGE_TYPE can be computed (aka. "calculated") columns to save storage and avoid the need for the CHECKs.

--- EDIT ---

Computed columns would work under SQL Server like this:

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".

Unfortunately, SQL Server requires for a computed column which is used in a foreign key to be PERSISTED. Other databases may not have this limitation (e.g. Oracle's virtual columns), which can save some storage space.