integer versus text data types

  • Thread starter Thread starter dalin
  • Start date Start date
D

dalin

good morning everyone..

after a quick, and unfortunately unsuccessful search, i was unable t
find the answer to what must be a very simple question...

heres my situation... i am working with a database that will stor
primarily id numbers for various form field (cityID, stateID, streetID
etc.)...

i -thought- i had read somewhere that it was more efficient to stor
numbers as 'text' fields if they were not going to be used fo
computational means...

so heres the question... whats the most efficient way to stor
relatively small numbers (0 to 4 digits)...

as 'integer' or 'text'...

and on a related note, if my values have leading zeros, should i stor
them as a truncated number(126 vs. original value of 0126), an
concatenate the 0's back on when displaying the values?

thanks for any insight...



-
dali
 
Are cityID and stateID actual codes or are they just used for linking? If
they are just used for linking, they should be autonumber in the lookup
tables and long integers in the main table. If they are actual meaningful
codes (ie 0123) then you should store them as text. Especially if they have
to retain their leading zeros. By all means, if they have a leading zero,
store it.
 
good morning everyone..

after a quick, and unfortunately unsuccessful search, i was unable to
find the answer to what must be a very simple question...

heres my situation... i am working with a database that will store
primarily id numbers for various form field (cityID, stateID, streetID,
etc.)...
Click to expand...

If you're letting Access assign these ID's, then they will be
Autonumbers - a special type of Long Integer; the foreign keys in
other tables should be Long Integer to match.
i -thought- i had read somewhere that it was more efficient to store
numbers as 'text' fields if they were not going to be used for
computational means...
Click to expand...

That's true if these will be human-readable values; but autonumbers
are numbers.
so heres the question... whats the most efficient way to store
relatively small numbers (0 to 4 digits)...

as 'integer' or 'text'...
Click to expand...

A tossup. A Long Integer is 4 bytes (Integer is 2), but the
performance difference between a 2-byte Integer and a 4-byte Text is
going to be trivial (the number will be a hair faster just because
it's smaller).
and on a related note, if my values have leading zeros, should i store
them as a truncated number(126 vs. original value of 0126), and
concatenate the 0's back on when displaying the values?
Click to expand...

If you want leading zeros, use text.
 
Back
Top