Not that I really want to get into the entire discussion...but I will...a little.
On Friday having a discussion with a developer friend after I showed him my Party model. It's all about boozing it up. OK, wishful thinking on my part, it's just a database design based on the Party (or Entity) Model. I've pondered it here and here.
I'm not planning on debating the merits of the Party Model. I do like it though, it seems to me a natural end point. It does not take into account testing of the design, nor does it take into account places where you might denormalize...and many choose to denormalize by keeping addresses in line with people or organizations.
On to my point.
To maintain data integrity without using Check constraints, I'll create a small lookup table.
I posted my query on twitter Friday morning and received a pretty solid response from 4 people.
The response was swift:
Which was exactly what my conversation with my friend had led to.
In this case, why would Natural keys, VARCHAR2s, be bad.
1. If a user enters them in, they could misspell something, CELL would be CLEL or something. Fair enough.
2. If #1 happens, you just fix the name field instead of the key.
So I started to come around to it, until I got to STATES. States have a 2 letter abbreviation. Why not use that? It's not like they'll change (hah!) right?
Perhaps a distinction is in order then. If it's user entered content, then use a surrogate key. If not, use the supplied code.
I don't know. Is this just my personal preference? Is there a standard? What do you do?