As I read through the other posts on this thread and the thread on "How to
ID a record", I realize my concept of keys might be slightly askew. I just
figured that the autonumber was a convenient way of labeling the record with
a number and not having to worry about the user assigning duplicate numbers.
I must have missed/minimized the importance of avoiding user-entered
duplicate data.
I would say that the largest majority of database developers are proponents
of surrogate keys. In my view, one of the problems with using them
exclusively is precisely that they are too convenient. So convenient that
people, especially beginners, are led to believe they have done all they
need to do for data redundancy when the use one -- but they have actually
done nothing to prevent it in that case. You may find that a Google search
on "surrogate keys" would return you some interesting, and lively,
discussions of the subject.
I've only designed one db of any consequence and it's still under
development while I'm trying to learn enough to do it properly. I have a
"find duplicates" report to locate duplicate "time card" entries. The leader
of a local user group suggested that rather than use such a report, that I
index the employeeID and date to prevent the duplicates. However, I chose
this route because in the 'real world' these employees don't use timecards.
They use "time worksheet forms" and occasionally someone will submit a
duplicate form.
I don't necessarily want the data entry person to struggle with error
messages while trying to enter the data on the form, so I have the report run
before the "time card summary" report runs. This then triggers the payroll
department to locate the duplication, figure out which one is correct, make
the corrections, and interview the 'offending' employee.
You should probably reconsider the advice of the local user group. By
allowing the employees to enter duplicates, you not only violate one of the
cardinal principles of database design, but make more work for you payroll
department, when they have to track down the employee who entered the
duplicate. I assume after they talk to this employee that they then have to
go out and delete the duplicate record. Wouldn't it be better to find a way
to preven its entry in the first place?
btw, on Saturday I got delivery of "Database Design for Mere Mortals", so
maybe by the time I finish reading it, I'll be a little better oriented on
these 'Natural key, combo key, surrogate key issues. But for now, I still
like the convenience of using just a single PK field.
That's excellent. Rebecca has an excellent discussion of "candidate keys" in
a real world context and in easy to understand language. She seems to lean
in favor of surrogate keys but not without a proper understanding of data
duplication.
