Defensive Db Programming Chapter 07

Jason Brimhall, 2010-11-03

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.

Rate

Share

Share

Rate

Related content

Database Mirroring FAQ: Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup?

Question: Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup? This question was sent to me via email. My reply follows. Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup? Databases to be mirrored are currently running on 2005 SQL instances but will be upgraded to 2008 SQL in the near future.

Robert Davis

2009-02-23

1,567 reads

Networking – Part 4

You may want to read Part 1 , Part 2 , and Part 3 before continuing. This time around I’d like to talk about social networking. We’ll start with social networking. Facebook, MySpace, and Twitter are all good examples of using technology to let…

Andy Warren

2009-02-17

1,530 reads

Speaking at Community Events – More Thoughts

Last week I posted Speaking at Community Events – Time to Raise the Bar?, a first cut at talking about to what degree we should require experience for speakers at events like SQLSaturday as well as when it might be appropriate to add additional focus/limitations on the presentations that are accepted. I’ve got a few more thoughts on the topic this week, and I look forward to your comments.

Andy Warren

2009-02-13

360 reads