"Michael Chaney" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] > 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 > [EMAIL PROTECTED] > 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 ---------------------------(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