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 [EMAIL PROTECTED] (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. > ---------------------------(end of broadcast)--------------------------- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
