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.
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
- Dozens more answers to tough database design questions from Pat Phelan
- The Best Database Design Web Links: tips, tutorials, scripts, and more
- Have an Oracle or SQL tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
- Ask your database design questions -- or help out your peers by answering them -- in our live discussion forums.
- Ask the Experts yourself: Our SQL, database design, Oracle, SQL Server, DB2, metadata, object-oriented and data warehousing gurus are waiting to answer your toughest questions.
Related Q&A from Pat Phelan, Data Modeler
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.