CODEWITHSUNDEEP
sql-serverdatabasedatabase-design
HaxElit
HaxElit

Reputation: 4073

Database Model Dependent Relationship

A picture does more justice so I'll start with that. Dependent Relation

So in my Relation_Type table I have several different Types (Owner, Reviewer, Approver, etc).
In my Relation_Status table I have different status' for some of the types:

Reviwer: (Pending Feedback, Feedback Received)
Approver: (Pending Decision, Approved, Denied)

My problem is that I don't know how to enforce the relationship that says if the relation type is feedback limit the status to only the feedback status'.
Right now the way this is modeled a relation type of Feedback can have any status which is a logical inconsistency. Also, not all Types have a Status.

So any tips on how to model this so it enforces the dependency ?

Thanks, Raul

Upvotes: 2

Views: 1085

Answers (3)

bobs
bobs

Reputation: 22204

I would remove the Project_Resource_Relation_Type_Id column from the Project_Resource_Relation table. This removes the relationship from Project_Resource_Relation to Project_Resource_Relation_Type.

The relation-type is already related to the Project_Resource_Relation table through the Project_Resource_Relation_Status table. The Project_Resource_Relation_Status table already enforces the relationship between types and statuses.

Upvotes: 0

Jeff Mattfield
Jeff Mattfield

Reputation:

Perhaps you need a compound foreign key that combines Status_Id with Type_Id.

Upvotes: 0

joelt
joelt

Reputation: 2680

You could create another table TypeStatus(ID, Type_Id, Status_Id). It would have FK's to the _Type and _Status table, and the _Relation table would have a single FK to this new table, rather than two FK's to the existing tables. You'd then also remove the _Type_Id column from the _Status table, I would think.

Upvotes: 1

Related Questions