How should one enforce/implement one-to-one-or-more relationships? For example, suppose I want enforce the rule that an Invoice carries one or more, i.e. not zero, Invoice Items. Most DB products seems to support only one-to-zero-or-more constraints natively.

    Requires Free Membership to View

    Login

The problem with this kind of relationship (1 to 1 or more) is due to a fundamental difference between business constraints and logical constraints. The business world can have objects like invoices where a valid invoice must have at least one detail line. Even in this case, an empty invoice still has to exist at least momentarily after creation except in a seriously contrived example. Relational databases are based on set theory, and the empty set is a subset of every real set. There are theoretical sets where this isn't the case, but no set can exist in the real world that doesn't have the empty set as one of its subsets.

From the standpoint of DRI (Declarative Referential Integrity), I don't know of any way to prevent an invoice without a detail line without denormalization of the tables.

My first question when presented with a case like this has to be since any logical design has to permit an invoice without detail lines in order to allow invoices to be created, how much work are you willing to do to make life difficult? An invoice without detail lines is essentially a Null invoice, so it shouldn't hurt anything.

In my opinion, the best way to deal with this is to have the client application manage the issue for you. If no detail lines are written in the invoice creation, just rollback the "new invoice" transaction. There are products such as ErWin that will "automagically" write triggers to support this kind of cardinality, but I see them as problematic instead of helpful due to the logical limitations that these triggers imply.

For More Information


This was first published in November 2002

Join the conversationComment

Share
Comments

    Results

    Contribute to the conversation

    All fields are required. Comments will appear at the bottom of the article.
    Back to top