Results 1 to 1 of 1
  1. #1
    Join Date
    Jan 2004

    Question Modeling Business rules with code tables?


    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.

    Last edited by PanamaMike; 09-10-04 at 13:40. Reason: Update wording

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts