Database Model Dependent Relationship

views:

answers:

+2 Q:

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

+1 A:

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.

joelt 2010-08-31 14:24:18

While a new table to store allowable combinations of type and status would be a good idea, it might not be such a good idea to replace the existing two FKs on the _Relation table with a single FK to the new table - if an existing type-status combination ceases to be valid, the record on the new table would need to be deleted, but this would leave orphan records on the _Relation table.

Mark Bannister 2010-08-31 17:54:13

I think that'll be a problem whether you have one FK or two... in both cases, if you remove a valid type-status combination, there may be records in the _Relation table that need to be either updated or deleted to match the functional constraint.

joelt 2010-08-31 18:52:44

I think this solution only works if there is a many-to-many relationship between types and statuses. While it's not explicitly stated in the question, the diagram implies that there is a one-to-many relationship between statuses and types. So, if there is a one-to-many relationship, you would have to add additional constraints in this solution. If the relationship is many-to-many, you get my vote for the best answer. It's the right solution for a many-to-many relationship.

bobs 2010-08-31 23:20:21

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

2010-08-31 14:28:07

This did the trick. I had to add a Unique constraint on the Status table with the _id and type_id columns so I could create the compound foreign key. The reason I selected this answer is because the rest either tied directly to the status, or had an extra table that had the logical groupings. Those options don't work because not all types have a status.Thanks

HaxElit 2010-09-01 13:53:18

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.

bobs 2010-08-31 23:14:24

This doesn't work because not all types have a status. So if I don't have a status I can't relate back to the type.

HaxElit 2010-09-01 13:54:43

ansaurus

tags:

views:

answers:

Database Model Dependent Relationship

related questions