Hello,

I'm struggling with the idea of using code tables to essentially implement business rules. In most cases the table serves as a lookup for specifically defined values, this is o.k.. In a more complex scenario multiple code tables are associated to create a subset of accepted code values combinations. This too is an easy lookup reference.

However, I'm into running into one situtation that seems to require a more complex relationship scheme. Essentially I have a need to "type"/"define a set of" two things. One is "contained" within the other.

For example: I need to provide a set of accepted lists with associated codes. Each list is made up of items. Each item also has an associated accepted codes. Additionally, there is a defined set of combinations of the two entities, "You can only have X type items in Y type list". To do this, I have created an association table of list_item_relationship.

Now here is my dilema. I've also created a table list_instance that references the list_code table to identify the list type of an item instance. Likewise, I've identified a table item_instance which is associated to a specific item_code. Having these things in place the final resolution seems simple. Create an associative table to link item_instance to list_instance to create item_in_list_instance.

However here is my problem. How do I ensure that the item_in_list instance is a valid combination according to the list_item_relationship table? If I reference the list_item_relationship table, I end up refering to the same list_code twice, once in the list_instance table and once in the list_item_relationship table. For the enforcement to work, I need both of these fields to match. There is no elegant way I can think of to enforce the relationship between the list_instance list_code with that of the list_item_relationship. From what I've read this problem seems to be described as a "UNIFIED" key. Where techincally I should migrate the same list_code value from both parent tables. I find this odd since I need for both values to be the same. However, if I migrate them to the PK they will allow for any combination. I suppose I could put in a constraint that says both fields must be equal, but this solution looks strange since I will have the field appear twice in the same table.

Another solution I can think of is using an alternate key strategy to force the equality between the tables, but I find this to be an awkward solution. I think I must be making some sort of modeling mistake.

Is there a more elegant accepted way to model this requirement?

Any help is greatly appreciated.

Mike