We are here again after another …um long lapse in time … and ready for another episode in this series.  Today we get to talk about chapter 6 in the book by Alex Kuznetsov (Blog).  You can find more on this series by looking here.

Advanced Use of Constraints

This chapter ties in well with chapters 4 and 6.  You may want to review those chapters as you read this chapter.  There were problems raised in those prior chapters and now they are being discussed at length in this chapter.  First order of business is to revisit the ticket tracking system that was discussed in chapter 4.

Ticket Tracking

The system was left with business rules implemented via triggers in chapter 4.  We are now going to explore using constraints in lieu of the triggers to enforce the business rules.  For sake of testing, the environment should be reset and a new environment built.  Alex provides all of the scripts to do so.  As Alex progresses through this example he lays out the assumptions and some of the problems with those assumptions.  Through a combination of Indexes and constraints.  Another item that was implemented was the use of ON UPDATE CASCADE to help ensure the business requirements could be met.  Alex talks about the implications on performance and the reasons for implementing the cascade update.  Make sure you read this chapter to find out more.

Inventory System

There is a substantial amount of discussion about a new example in this chapter.  That discussion revolves around the Inventory System and several use cases.  I recommend reading the chapter.  I am not going to discuss it beyond that here in this post.  There are a great collection of scripts to run through the example.

Conclusion

In this chapter Alex shows us how a combination of constraints can enforce complex business rules.  The use of constraints should be included as a viable option when trying to enforce business rules.

Once again, go get this book and read it.  It is well worth the read.