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

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(at)michaelchaney(dot)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.
> >
> > Store the SSN as an "integer". When you begin to think about this
> > correctly, the "leading zeros" problem disappears since that is also a
> > *rendering* issue.
>
> Well put.
>
> The one thing that is a bit unfortunate is that 32 bit ints aren't
> quite big enough for this. You need 1 extra digit :-(.
>
> > When you pull the data out, either fix it up in your programming
> > language to the format that you wish, or use the to_char function as
> > shown above in your select statements.
>
> Using a view to hide the "physical" representation is also an idea.
>
> A full scale type definition could make for an even more efficient
> approach that makes the implementation appear invisible.
>
> > To help you think about this whole issue, consider the timestamp
> > datatype. Timestamps are stored as a Julian date internally. I
> > suspect that they use a double-floating point as the actual format,
> > but regardless the point is that it's a number. Rather than storing
>
> Actually, it's an "int64"; a 64 bit integer, on platforms that support
> that type. It's a "double" only on platforms that do not support that
> type.
>
> > It's easier to use that as a basic format from which we can render
> > it in any way we wish.
>
> Indeed.
>