[AccessD] Re: Number vs text data type

Hi Robert

With all respect I disagree. The rule should be reversed:

  If you are not going to use the number as a string,
  store it as a number.

The exceptions are only those where the numbers exceed the capacity of
your database engine, normally at least 14 digits (Jet: Currency) and
a Long Integer is always available which holds 9 digits.

Also, as several have stated, there may be some good reason for not
storing a number as a number but as a numeric string. The most common
situations are those where the place holder has a meaning. An example
is old accounting systems where longer account numbers indicate lower
levels relative to shorter account numbers:

       4        Main account
       48       Sub account of 4
       482      Sub account of 48
       8        Main account
       ...      etc.

One way to avoid strings in this case is to decide on a maximum length
for account numbers, say 6, and append traling zeroes:

       400000   Main account
       480000   Sub account of 4
       482000   Sub account of 48
       800000   Main account
       ...      etc.

Another example is something like product numbers where leading zeroes
are used but the length of the numbers varies. Such a combination
cannot be sorted by formatting with leading zeroes as you don't know
how many zeroes to add:

       00459208
       69392110
       0002918
       33981
       88470098
       0877281

However, in many countries postal codes are pure numerics and of fixed
length, so there's no reason to store a, say, four digit number as a
string when a two-byte Integer can do.

And here every company is identified for the authorities by a fixed
8-digit number like 12002696. Although it should be formatted as
12-00-26-96 I see no special reason for storing it as text.

/gustav


> Simple rule...

> If you are not going to do math with it, store it as text,
> except when using it as a dumb primary key/foreign key.

> All of the reason cited in the posts are correct.  But
> if you need a list of numbers stored as text sorted
> properly, just pad it with 0 or space on the left side.
> Everything will be sorted correctly.

> The reason the keys should stay numeric is the speed of
> the search for a record.  It is a few milliseconds faster.

> Robert

> At 12:00 PM 10/13/2003 -0500, you wrote:
>>Date: Mon, 13 Oct 2003 08:47:57 -0700
>>From: "Charlotte Foust" <cfoust at infostatsystems.com>
>>Subject: RE: [AccessD] Number vs text data type
>>To: "Access Developers discussion and problem solving"
>>         <accessd at databaseadvisors.com>
>>Message-ID:
>>         <E61FC1D4B1918244905B113C680BEA8631236E at infoserver01.infostat.local>
>>Content-Type: text/plain;       charset="us-ascii"
>>
>>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