Nanette said:
Although Part Numbers are unique, is it better to use auto numbering
(such as id_PN) as the primary key in a table? What are the pro's and
con's?
This is a personal preference issue that many are "religous" about. Some
take the view that the PK should always be a natural data key and a
surrogate field like AutoNumber should never be used. Others use an
AutoNumber PK in every table even when there is a natural key field that
would do the job.
My personal view is if the table has a good candidate natural key I will use
it. I define "good" to mean that I can create a natural key with two fields
or less, that the fields are unlikely to ever change, and that they are not
DataTypes that might have compatibility issues between various database
engines (DateTimes and non-precise numerics for instance fall into this
category). If those rules cannot be satisifed then I use a numeric
surrogate as the Primary Key.
Having said that my surrogate key might not always be an AutoNumber. It
might be a number that is programatically assigned when records are
inserted. In addition, the field or fields that make up the natural key
that I chose NOT to use still should have a unique index on them to prevent
duplicates.
