From: Steve Crawford <scrawford@pinpointresearch.com>
To: Dana Hudes <dhudes@tcp-ip.info>
To: Christopher Browne <cbbrowne@acm.org>
Cc: pgsql-sql@postgresql.org
Subject: Re: User defined types -- Social Security number...
Date: Mon, 1 Mar 2004 09:42:48 -0800
Message-ID: <200403010942.48760.scrawford@pinpointresearch.com> (raw)
In-Reply-To: <Pine.LNX.4.58.0403011151340.17082@screamer.tcp-ip.info>
References: <c16fu8$2ro4$1@news.hub.org>
<m37jy5ryto.fsf@wolfe.cbbrowne.com>
<Pine.LNX.4.58.0403011151340.17082@screamer.tcp-ip.info>
On Monday 01 March 2004 8:54 am, Dana Hudes wrote:
> I would represent an SSN as numeric(9,0).
> an int 32 would work though.
> 2**31 is > 999999999
>
> On Sun, 29 Feb 2004, Christopher Browne wrote:
> > The world rejoiced as mdchaney@michaelchaney.com (Michael Chaney)
wrote:
> > > Look, you're thinking way too hard on this. An SSN is a
> > > 9-digit number, nothing more. There are some 9-digit numbers
> > > which aren't valid SSN's, and you might want to get fancy and
> > > create a constraint for that.
> > >
> > > Regardless, you are making a *major* mistake of confusing data
> > > storage with rendering. It is common to *render* an SSN as
> > > xxx-xx-xxxx and its cousin the FETID (Federal Employers Tax ID)
> > > as xx-xxxxxxx. To store the dashes makes no sense. They're in
> > > the same place each time, it's wasted data.
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).
While the dashes certainly don't need to be stored, they are not in
arbitrary positions as they delimit the parts of the SSN noted above.
One might even want to store the SSN in three columns, the first
linked to a table of valid area numbers, the second error checked so
"00" is not valid and so on or get even more fancy and error check
against: http://www.ssa.gov/employer/highgroup.txt. It all depends on
one's specific requirements.
Google and you will find SSN info pages such as:
http://proagency.tripod.com/usasssearch.htmlhttp://www.ssa.gov/foia/stateweb.html
Cheers,
Steve
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: scrawford@pinpointresearch.com, dhudes@tcp-ip.info, cbbrowne@acm.org
Subject: Re: User defined types -- Social Security number...
In-Reply-To: <200403010942.48760.scrawford@pinpointresearch.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox