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.

Answer 2

Somehow seems to me that inventory-items may change locations, so I would go with something like this.

Answer 3

As you say, there are many solutions. I would recommend starting with the simplest solution, then optimising later if performance or storage become problems. The simplest solution (but not optimal in terms of storage) would be to have a Storage table that has a column for storage type (indicating whether the row represents a van or a warehouse), plus columns for Van attributes as well as Warehouse attributes. In a row that represents a Van, the columns for the Warehouse attributes will all be null. In a row that represents a Warehouse, the columns for the Van attributes will all be null.

That way, you cut down on the number of tables, and keep your queries nice and simple. Be prepared to revisit your decision if storage becomes tight.