Or You Could Use a Domain

Nice little blog post about [http://www.xerratus.com/2005/12/21/WhySSNShouldNOTBeStoredAsAnIntegerInADatabase.aspx why storing Social Security Numbers (SSN) as integers is a bad idea]. The best part is the guy who comments that the SSN is really an integer, and that preceeding zero’s are simply a “display issue”, and that the “validator” function isn’t doing it’s job. OK, here’s my validator function:

 rms=# CREATE DOMAIN ssn AS text CHECK (VALUE like '_-_-___') ;

CREATE DOMAIN

Now let’s see about inserting crappy data:

rms=# CREATE TABLE peeps (name text, social ssn);

CREATE TABLE

First the “validator strips preceeding 0’s cause it thinks it’s an int” problem, which my friend Amber would suffer from :

rms=# INSERT INTO peeps VALUES ('hambre','50422112');

ERROR:  value for domain ssn violates check constraint "ssn_check"

No problem there. Ok, what about my pal Devrim, he doesn’t have a SSN:

rms=# INSERT INTO peeps VALUES ('Devrim','');

ERROR:  value for domain ssn violates check constraint "ssn_check"

Oh look, more busted app code… I said he didn’t have a SSN, not that he had a blank SSN [[image /xzilla/templates/default/img/emoticons/smile.png alt=”:-)” style=”display: inline; vertical-align: bottom;” class=”emoticon” /]]

rms=# INSERT INTO peeps VALUES ('Devrim',NULL);

INSERT 0 1

Ok, that worked. Lastly, let’s look at my pal David, who does have a SSN:

rms=# INSERT INTO peeps VALUES ('fett-dawg','415422112');

ERROR:  value for domain ssn violates check constraint "ssn_check"

See, my domain validator is quite draconian… I’m gonna require the “-” in the number. On the upside, you won’t have to worry about anyone displaying the number incorrectly.

rms=# INSERT INTO peeps VALUES ('fett-dawg','415-42-2112');

INSERT 0 1

Even if you don’t like **my** definition of what a SSN domain should look like, the point here is that if you use a domain, there will be no question what a SSN will be defined at within your application; no matter if your application developers disagree, get replaced, or work on different interfaces to the data and never even speak to each other; in every case your safe from getting munged data. Aaaah! I love the smell of data integrity in the morning! One last note… Magnus thinks my example is not complicated enough, but hopefully it still get’s the point across.