auto-numbering fields or sequence

[niqola]

Hi,
What is the interest of using a field with autonumbering as a primary key?
I have an old db in access to rebuild totally in Oracle. In many tables, we have this structure:
table_name(
id(auto-numbering)
name(text))
I think of throwing away the field "id" because I don't see its usefullness. Could you give me advice about that.
Thx

 

[Craig0201]

Information theory time.....

Say that the number is a long integer and takes up two bytes. The average name is 20 characters and therefore takes up 20 bytes. I have 20 000 rows in the table. My space is thus 440000 bytes, 40 000 of integer, 400 000 of char.

Now I have a table with using either the number or the name as the foreign key. The table has 100 000 rows. The space taken by the number is 200 000 bytes whereas the space taken by the name is 2 000 000 bytes.

I have therefore saved 1 800 000 bytes at the cost of 40 000.

Imagine this in a table where the natural key is a combination of 10 char fields, each of 25 characters. The saving is large. Hence a use for the autogenerated field.

Whether you need it or not depends on your circumstances.....

Craig

 

[CajunCenturion]

There is also performance issue. It's faster to search for and compare numeric values than it is to compare string values.

There is also the update issue. If one of the name (text) entries change, then you only have to change it in the lookup table, rather then for every record in the database. This becomes even more beneficial if this lookup table appies against multiple "master" tables.

Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

 

[lespaul]

Besides, every table should have an id field. From a maintenance standpoint alone, it makes things much easier. I recently designed a database system for Jury Tracking. In my Juror Table, each juror has a unique id (couldn't use the SSN because people can be summoned repeatedly). Within this table, there is a field StatusID. This is tied to the Status Table:

1 = Will Serve
2 = Postponed
3 = Excused Medical
etc.

If I stored the entire description of the status in the Juror Table instead of just the code, when the users came back to me 6 months later and say "We want Postponed change to Postponement" instead of having to write a query to update all the records, all you have to do is go to Status Table and change the description.

This design also allows you to control what values can be stored in that field (by enforcing referential integrity). If I allowed the user to type in each status code, that field may be filled with:

Wil serve
Will Server
WS
Will Serve

which makes report generation a NIGHTMARE! So instead of having to come up with every variation of status code a user can come up with, I only have to search for jurors whose status code matches a specific number.

For this and the above stated reasons, I would suggest not getting rid of the id field.




Leslie
landrews@metrocourt.state.nm.us

There are 10 types of people in the world -
those who understand binary
and
those who don't!