Re: Thanks!

From: Lynn Trapp (ltrappNoSpam_at_ltcomputerdesigns.com)
Date: 08/31/04 

Date: Tue, 31 Aug 2004 09:32:06 -0500

> 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 work*** 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. 

-- 
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm