Chapter 19. Maintaining Database Integrity
Imagine that you're the owner of a small manufacturing company, and you've been working very hard to lure a certain customer away from a larger, better-established competitor. You finally convince the customer to give you a try, but you have to deliver the merchandise within 48 hours. You check with manufacturing and they can do it (just barely), so you break out the champagne.
Ten minutes later, there's trouble. It seems the accountant won't sign off on the order until the customer's credit has been approved, which will take a week, and without a signed order manufacturing can't proceed. What do you do? You'll probably start by explaining to the accountant that the situation requires bending the rules a little. If that doesn't work, you'll explain that requiring a credit check before completing an order is your rule, and because it's your company, you can break it as you see fit. If nothing else works, you'll sack the idiot and issue the paperwork yourself.
I have it on good authority that accountants are people and that they have families and mortgages like the rest of us, so the scenario I've just outlined isn't very likely. People don't just flatly refuse to carry out their boss' orders (or at least not very often). But computer systems do, regularly. They stamp their little electronic feet, stick out their little electronic lower lips, and refuse to comply with perfectly reasonable requests, all in the name of "maintaining data integrity."
After telling you earlier in the book about what data integrity is, how to model it, and where to implement it, I'm now going to tell you something really scary: Maintaining data integrity isn't very important.
I'll pause now and wait for the howls of protest to die down.
I'm not saying you should eliminate data validation. I'm saying that maintaining the integrity of the database is far less important than helping users do their jobs, and you should design your system accordingly. Data does need to be valid eventually, but it doesn't necessarily need to be completely valid at the moment it's entered.
Stop for a moment and think about why your database system is being developed: to help people perform some task. Data validation that helps people perform tasks is good; it supports the system's goals. Data validation that prevents people from performing tasks in the order that makes sense to them, or from doing something perfectly reasonable, but unexpected, is bad. It's as simple as that.
Your system should never prevent the user from entering data just because it isn't complete or you didn't anticipate it. Of course, like most things, doing this is sometimes easier said than done. In this chapter, we'll look at various types of integrity constraints and at how you can balance protecting the data from accidental errors with maintaining the reliability and usability of the system.