The Price Tag and the Alternatives
Alternatively, you can use either triggers or stored procedures to implement this business rule. To decide which solution works best for you, consider the advantages of the constraints solution:
- Constraints provide a waterproof solution. Once they are in place, they guarantee that all your data is clean.
- You implement your business rule only once, in only one place.
- Typically, constraints work faster than queries in your stored procedures and triggers.
To be fair to yourself, consider the disadvantages of this solution as well:
- You have to add an additional column.
- You have to add two additional indexes.
- You increase lock contention on your child table.
Clearly, if your business absolutely does not tolerate mismatches between statuses of tickets and programmers, the waterproof solution described in this article is for you. However, if your business will not suffer too terribly from just a few mismatches, then the price you have to pay for the constraints solution may be too steep.
Applying This Solution to Other Situations
Suppose your client is a doctor's office whose hours are from 8 AM to 8 PM, and you want to prevent its scheduling system from saving 7:30-to-8:15 AM appointments in the database. You can use the same approach to have your database guarantee that appointments completely fit into the office hours. You also can use the same approach to implement process flow business rules.