why use autonumber

[Pete Morris]

Is it important to use autonumber when creating tables?
I have seen some examples of tables when looking at other options like cascade combo boxes etc and they always seem to contain autonumer as the key.
For instance,
if you have two tables, one for countries and one for continents, why include an autonumber, as continents or countries are unique, what is the benefit??
Am I missing something fundamental here???

 

[Fuga]

if you have two tables, one for countries and one for continents, why include an autonumber, as continents or countries are unique, what is the benefit??

In this structure, how do you plan to know which countries belong to which continent? You will need a field that links the tables.

Autonumber is good because it generates a unique value. You would then use autonumber for the continents table and number for the countries table, as there can be many countries in every continent.

Fuga.

 

[Pete Morris]

another example;
I have a table with manufacturer of equipment and then model of equipment.
Is it best to have a separate table with each and an autonumber??

Tbl1
Hover
Mower
Flymo
Grasscutt

Tbl2
nice line
so smooth
quitetrim
lower

Or should I keep manufact & model in one table with an entry for each new item??

Tbl1
Hover nice line
Mower So smooth

etc etc....

 

[Mile-O]

Is it best to have a separate table with each and an autonumber??

Yes. Afterall, a manufacturer and a model are two different entities.

tblManufacturers
ManufacturerID
Manufaturer

tblModels
ModelID
Model
ManufacturerID

 

[Pete Morris]

OK just to play devils advocate, what's the difference between having the manufacturer id rather than the manufacturer name??
Isn't it basically the same thing???? and doesn't it cut down on the need for tables.

I'm not trying to be facetious, but trying to understand the advantage of one over the other????
I feel like I'm missing something here (apart from a couple of million brain cells).

 

[Mile-O]

what's the difference between having the manufacturer id rather than the manufacturer name??
Isn't it basically the same thing???? and doesn't it cut down on the need for tables.

Now we're going into memory usage territory.

ManufacturerID: An autonumber (basically a long integer) uses four bytes.
Manufacturer: A text field uses one byte per character.

Now, as a primary key in a relational database, a text string is going to cause the greater use of memory as it will be replicated in each table that uses the primary key as a foreign key as opposed to a guaranteed four bytes for every foreign key with an autonumber.

As for cutting down on the need for tables - it's not about reducing the amount of tables you need, it's about reducing redundant data. A typical mistake people make is to build their tables with more than one type of information in it a la repeating groups (Info1, Info2, Info3) when it becomes quite clear that Info is a new type of information and is worthy of a table of its own. Creating tables is about splitting down all of the information into recognised objects (or classes) and deciding how they relate to each other.

 

[Pete Morris]

ok one final thing, talking about memory. (sorry I thought there was a Joke there but can't remember it).
With the use of autonumbers etc, will this speed up the 'performance' of a database in query's etc... as opposed to text strings??

 

[Mile-O]

I can't say for sure in this instance but when a field is Indexed it becomes faster for Jet to play about with. The more unique the better - the autonumber guarantees this unique property.

I think it's better. Once I created a database where PayrollID was to be the primary key for employees (as I was advised it should be) and, when we started putting the employee info in we had very few payroll numbers to put in. Using an autonumber as the primary key would create a unique key without having to require all the other info in the table (although it is best to get all the info in there)

 

[The_Doc_Man]

Using numbers speeds things up twice.

First, when you index on a number, you get more info per buffer because autonumbers are shorter to store than long text strings.

Second, when you link from table A to table B with a number, you speed up the (implicit) query that joins the tables because the linkage item is smaller.

Remember, deep down its all about CPU cycles. Intel PC clones have instructions that work on 32-bit numbers DIRECTLY as a SINGLE ENTITY. They have to do text field comparisons one byte at a time.

 

[neileg]

OK just to play devils advocate, what's the difference between having the manufacturer id rather than the manufacturer name??

What happens when the manufacturer changes their name? Like 'Flymo (Europe)' becomes 'Flymo (Northern Hemisphere)'?

 

[Pete Morris]

One final thing (again). If I have 10 or 15 different tables, is it ok just to leave access allocating the number for each new entry in each table???
When the records are pulled back together through a query or whatever, I assume there is no confusion with autonumbers being the same as they are form different tables, is this correct??

 

[namliam]

That is correct....

 

[Pete Morris]

thanks everyone for their help.

Cheers, Pete.