Use of Standard Access IDs as Primary Keys

[Solar.Paul]

Hi I've built a system with around 20 tables in it. All of these use the standard ID field offered by default by Access as the primary key. A friend with a background in database design and development for large corporates using Oracle has reviewed the database (he is going to do some VBA programming for me) and suggested that we use more appropriate primary keys based on data attributes.

He has read "somewhere" that there are problems with the standard ID fields and that occasionally the fields are renumbered/reindexed and that this can cause problems. There are a fair number of queries and some complex forms which will need to be updated for these changes but it would be a shame to go through all this work if it isn't really.

Should we use the standard Access IDs and is it worth a chunk of effort to change from where we are now?

Thanks in advance
Solar Paul

 

[Galaxiom]

The "standard" keys as you call them are Long Integer fields with Autonumber.

They don't simply renumber themselves at random. Occasionally they can can start allocating previouly used numbers, hence preventing any further inserts due to the unique constraint because their "seed" has gone wrong. I have only seen this happen after inserting records with a query but it probably can happen for other reasons.

There can be good reasons to use a artificial key (no meaning in the data) instead of a natural key (one of the meaningful values). If the field used as a natural key ever changes, for example the customer number when a takeover happens then it is rather messy if it is also the primary key.

If you are concerned about the potential for seed problems then drop the autonumber facilty back to an ordinary Long and use an alternative method to allocate the key. These technique include what is usually knows as DMax + 1.

However there is absolutely no reason to change the key itself.

 

[RainLover]

You will find that reindexing will happen a lot. Namely every time a record is added or changed in some way.

This is simply normal.

 

[ButtonMoon]

Should we use the standard Access IDs and is it worth a chunk of effort to change from where we are now?

What do you intend to use as natural keys in your tables? Tables in general need some kind of user-visible identifier (AKA "natural" keys, "business" keys or "domain" keys) that allows users properly to identify information and relate it to the real things the database is supposed to be modelling. For a variety of reasons auto-numbered keys don't make good keys for that purpose; auto-numbered columns are not usually intended for end users to use as keys.

The natural keys should be enforced by uniqueness constraints and backed up by indexes in exactly the same way as your auto-numbered primary key should be. Many people (possibly including your friend) will refer to such keys as "primary" keys just because they are the "preferred" or most important identifier for data in the tables - even if they aren't ever used as foreign key references in other tables.

What's important is that your natural keys are present and enforced in the database. Whether you refer to them as primary key or not is less important. Assuming you have natural keys in your tables, then point them out to your database designer friend.

 

[gemma-the-husky]

as an example of what button just said

say you have two tables, one for customers, and one for orders

your customers tables has these fields

customername(PK), address, phonenumber etc

and the order table has these

customername, ordernumber, orderdate, etc

so each customer is related to the appropriate orders

-----

now the same set up with an autonumber field is this

customers
id (numeric PK), customername, address, phonenumber etc


orders
orderid(numeric PK), customerID (the ID field above), ordernumber, orderdate, etc


the changes are:

1. in the customers table we now have an autonumber PK, so we have to add an extra unique key to maintain the customernames (which would be done automatically iif it was the PK) - this is the "business key buttonmoon just mentioned

2. in the orders table, we are now storing the customerID rather than the customername.

-----

so what are the benefits/differences

1. numbers work far more efficiently than strings, so access can join the tables much easier with the numeric values.

2. you do lose the visibility of the customer name in the order table, but that is trivial.
a) you do not need to look at the table, generally
b) in practice, you will use a query to pick up the customer name

3. because the customername is now only included in one place, it can be changed without the changed string value needing to be propagated through the system. (cascading updates)

4. managing data (ie writing code) becomes much easier when you can use a single key field (numeric especially) compared with having to manage multiple-field joins.

so it is a matter of taste, but most of us are happy to add the slight extra overhead of a numeric key, as the benefits far outweigh the drawbacks

----
just to be clear. the numeric keys are just there to manage the relatedness of records between your tables in the most efficient way. you still need to consider what additional key structure needs to be implemented to add integrity to your system, just as you would have, if you did not have the numeric keys.

 

[Solar.Paul]

Thanks guys.

Your input has given us the confidence to go ahead with the numeric IDs as primary keys. There do appear to be advantages with this approach and it sounds like the problems are few and far between.

FYI we are using these fields as foreign keys in other tables and have embraced queries for look up so we aren't bamboozled by looking at meaningless numbers.

We appreciate the effort you made. It has been a help

Cheers
Solar.Paul

 

[gemma-the-husky]

good luck with your project.

as a final observation, most of us prefer not to define the fields as lookups - as we prefer to see the data directly - and define our own combo boxes/list boxes. often the one access creates automatically is not exactly what we want, and there are other subtle issues as well.

The other thing to avoid is probably having captions defined at the table level. When you do this, queries get defined using the caption rather than the true field name, and it can be very awkward establishing what a query is really doing, when you come to it later. Again, you can defined these easily as you need them.