Ask the Expert

One-to-one-or-more relationships

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

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

There are Comments. Add yours.

TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to: