Using indexes to create multiple relationships to a table in order to enforce data integrity and add meaning

I'm working on an inventory database that uses supertype/subtype model. My question regards creating multiple relationships with the same tables using the primary key and composite indexes that also include the primary key. Example situations follow.

Situation 1
(To keep things simple, unnecessary fields have been removed from example figures)

I have the following tables: Device, DeviceType, and DeviceCategory.

DeviceCategory Table

CategoryCode | Name        | Description
----------------------------------------
WKS          | Workstation | Description of what classifies an item as a workstation...
LPT          | Laptop      | Description of what classifies an item as a laptop...

DeviceType Table

ID | CategoryCode | Manufacturer | Model  | IsTrackedInOtherSystemDefault
-------------------------------------------------------------------------
1  | WKS          | Dell         | GX1000 | true
2  | LPT          | HP           | dv4000 | false

Device Table

ID | SerialNumber | DeviceTypeID | IsTrackedInOtherSystem | CategoryDiscriminator
---------------------------------------------------------------------------------
1  | I81U812      | 1            | true                   | WKS
2  | N0S4A2       | 1            | false                  | WKS
3  | 3BL1NDMIC3   | 2            | false                  | LPT

If you notice, I have a relationship between DeviceType and Device using DeviceType's primary key and I also have an index on DeviceTypes's ID and CategoryCode, which I use to propagate the CategoryCode to the Device table to use as a discriminator for subtype tables. It also ensures that the CategoryCode and DeviceType ID combination actually exists in the DeviceType table.

You will also notice that the Computer subtype table has a relationship with Device's primary key, and another relationship with a composite index on Device's ID and CategoryDiscriminator field. This propagates the discriminator so I can use it in

Situation 1:

Your tables have one relationship and not two. (example: a Device belongs to a DeviceType)

So, keep only one relationship, the one with the composite keys (that include the Primary Key). The other relationship is redundant when the composite one is defined.

I would also suggest you have same names for related columns:

DeviceCategory Table
CategoryCode | Name        | Description
----------------------------------------
WKS          | Workstation | Description of what classifies an item as a workstation...
LPT          | Laptop      | Description of what classifies an item as a laptop...

DeviceType Table
DeviceTypeID | CategoryCode | Manufacturer | Model  | IsTrackedInOtherSystemDefault
-----------------------------------------------------------------------------------
1            | WKS          | Dell         | GX1000 | true
2            | LPT          | HP           | dv4000 | false
3            | WKS          | HP           | xx9000 | false

Device Table
DeviceID | SerialNumber | DeviceTypeID | IsTrackedInOtherSystem | CategoryCode 
------------------------------------------------------------------------------
1        | I81U812      | 1            | true                   | WKS
2        | N0S4A2       | 1            | false                  | WKS
3        | 3BL1NDMIC3   | 2            | false                  | LPT

So, the design would be:

DeviceCategory
--------------
CategoryCode   PK
Name           U1
Description

DeviceType 
----------
DeviceTypeID   PK   U1
CategoryCode   FK   U1
Manufacturer   U2
Model          U2
IsTrackedInOtherSystemDefault

Device 
------
DeviceID       PK   U1
SerialNumber   U2
DeviceTypeID   FK1
IsTrackedInOtherSystem
CategoryCode   FK1  U1

and for the Computer:

Computer
--------
DeviceID       PK   FK1
Hostname       U1
IPAddress      U2
CategoryCode   FK1  CHK

The "additional" UNIQUE keys (the two composite U1 ones) will be needed in most DBMS to enforce the foreign key constraints. I guess this answers your question 2, relationships needs indices to be enforced, so (you have to) use them. They will be used by the DBMS not only to enforce integrity but in your queries/statements, when you will be joining the tables.

The only one that is not needed is the U3 you had in the Computer table.

About question 3 (the over-engineering part): No, I don't think so but that's just my opinion. And you haven't told us if this is a homework/exercise or a real project, whether you will be holding only your family's or a multi-million company's inventory, etc.

Situation 2

I think what you have is fine and there is no need (and not a good idea) to have referential integrity constraints on these columns. This is a default value that is copied in the second table via a stored procedure (I guess during Inserts on the second table?) or altered by a user. If you add an FK, won't that deny users the ability to override the default?

The names of the two columns are self-explanatory enough for a DBA to understand the functionality.

StackExchange Database Administrators Q#21206, answer score: 4