Re: SQL Humor

"--CELKO--" <jcelko212_at_earthlink.net> wrote in message news:1124465435.116434.124650_at_g14g2000cwa.googlegroups.com...
>>> just stick SSN in a varchar(20) as an extraneous attribute and use an 4
>>> byte integer identity for foreign key relationships. <<
>
> And sure enough, you will get a 20 letter SSN in your database. And it
> will be a duplicate on top of that.
>

I make good use of check constraints, especially on user entered VarChar columns. The reason I suggested VarChar(20) is to support in future changes the Fed may make to the SSN coding scheme.

> A good DB designer would know to use CHAR(9) and how to write at least
> a minimal constraint to insure some data integrity. A good DB designer
> would know how to verify SSNs via several commercial and puiblic
> services.
>

A good DBA would only be concerned with implementing a check constraint to confirm that the SSN conforms to formatting specifications. If and when the specication changes at some future date, the constaint can be modified as needed, and the extended length provides growing room without additional structural changes needed to existing data. It's the application developers perogative to make whatever remote procedure calls are needed to confirm the authenticity of the SSN number itself. Perhaps the developer would also want to cross reference the customer's name with the supplied phone number, or (if it's a flight scheduling application) even run their name against a terrorist watch list, but that degree of data validation is beyond the scope of database management.

> But a bad DB designer does not have to bother with all that research
> and work. He has his proprietary, magical, universal,
> one-size-fits-all-creation IDENTITY column.

The purpose of the identity column is to provide a non-changing single integer column for use in foreign key relationships.

>Let's let him tell the IRS that SSNs are an "extraneous attribute" while
>they haul himoff to jail and close us down.
>

The Social Security numbering and allocation system was invented by the Fed for the Fed's own accounting purposes. Over the years, it has been adopted by other organizations, because it is the closest thing we have to a unique identifier of US citizens. It provides a somewhat reliable method of looking up a master detail record for a specific customer, but is not reliable enough to be used as a foreign key. From the perspective of an eCommerce or magazine subscription system, it is an extraneous attribute; no more relevent at the application level than driver's license number or eye color. Even if you store the SSN in an integer, it still would be an inappropriate choice as a foreign key, because it is a confidential piece of customer information, and it would be difficult to scope to what degree it propogates throughout the enterprise. For compliance reasons, it is much better to assign your customers a sequentialy generated integer as the primary key and place a unique constraint on SSN. Received on Fri Aug 19 2005 - 18:32:53 CEST