SQL Database design question

Question

I am grappling with a database design issue at the moment, I will present a simplified scenario of what is going on:

Lets say I have four tables, Equipment, CurrentState, StateValue, StateType with the following schemas:

Equipment
------------
Id (PK),
Name


CurrentState
------------
Id (PK),
EquipmentId (FK) (IX),
StateValueId (FK),
StateTypeId (FK) (IX)


StateValue
------------
Id (PK),
StateTypeId (FK),
Name


StateType
-----------
Id (PK),
Name

A piece of equipment can have several different CurrentStates belonging to different StateTypes, hence the Unique Index (IX). A StateType is basically a state machine and StateValue contains the values for each state machine.

Now, my question, well 2 questions, is around the StateTypeId foreign keys in the CurrentState and StateValue tables that determine what StateType a CurrentState record is and what StateType a StateValue record is.

First of all, is having this type of relationship bad design in terms of referential integrity? My guess is that it IS bad design as there should only be one link between the CurrentState table and the StateType table, and this is via the StateValue, else a CurrentState record could end up having two different StateTypes (one via the direct FK and the other via the StateValue tables FK)...

But the second question comes: If I should not have a StateTypeId FK in the CurrentState table, how can I enforce the Index, that is ensure that there is not two CurrentState records for a single EquipmentId that have StateValueIds pointing to StateValue records of the same StateType...

Do I have to use a trigger when inserting into the CurrentState table to check that the rules are followed? I have never used triggers before, so I will need to do a bit of research. I am also using Entity Framework and have no idea what the implications are with this (there shouldn't be any in theory).

Answer 1

Yes, this is bad practice, since you can have two different State Types for one current value. Drop the StateTypeId column out of CurrentState, and have it only go through StateValueId. Your unique index should be on CurrentState and StateValueId.

Of course, if you insisted on having StateTypeId in CurrentState, you could set up a check constraint that would make sure that StateTypeId was equal to the StateTypeId from StateValueId. But this is really roundabout. It's much cleaner and more maintainable to leave them all in their own silos.

Answer 2

The problem with your design is not due to the column design in your tables. You can keep the same exact columns you presented in your proposed solution, and get better results, from both a performance and a flexibility perspective.

What you need to do is to set up a compound referential constraint between CurrentState and StateValue, and drop the referential constraint between CurrentState and StateType. (I don't see that constraint in your problem statement, but I presume it from your use of "FK".) I've forgotten the syntax for a compound referential constraint (it might be DBMS specific), but it's something like

CONSTRAINT ValidState FOREIGNKEY (StateValueId, StateTypeId) REFERENCES StateValue (Id, StateTypeId)

This would be in the definiton of CurrentState. Don't worry about the fact that CurrentState doesn't reference StateType. Because StateValue references StateType your constraints will work transitively together. The compound contraint will ensure that you don't store a relationship between State Values and State types in Current state that contradicts the same relationship stored in StateValue.

Now you can create a single unique index that ties two of the three foreign keys in CurrentState together.

CREATE UNIQUE INDEX CurrentStateIDX on CurrentState (EquipmentId, StateTypeId).

If I were doing the design, I would drop the column CurrentState.ID completely. I would then define a compound primary key for CurrentState that lists EquipmentID and StateTypeID. This would cause the DBMS (in most cases) to organize the table optimally for joins, and create the appropriate unique index. CurrentState.ID is going to be rarely used anyway. But if you really like to have an ID column on every table, suit yourself.

Answer 3

Building on Walter Mitty's answer I'd go another step ahead: assuming that two StateValue belonging to the same StateType (i.e., having the same StateTypeId) cannot have the same name, I'd drop the Id column in StateValue and take (StateTypeId, name) as compound/concatenated primary key for StateValue. StateValueId in the table CurrentState is then replaced by StateValueName, and the pair (StateTypeId, StateValueName) in CurrentState references the compound primary key of StateValue. A uniqueness constraint on the pair (EquipmentId, StateTypeId) in CurrentState has to be added as described by Walter. If this pair is also used as primary key for CurrentState instead of Id, depends on the fact if other tables are referencing the CurrentState table or not.

So the answers are: 1) Yes, 2) Not necessarily

General tip: "Beginning Database Design" by Clare Churcher (and building upon it "Applied Mathematics for Database Professionals") is an excellent read. You can take also a look at it at Google Books.

Answer 4

Ditto that the FK for StateTypeID accomplishes nothing and creates the possibility of inconsistent data.

I understand you're trying to simplify the problem for discussion, but what are these different "state types"? If they have nothing to do with each other, maybe cramming them all into one table is a bad idea. Like, if you have a type of Location with values of "Building 1", "Building 2", and "Building 3"; and a type of Depreciation Method with values of "30-year straight-line", "15-year declining balance", etc; and a type of "Maintenance Required" with values of "none", "complete overhaul", "tuning", whatever; then cramming all these unrelated things into one table is just wrong. If you create a separate table for each state type, and a separate column in the Equipment table for each stateValueId, then the whole issue goes away.

A lot depends on what queries you do or are likely to do. Do you have any queries that are made simpler by this table structure? Or are they all made more complicated because you have to test both the state type and the state value?

If the only reason you did this was because creating more tables seemed like a pain, that's the wrong answer.

If you did it to have fewer edit screens, I'd suggest creating a generic edit program that can take the state type table name as a parameter.