Drew Wutka
DWUTKA at marlow.com
Thu Oct 16 01:57:07 CDT 2003
If it was going to be an international db, then you're right, a Zip code field wouldn't be a number. Drew -----Original Message----- From: Developer [mailto:Developer at ultradnt.com] Sent: Wednesday, October 15, 2003 2:57 PM To: 'Access Developers discussion and problem solving' Subject: RE: [AccessD] Number vs text data type While I not been following this thread completely, why would ZIP code ever be a number? Besides leading zeros, many non-US postal codes are mixed alpha-numeric ... Do you mean you'd add another field for that, rather than a "postal code" field to handle ANY country? -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Drew Wutka Sent: Wednesday, October 15, 2003 12:26 PM To: 'Access Developers discussion and problem solving' Subject: RE: [AccessD] Number vs text data type Ah, but you can always add fields to handle new anomalies! With the zip code, just add an 'extension' field. Original Number field is left intact (and all 'search' routines) would still work. Drew -----Original Message----- From: Heenan, Lambert [mailto:Lambert.Heenan at aig.com] Sent: Wednesday, October 15, 2003 8:38 AM To: 'Access Developers discussion and problem solving' Subject: RE: [AccessD] Number vs text data type This thread has been going on quite some time now, so why should I let it stop :-) One key issue that which has not yet been mentioned but should be considered is is there any possibility that the design / structure of the "numerical" identifier might change? To take the example of US Zip codes. Once upon a time there were no Zip codes, then along came 5 digit zips, and a little later the hyphen plus four digit extensions. This kind of change could happen at any point in time because at the end of the day a postal code is an *arbitrary* identifier. In other words there is no arithmetic meaning in a zip code, so it should not be stored as a number. You CAN store a 5 digit zip code as a Long Int, but you'll regret it if "the powers that be" decide to change things later on. As for searching for strings in a sorted list, while it is slower to compare a whole string with another whole string, vs. comparing numeric values, to search a sorted list really does not take that long as a standard binary chop approach is going to zero in very quickly on the target string. (If my mental arithmetic is up to it, searching for a string in a list of one billion (10^9) strings would require 30 comparisons at most.) In addition, the initial stages of the binary chop only need to compare the first character (or first few) in the string with the first character (or first few) of the target, not much slower than integer data comparisons. And it's just as simple to figure out if a target is within some range. Lambert > -----Original Message----- > From: Drew Wutka [SMTP:DWUTKA at marlow.com] > Sent: Tuesday, October 14, 2003 5:18 PM > To: 'Access Developers discussion and problem solving' > Subject: RE: [AccessD] Number vs text data type > > Yes, but if something can be stored JUST as a number, it is not only a > storage space issue, but a speed issue. If you took just the 5 digit > Zip code, (ignoring any extensions), and you wanted to search for > addresses in the range of 70000 to 80000, if the numbers are stored as > text, the processor is going to have to compare each digit of the > number, because it's looking at ASCII (or unicode) and NOT a number. > However, if it's stored as > a number, then it's a VERY simple check to determine if it falls in that > range! > > Drew > > -----Original Message----- > From: Charlotte Foust [mailto:cfoust at infostatsystems.com] > Sent: Monday, October 13, 2003 10:48 AM > To: Access Developers discussion and problem solving > Subject: RE: [AccessD] Number vs text data type > > > Same holds true for social security numbers in the US and for > telephone numbers everywhere. There are good reasons NOT to use > numbers for some kinds of numeric data, which is probably where the > argument comes from in the first place, that and the old approach of > squeezing everything into the smallest possible datatype to shave > storage bytes. > > Charlotte Foust > > -----Original Message----- > From: Susan Harkins [mailto:ssharkins at bellsouth.net] > Sent: Monday, October 13, 2003 7:32 AM > To: Access Developers discussion and problem solving > Subject: Re: [AccessD] Number vs text data type > > > What about Zip Codes, etc... what possible purpose would you have for > treating such an entry as a number? > > I'm afraid this isn't old school -- it's still very alive and with us. > > Susan H. > > > > That sounds like one of my co-workers who comes from the old school > > early-days C and COBOL programming. He makes everything text unless > > an actual calculation must be performed, even when that doesn't > > necessarily make sense in context. I believe that if its a number it > > should be treated as a number unless there is a very compelling > > reason not to, for all of the reasons given so far and probably > > others I haven't thought of. To me, logic tells you that if the data > > is numeric you should use a number data type, using text just > > muddies things up. As far as a number field needing "additional > > resources", especially for calculations, that makes no sense at all > > to me. Some number types may take additional storage space as > > compared to holding the same digit characters in a text type, and > > that can become an issue for DBAs when they are working > > under short drive space conditions. Other than, I can't > > think of a resource reason. > > > > Ron > > > > On Mon, 13 Oct 2003 10:42:15 -0400 > > CYNTHIA SPELL <CSPELL at jhuccp.org> wrote: > > >I will, that's a good idea. What I've heard so far is that logic > > >tells you that if you don't need to calculate, you should use text. > > >And that a number data type requires additional resources in order > > >for the field to have the ability to do the calculating. > > _______________________________________________ > > AccessD mailing list > > AccessD at databaseadvisors.com > > http://databaseadvisors.com/mailman/listinfo/accessd > > Website: http://www.databaseadvisors.com > > > > _______________________________________________ > AccessD mailing list > AccessD at databaseadvisors.com > http://databaseadvisors.com/mailman/listinfo/accessd > Website: http://www.databaseadvisors.com > _______________________________________________ > AccessD mailing list > AccessD at databaseadvisors.com > http://databaseadvisors.com/mailman/listinfo/accessd > Website: http://www.databaseadvisors.com > _______________________________________________ > AccessD mailing list > AccessD at databaseadvisors.com > http://databaseadvisors.com/mailman/listinfo/accessd > Website: http://www.databaseadvisors.com _______________________________________________ AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com _______________________________________________ AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com _______________________________________________ AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com