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

Question

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

Figure 1 - Entity Relationship Diagram

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 CHECK constraints to enforce that only certain categories of devices are allowed in the table. Again, it also ensures that the CategoryDiscriminator and ID combination actually exists in the Device table.

I have the following questions about this setup:

1. Should I have two relationships with the same two tables, one with the primary key and one with an index that contains the primary key, or should I just make the relationship with the composite index because it already contains the primary key?
2. Am I using indexes incorrectly by creating them for the sole purpose of making relationships that enforce data integrity?
3. Am I over-engineering the database?

Situation 2

In the first example ERD, the field IsTrackedInOtherSystemDefault in the DeviceType table is a boolean value that states whether that device type is tracked in another specialized system that we use. Some devices may are not tracked in the other system for various reasons, so I wanted to provide the ability for users to override this field when they enter a device. The IsTrackedInOtherSystem field in the Device table is copied from the DeviceType table if no other value is provided. Copying the default value from DeviceType will happen in a stored procedure.

I didn't know if having this functionality buried in a procedure is a bad idea because other DBAs won't directly see where the field is getting its default value from. I thought I could enforce it and make it more visible by using a relationship, which would require another index. See the following ERD.

Figure 2 - Entity Relationship Diagram

In this setup the composite index of DeviceType's primary key and the IsTrackedInOtherSystemDefault is used to enforce where the value comes from and that it must be a valid combination of the two fields. The OverrideTrackedDefault field in the Device table is another boolean value that essentially says to negate whatever the default field is. I feel this setup gives it more meaning by making these rules apparent in the table starting at the table's creation rather than relying on a line of code buried somewhere in a stored procedure.

Update: Really what I'm asking here is: if a field in a table gets a value from a field in another table, doesn't this warrant a relationship? It seems unnatural to just copy the value in a stored procedure where it is not in plain sight.

I have the following questions about this setup:

4. Same question as Situation 1: Question 1.
5. Same question as Situation 1: Question 2.
6. Same question as Situation 1: Question 3.
7. While the situation may be easier, and performed with less code in a stored procedure, is the overhead of adding fields, relationships and indexes something that is recommended if it makes the rules of the system more apparent?

• A-K: In your Situation 2, I would not copy the default value to child table at all. Instead, I would create a view with the following column: COALESCE(OverrideTrackedDefault, IsTrackedInOtherSystemDefault) AS IsTrackedInOtherSystem That should be enough to clearly document your intent without copying values from one table to another.

Answer 1

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.

• TheSecretSquad: Thank you for your excellent answer. I made changes using your recommendations, including changing the field names to be consistent. In regard to Situation 2: If you look in the Example2.Device table the relationship is with the fields DeviceTypeID and IsTrackedInOtherSystemDefault. There is an additional field OverrideTrackedDefault that is then set to true or false to override the default value. I thought this would make the concept more visible, and still let users override with the additional field. You think managing this with one field and a stored proc is okay? Please see update in post