Constraints & Good Design
The best of artists hath no thought to show, which the rough stone in its superfluous shell, doth not include; to break the marble spell, is all the hand that serves the brain can do. —Michelangelo
A well-designed application does not include artificial constructs to model business processes; it simply models what is there.
If we use solid development practices, this tends to happen naturally. If, instead, we immediately adopt crutches in our design process, we begin to heavily rely on artificial constructs and our users may even become convinced that what we have given them is "the way it is".
Consider the case of a project management web application. A given user with a couple of projects going in the application calls for support. What information does the support person ask for in order to identify the customer? Let's imagine two scenarios. The first is an application where "the DBA" uses auto-incrementing integral surrogate primary keys for everything. The second is a scenario where natural keys are used as much as possible.
Both Scenarios
Tech Support: Hello, thanks for calling XYZ Support, can I help you?
Customer: Hi there, yeah, I am having problems accessing one of my projects.
Tech Support: I can help with that. I just need to get some information first.
Scenario #1: Surrogate Keys
Tech Support: What's your customer ID?
Customer: Eh, wha-? I don't know, where do I get that?
Tech Support: Don't worry, I can look it up for you. What's your name?
Scenario #2: Natural Keys
Tech Support: May I have your name please?
Both
Customer: John Public
Tech Support: Thank you Mr. Public, could you confirm your billing address?
Customer: 100 W Main St., Smithville, Oregon
Tech Support: Thank you sir. For security purposes, please confirm the last four digits of your social security number.
Customer: 9999
Tech Support: Thank you Mr. Public. Now, which project are you having trouble accessing?
Customer: My "foo for bar" project. I can't even get into it.
Scenario #1: Surrogate Key
Tech Support: Mr. Public, I see you have several "foo for bar" projects here.
Customer: yeah, I was going to try and start over, I guess, but I don't want to do all that work again. Can you help me get into my old one?
Tech Support: Yes, sir, what is the project ID?
Customer: I'm sorry, the project ID? It's "foo for bar".
Tech Support: No, sir, I mean what is the numeric ID of the project?
Customer: I-uh-where do I get that?
Tech Support: It's okay sir, let me see if I can figure out which one you want.
Scenario #2: Natural Keys
Tech Support: Mr. Public, I see you have a few "foo for bar" projects here. There's "foo for bar", "foo for bar 2", and "foo for bar 3".
Customer: Yeah, I was going to start over, I guess. I want my original one, just "foo for bar".
Tech Support: Okay, sir, I can help you with that.
What happened here? In scenario #1, we lazily just used numeric Ids all over the place. Our tech support staff thinks in those terms now, and our customer feels less than valued and perhaps a little stupid. In scenario #2, we used the natural key of customer identfier + project title as the primary key and our application reflects that. Customers cannot create duplicate projects with the same name; tech support staff cannot just reduce people and their work to numbers; and the application accurately models the real world processes it is designed to support without artificially introducing ids.
Make your software support your users, not vice versa.
