auto number vs byte

  • Thread starter Thread starter george
  • Start date Start date
G

george

Hi,

I have a database comprising 20 look up tables. Only a few
of them need not be updated (ie zip codes), the rest all
need updating (a list of nationalities where from time to
time a new nationality needs to be added). In the
beginning I designed the primary key to be number (byte)
instead of auto number in order to save on resourses and
speed up the application.

Now I'm working on a routine to automatically add new
items in the combo box lists (based on the above mentioned
lookup tables) as the user enters a new item. I have found
out that having chosen byte as primary keys makes thing a
lot more difficult in programming compared to auto number.

Should I change to auto number and make programming easier
with the additional cost on resources or should I develop
a more complex routine to handle addition of new items
having a byte as primary key?

thanks in advance, george
 
The AutoNumber in Access is a Long (32-bit) integer.

Because your operating system is 32-bit, it handles the Number (Long) all at
once. It is probably optimised for handling these faster than Integer, and
it certainly handles them faster than the unsigned Byte type. Your software
is therefore likely to run slower by using the Byte. It does use slightly
less disk space to store and retrieve a Byte than a Long, but the difference
is likely to be insignificant in terms of disk retrieval time or storage
costs.

Go with the Long Integer sized field as your default size.
 
George,

If a byte will cover your needs in a table, this means a max. of 256 records
per table. Now, in terms PK storage, this translates to 256 bytes per table
(max), or 20x256=5,120 bytes for all tables. Autonumber field size is 4
bytes, so the number would be 20x256x4=20,480 bytes.
So, sticking with the bytes you make your life dufficult, and save you a
humble 15KB on storage! As for impact on execution speed, I don't hink it's
possible to find a PC old enough to make the difference apparent, while
still able to run Access 97 or later.
I 'd say the choice is obvious.

HTH,
Nikos
 
Back
Top