SQL Database Design Issue

Question

At the moment I am solving a problem with database design, I will present a simplified scenario of what is happening:

Suppose I have four tables: Equipment, CurrentState, StateValue, StateType with the following schemes:

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 may have several different CurrentStates belonging to different StateTypes, hence a unique index (IX). StateType is basically a state machine, and StateValue contains values ​​for each state machine.

Now my question, as well as two questions, are about the StateTypeId foreign keys in the CurrentState and StateValue tables, which determine that StateType is a CurrentState record and which StateType is a StateValue record.

, ? , , CurrentState StateType , StateValue, CurrentState ( FK StateValue FK)...

: StateTypeId FK CurrentState, , , CurrentState EquipmentId, StateValueIds, StateValue StateType...

CurrentState, ? , . Entity Framework , ( ).

Answer 1

, , . StateTypeId CurrentState StateValueId. CurrentState StateValueId.

, StateTypeId CurrentState, check, , StateTypeId StateTypeId StateValueId. . , .

Answer 2

. , , , .

, CurrentState StateValue CurrentState StateType. ( , "FK".) ( ), -

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

CurrentState. , CurrentState StateType. StateValue StateType, . , , , StateValue.

, CurrentState .

CurrentStateIDX CurrentState (EquipmentId, StateTypeId).

, CurrentState.ID. CurrentState, EquipmentID StateTypeID. , ( ) . CurrentState.ID . , .

Answer 3

, : , StateValue, StateType ( , StateTypeId), , Id StateValue (StateTypeId, name) / StateValue. StateValueId CurrentState StateValueName, (StateTypeId, StateValueName) CurrentState StateValue. (EquipmentId, StateTypeId) CurrentState , . CurrentState Id, , CurrentState .

, : 1) , 2)

: " " (, " " ), . Google .

Answer 4

, FK StateTypeID .

, , " "? , , , . , "Building 1", "Building 2" "Building 3"; "30- ", "15- " ..; "Maintenance Required" "none", " ", "", ; . Equipment stateValueId, .

Much depends on what kind of requests you or want to make. Do you have any queries simplified by this table structure? Or are they all more complicated because you need to check the state type and state value?

If the only reason you did this was because creating more tables seemed sick, this is the wrong answer.

If you had fewer editing screens, I would suggest creating a general editing program that can take the name of the state type table as a parameter.