DMGDatabase Design
I've long said that when you have a single field in a database doing double duty, or when you have "magic" values that indicate special circumstances (usually requiring special behavior), you're asking for trouble.
The State of California evidently found this out the hard way.
According to this snopes article, a Los Angeles man back in 1979 wanted a personalized license plate. The form asked for three choices in order of preference, so he entered "BOATING" and "SALING" as his top choices. If those were taken, he didn't want a personalized plate, so he entered "NO PLATE" as the third choice.
Uh-oh.
It turns out that both "BOATING" and "SAILING" were taken, so the California DMV (predictably) assigned him the plate "NO PLATE".
The trouble really started when computer systems across the state started noticing that his plate, "NO PLATE", matched the "plate" entry on thousands of parking tickets. See, when an officer tickets a vehicle that doesn't have a license plate, he writes "NO PLATE" on the ticket, and that gets entered into the computer system.
So poor Mr. NO PLATE started getting all those parking tickets sent to him, and since those tickets had been floating around in the system for a while, a lot of them were already over due. Mr. NO PLATE had to go to all kinds of trouble to clear his name.
So, what can this teach us about software design?
(Assumptions up front: I'm assuming here that officers are writing on paper forms, and later transcribing into computer screens.)
One might say that the state's parking ticket database should have recognized "NO PLATE" as a special value, and not tried to match those tickets against actual plates. But what happens when officers use "MISSING" or "NONE" or just put a line through the field on their form? In order to have some special value in the LICENSE_PLATE field, you have to get all the cops in the state using the same special value or set of values, and you have to get all the data entry clerks to recognize it, and you have to make sure that the DMV doesn't issue any plates with that special value, since those plates would make the car immune to tickets.
So that's not a good option.
Here's how I'd do it: put a checkbox on the form used by the officers to indicate a missing plate. That way, they can say unambiguously "the car had no license plate" without resorting to special magical values. If that box is checked, then the PLATE field in the database stays null, and a corresponding MISSING_PLATE field is set to true.
And, since we don't want to lose any information, I'd keep the paper copy on file, and put a scanned image of the ticket (both sides) in the database.
(BTW, I'd still want the DMV to reject applications for smartass plates that might confuse stressed out cops looking for bad guys. Their job is tough enough.)
All of that is pretty specific to this problem. Let's talk about general principles that apply here.
Don't use "magic values" in your database fields. If there's no
value for the plate, then that field should be null,
indicating unambiguously that "THIS FIELD HAS NO VALUE".
Now, sometimes magic values are enescapable. In that case, cover yourself. Make sure that your code knows about the special values, and enforces their specialness.
For instance, if you were to walk into a restaurant, ask for a table for six, and say that your name is "Party of 4", the hostess is going to insist on getting your real name, because hearing "Party of four, party of six" is going to confuse everyone waiting for a table. She's enforcing the specialness of the phrase "Party of X" in this context.
The database structure doesn't have to match the input form. The database is not a copy of the input data. It is a semantic representation of the input data. So do whatever you want with the input form, so users have an easy time using the application. But make sure your database structure is solid, or you're going to have problems down the road.