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!
To implement a one-to-many relationship in the Teachers and Courses table, break the tables into two and link them using a foreign key. We have developed a relationship between the Teachers and the Courses table using a foreign key.
Set the foreign key as a primary key, and then set the relationship on both primary key fields. That's it! You should see a key sign on both ends of the relationship line. This represents a one to one.
One-to-one relationships associate one record in one table with a single record in the other table. One-to-many relationships associate one record in one table with many records in the other table.
One-to-one (1:1)A relationship is one-to-one if and only if one record from table A is related to a maximum of one record in table B. To establish a one-to-one relationship, the primary key of table B (with no orphan record) must be the secondary key of table A (with orphan records).
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:
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 CHECK
s.
--- 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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With