Re: User defined types -- Social Security number...

"Michael Chaney" <mdchaney@michaelchaney.com> wrote in message
news:20040301211843.GB19105@michaelchaney.com...
> On Mon, Mar 01, 2004 at 09:42:48AM -0800, Steve Crawford wrote:
> > I missed the start of this thread but will chime in with a comment
> > anyway.
> >
> > My rule is to select an appropriate numeric type of data if you will
> > be doing numeric types of things to it, character types if you will
> > be doing character manipulations, etc.
> >
> > I don't know of any good reasons to need to know SSN/6.9, sqrt(SSN),
> > SSN+7.86 but there are plenty of good reasons to need the first three
> > characters (the "area number"), the middle two characters (the "group
> > number", and the last 4 characters (the "serial number", often
> > (ab)used as a password for banking and other purposes).
>
> Another excellent point.  I often store zip codes as text for this
> reason.
>
> The only other thing that I would mention is that if the SSN field in
> the db will be a key of some sort, which is often the case, then it
> might be more efficient to store it as an integer.  It might be more
> efficient to store it as a character string.  The author should test
> in this case to determine the most efficient way.
>
> As for character vs. integer manipulations, in most scripting style
> languages, which is pretty much exlusively what I use, there's no
> need to think about types, and something like an SSN will silently
> change between being character or integer depending on what operations
> are being performed on it.
>
> Michael
> -- 
> Michael Darrin Chaney
> mdchaney@michaelchaney.com
> http://www.michaelchaney.com/
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: the planner will ignore your desire to choose an index scan if your
>       joining column's datatypes do not match
>

Ther are some other points I'd like to make --

If I store the SSN as an integer -- theoretically -- leading zeroes will be
stripped (041-99-9999) -- my OWN ssn is a perfect example of this as it
starts with a leading zero...

This would cause a problem in that one of the requirements of an SSN is that
the length be exactly 9 digits or 9 chars WITHOUT the dashes.... so a CHECK
CONSTRAINT would be useful...

But if the SSN is stored as an integer -- there is no check constraint that
wouldn't fail for SSNs that start with one or more zeroes.... So I thought
how about a varchar(9) field and insert/update triggers that do the
formatting (adding the dashes on insert/update --) and validate the check
contraints (9 chars + the dashes)...

The two extra characters making a varchar(11) field are not a concern in the
normalization or schema... I simply wanted a formatting function so that I
dont have to do it in my scripting language or use the same CAST over and
over and over in my select/insert/update statements....

I am mainly looking to do the formatting automatically rather than having to
constantly format such a simple piece of data...

It would be really sweet in postgreSQL if we could apply the equivalent of a
printf(columnname) to the table definition -- MS Access has what they call
an "input mask" and it comes in really handy -- however -- I havent used
Access for anthing serious for about 4 years...


-- 
Greg Patnude / The Digital Demention
2916 East Upper Hayden Lake Road
Hayden Lake, ID 83835
(208) 762-0762