Key field best practice

Tskutnik

Registered User.
Local time
Today, 02:25
Joined
Sep 15, 2012
Messages
234
Hello all,
General question. If a table is populated with a text or value field that is truly unique is it still better to use auto-numbering as the key? ... and ... if the source data is OK as the key, is that conditional based on data type (e.g. a unique number is more efficient or better than unique text...).

The table(s) are not just lists, they are actual data tables with 5 - 8 columns of info. I'd assume tables being used as just lists are definitely OK with the unique as the actual data.

My database is huge so performance is a consideration.

Thanks for your help
 
4 byte numbers (autonumbers) are more efficient than text. If text, then the shorter the better. The text may well be propagated throughout numerous tables as a FK.

Therefore one further consideration is whether the value of the text may ever change, as that would either leave to orphans, or would need cascading updates.

Personally I prefer a numeric, but for something like say, a US state code, or a UK post code, it would be fairly pointless adding an extra lookup table just to add a numeric PK.

For something like a vehicle registration, say, it's less clear cut.
a) they can change, because of personalised plates - MY72 BWX ---> M1 REG
b) users can enter the "same" thing in more than one style MY01REG and MY01 REG
 
Thank you very much for the help. This all makes sense.
 
Fundamentally, a PK is just a unique index. Like all indexes, it takes resources to build it. Every time a record is added to the table the index must be updated.

If there is already a field in the data whose values will never change and already requires a unique index, then adding another separate field as the PK is really just adding an extra overheads.

There are are a number of myths surrounding Primary Keys in Access.

One claims that all tables should have an Autonumber PK. Aside from saving the resources need to make another index, a suitable natural key can be more reliable. The natural key avoids the problems that can happen with autonumber from time to time when they lose their seed and start trying to insert duplicates.

A second myth claims that all tables must have a Primary Key. If the table is the end of the chain of relationships and there is no requirement to uniquely update or delete the record via a query then there is no need for the table to have a PK. Leaving it off saves significant resources and time required to insert records into the table.

Indeed, during bulk inserts to tables with any kinds of keys, it can be beneficial to drop the indexes before the insert and rebuild them afterwards.

Note however that ODBC linked tables do need a PK. If the original table doesn't have one designated, Access will ask for a field or combination of fields to be used as the PK during the linking. Get that wrong and weird data will appear in Access.

The third myth is that composite PKs should never be used because they are inefficient. A composite key requires that the relate tables also carry all the fields that have been designated as the PK in the parent table. On the surface this sounds like a serious penalty but under some circumstances, it can allow the related table to be queried without having to join the parent table, enhancing rather then degrading efficiency. With a surrogate key, the field to be queried may otherwise only be in the parent table where it can only be access via a join.

A good example of this is a database used my multiple companies. Say you want to query invoices. With a composite key that includes the CompanyId, the Invoice records can be queried for a particular company without joining to a parent table.

A closely related myth is the claim that composite PKs are a "nightmare". The claim is rubbish. It just means the query needs a join on each field in the key. The database engine, being aware of the keys, will recognise the joined fields comprise a unique index and use it as though they were a single join.

Bottom line. Ensure you understand why design choices are made. Beware those who simply repeat "conventional wisdom" and insist on how it "should be".
 
Last edited:

Users who are viewing this thread

Back
Top Bottom