compound primary key versus autonumber

[Kevin K. Sullivan]

I'm creating a table to hold conference call minutes. Each call must
have exactly one Study, exactly one CallType, and exactly one CallDate.
I'm torn between two designs. Do I use a three-field composite primary
key or do I create a CallID Autonumber as PK and add a composite index
to the three fields? What are the pros and cons of each? TIA, Kevin

table Calls:
Study TEXT(10)
CallType TEXT(15)
CallDate DATETIME
(all above constitute PK)
Minutes Memo


vs.

CallID AUTONUMBER - PK
Study TEXT(10)
CallType TEXT(15)
CallDate DATETIME
(index on above 3 fields)
Minutes Memo

 

[Duane Hookom]

I expect some disagreement on this but I would create a CallID autonumber,
primary key and create a unique index on Study, CallType, and CallDate.

 

[TedMi]

The unique index on the 3-field compound key is absolutely necessary,
otherwise you cannot enforce uniqueness. Contrary to popular belief, an
autonumber does not provide uniqueness of data, on the contrary, it
facilitates duplicates unless other steps are taken to prevent them.
An Autonumber PK is useful only if you will have child tables related to
this one. It's much more efficient to relate on a single field than on 3. If
this table has no dependents, then the autonumber serves no purpose. That
said, it's often useful to include the autonumber just in case the need for a
dependent table arises later.

HOWEVER: Caveat on using date in a unique index. The date type includes both
calendar and time-of-day information. So two date values on the same day but
at different times satisfy the uniqueness constraint. This is especially true
if you use the Now() function to insert date values. Using it at different
times on the same day will produce unique date values. Furthermore, this
problem is unaffected by the presence or absence of an autonumber PK. In
either case, you can have multiple entries for the same Study and Call Type
at different times on the same date. If by unique date you mean anytime
during the same day, then you must ensure that date values are consistently
generated with a time component of 00:00.

 

[Jerry Whittle]

I agree with Duane (and knowing Duane's excellent work would be a fool not
to). I've been burnt too many times by natural primary keys and composite
keys. An autonumber PK will be unique and a unique index on the other three
fields will keep away duplicates. IMHO the extra overhead of two indexes for
the autonumber Pk and unique constraint are well worth it.

Also if you need to create a child table to the Calls table, you'll find
linking one field from each much easier to work with than linking three
fields from each.

 

[Chris2]

I expect some disagreement on this but I would create a CallID autonumber,
primary key and create a unique index on Study, CallType, and CallDate.

RDBMS purists may say otherwise, but I have to go with Duane's
opinion, for many, many reasons.


Sincerely,

chris O.

 

[Mikal via AccessMonster.com]

In my network, autonumber fields will reset themselves to values that have
been already generated. It is a bug that the network administrators are
aware of and they are not tripping over themselves to squash it. If you know
that your autonumber field will not do this under any circumstances you are
OK. Otherwise, you might consider setting it to ReplicationID and avoid a
lot of hassle.
HTH
Mike

 

[John Welch]

Kevin, you never actually state that you need to insure no duplicates in the
three field combination. Will you never want to have two calls on the same
day for the same study with the same CallType?
-John

 

[Jerry Whittle]

There are only three ways that an Access autonumber can be reused.

1. All the records have been deleted from a table AND a compact and repair
is done.
2. The autonumber is not set as the primary key or unique constraint. Then
it is possible to insert duplicate numbers into the table.
3. The database is corrupt and I've never seen corruption cause this behavior.

 

[Rick Brandt]

There are only three ways that an Access autonumber can be reused.

1. All the records have been deleted from a table AND a compact and
repair
is done.
2. The autonumber is not set as the primary key or unique constraint.
Then
it is possible to insert duplicate numbers into the table.
3. The database is corrupt and I've never seen corruption cause this

Create a table with ID (AutoNumber and PK), enter five records, delete record
with ID = 3 and then use an append query to insert a record having 3 for the ID
value. Any used AutoNumber value that has been deleted can be reinserted this
way.

 

[Tim Ferguson]

"=?Utf-8?B?SmVycnkgV2hpdHRsZQ==?="

3. The database is corrupt and I've never seen corruption cause this
behavior.

Well publicised bug in early releases of Access 2000, not cured until
(IIRC) SP3. Created a huge problem because the dbengine allocated an
already-existing number, and then promptly refused to INSERT it -- result
was an unusable database and totally screwed users.

History now, of course, but we can only wait for what delights will come
when the Access team will take over the Jet engine itself. Autofill at the
db engine level, anyone?

Tim F

 

[Tim Ferguson]

An autonumber PK will be unique and a unique index on the other three
fields will keep away duplicates. IMHO the extra overhead of two
indexes for the autonumber Pk and unique constraint are well worth it.

Apart from John's point downthread that the OP did not state that (study,
calltype, calldate) had to be unique...


The extra overhead of two indexes are worth what, exactly?

There is no saving on the part of the user, just a slower interface

There is no saving on the part of the developer, just a need to create
more multitable joins

There is no benefit to the logical design, since creating an artificial
key does nothing to identify what is actually _in_ the records; it's just
a way of taking the table out of BCNF

I agree completely that the cost of adding an AN artificial key is very
small, but it does not lead to a single advantage. Nothing works better
with it, only the same or a bit slower.

I've never understood this argument.

Tim F

 

[Jerry Whittle]

I'll be danged! I've been working with Access since version 1 and have never
seen nor heard of this behavior. I seen it happen with autonumber fields that
were not a primary key.

Thanks for teaching this old dog a new trick.