Results 1 to 6 of 6
  1. #1
    Join Date
    Jul 2013
    Posts
    7

    Enforcing rules on generalised data model.

    Hi everyone. I've looked through the forums for an answer to this question but I can't find much, so I thought I would ask. The data model in the link below is just a made up example, so some things might not strictly make much sense.

    http://i61.tinypic.com/hx851c.jpg

    I'll refer to the green section as the "generalised model" and to the pink section as the "specific model". They both represent the same concepts at a different level of generalisation.

    The following are some bare-bones tables for the generalised model, which is the focus of my question. Just the primary keys (and foreign keys for the junction tables) are shown:

    ContactType(ct_id)
    ContactMechanismType(cmt_id)
    Valid_ContactType_ContactMechanismType(ct_id, cmt_id)

    Contact(c_id)
    ContactMechanism(cm_id)
    Contact_ContactMechanism(c_id, cm_id)

    The entity types "Person" and "Organisation" in the specific model are subtypes of "Contact" and instances of "ContactType", which is basically a discriminator/type entity in ER terms (?). My rationale for taking this approach is that the requirements for the actual model I'm working on have been very volatile and they're bound to change in the future as well. So I think it makes more sense to change records in "ContactType", for example, rather than keep adding/removing tables to the database.

    Now, my issue: In the specific model, a Person can have only 1 Address and no POBox. In the generalised model, however, a Contact (which may be classified as a Person by its association with ContactType) may have many Addresses and many POBoxes, as the model stands.

    The information for which ContactMechanismType is valid for each ContactType is stored in Valid_ContactType_ContactMechanismType. So, it is explicitly stated in terms of data, but how can I enforce it on the association table Contact_ContactMechanism (Rule 1 in diagram)? These are the options I can think of:

    1. Use referential integrity constraints. The problem is that there are no foreign keys explicitly stated between the 2 tables. Contact_ContactMechanism(c_id, cm_id) would need to be turned to Contact_ContactMechanism(c_id, cm_id, ct_id, cmt_id) which would provide the foreign key (ct_id, cmt_id) to Valid_ContactType_ContactMechanismType(ct_id, cmt_id), but introduces redundant data (ct_id, cmt_id) in the Contact_ContactMechanism relation, along with a risk of ending up with inconsistent data.

    A way I've thought around this is to let the identity of Contact be defined by its type plus a unique id for they type, i.e. (c_id, ct_id) being the primary key for Contact. Same goes for ContactMechanism(cm_id, cmt_id). In this case the junction tables is Contact_ContactMechanism(c_id, cm_id, ct_id, cmt_id) which contains a foreign key to Valid_Contact_ContactMechanism that can be used to enforce Rule 1. The problem with this approach is the extra storage space, the more complex queries, unknown consequences in interacting with applications (e.g. composite key + ORM?), plus conceptually I've coupled the identity of a Contact with its classification. Now I can't reclassify a Contact (e.g. from Person to Organisation) without changing its identity, which feels fishy.

    2. Use a trigger or a stored procedure to check if I'm importing valid Contact - ContactMechanism tuples in the Contact_ContactMechanism table. But this does not feel satisfactory either and I'm pretty sure it's going to create performance issues in bulk insert situtations.

    3. Enforce the rule in the application. This options takes the rule completely out of the database and if another application uses the database it may corrupt it unless it implements the rule itself.

    So... these are my troubles. And only the easiest part because then there's the matter of enforcing the correct cardinality constraints between Contact and ContactMechanism, but this is a matter for another time. To all you experienced data modelers out there, is there a best-practice for handling these situations in the database? Any help will be greatly appreciated

    Thanks,
    Nico

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    One method of implementing complex integrity rules is through indexed views.
    Indexed views can have unique constraints.
    So, you could create a view of just the person/contact records, and put a unique constraint on the person ID. If two addresses are attempted to be entered for a single person, the constraint on the view will be violated and an error generated.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Jul 2013
    Posts
    7
    Thank you very much for your answer blindman. Views do seem like the most elegant solution for this purpose... I've gone through various blogs and articles these days and although I've stumbled upon many other approaches for enforcing business rules in the database, most of them left me with a hack-ish feeling. It seems people suggest different, ad-hoc mechanisms for enforcing different types of rules, including the use of:

    1. Triggers:
    Enforcing Business Rules Vs. Avoiding Triggers: Which Is Better? | Michael J. Swart
    Enforcing Business Rules with Triggers

    2. Constraints in a specially-designed data model (e.g. add a redundant/derived attribute to the relation):
    Avoid These Common Business Rule Implementation Mistakes*: Page 5
    https://www.simple-talk.com/sql/t-sq...-time-periods/


    3. UDFs (actually not recommended at all!):
    Alexander Kuznetsov : Scalar UDFs wrapped in CHECK constraints are very slow and may fail for multirow updates

    4. Views:
    Using Views to Enforce Business Rules - SQLTeam.com

    As I said, the views option strikes me as the most elegant, because it does not physically store redundant data, is "declarative" and does not involve cryptic code, etc. Nevertheless, my experience is quite limited so don't take my word for it. I don't even know if different DBMSs implement this mechanism...

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    I would not describe the views method as "the most elegant", but I would describe is as "the least hackish".
    I have implemented it on a few occasions.
    One drawback is the schema-binding required, which needs to be dropped and recreated if you make any changes to the underlying objects.
    The other drawback is that you have business rules that are "hidden", not entirely apparent just by looking at the table itself. So when I have implemented this I have named the view specifically to designate is as means of implementing a constraint.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  5. #5
    Join Date
    Jul 2013
    Posts
    7
    One interesting thing I noticed during my brief research on the topic and which could very well be due to my own inexperience, or simply because there is actually merit in the various viewpoints, is that I tended to agree with many different, conflicting views.

    In particular, some people told me "you have to enforce rules in the database"; others told me "no, that's what business logic is for; layer your application.". Other than to say I felt stupid in both occasions (simply can't win!), I'm mentioning this because obviously there's another way I didn't mention initially: don't enforce (complex) rules in the database at all.

    Some advantages I see there is that (1) you potentially off-load work to the application developer, (2) you might worry less about data migrations to data stores that, for example, don't support views, triggers or other such mechanisms (e.g. flat files) and (3) you might take advantage of some business rules engine. The drawback is you've introduced another layer and some applications might circumvent it. But really, why do that when the whole system has been designed with a second layer?

    I get a feel that there are 2 different tasks in *capturing* rules in the data model and then *enforcing* them in some way, either using database mechanisms or an additional layer. But the rules should at least be captured by the data model (through a meta-model?)... That's more of a thought, than a fact, so feel free to tell me how wrong I am. I'm starting to get used to it!

  6. #6
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    "Business Rules"
    Let's agree that is a pretty broad term, isn't it?
    Yet developers sling the phrase around as a catch-all.
    Some "business rules" define how users will interact with data.
    Other "business rules" define how data interacts with other data.
    Doesn't it make sense that rules applying to the behavior of data should be maintained close to the data, and rules applying to the behavior of users should be maintained close to the users?
    So..."data" rules should be enforced in the database, while "user" rules should be enforced in the interface.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

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