Discussion: What are the advantages/disadvantages to combination k

So I'd like to apologize for asking
people to re-visit a common issue.

Truly nothing to apologize for, rpw. It's good to revisit this question from
time to time.

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

true. when using the foreign keys from the parent tables as the combo
primary key of the child table, no problem. but in the few instances when
i've used that child table in turn as the parent of another table, i've
opted for a unique index on the combined foreign key fields where
appropriate, and a surrogate primary key. cringes and covers head with
arms


"Lynn Trapp" wrote in message
...
Hi Tina!
Ok, I'll go easy on you -- this time! A single field primary key is nice
for
creating relationships between tables but, in my opinion, unnecessary in
the
case of a junction table, as described by rpw. Basically, each field in
the
table is the child of only one parent table.

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


"tina" wrote in message
...
personally, i normally don't use a combination primary key in any table
that
is the "parent" in a parent/child relationship with another table,
because
i
don't like multi-field foreign keys. but that's just me. (be gentle,
Lynn!
g)


"rpw" wrote in message
...
Hi Lynn,

Thank you for responding. Sorry for asking pea-brain questions, but
does
the junction table having child tables or the number of fields making
up
the
combination PK have any influence on the decision?

"Lynn Trapp" wrote:

rpw,
In my opinion, the only reason for ever using an AutoNumber field
for
a
primary key is when there is not an easily identifiable natural key.
In
a
junction table the 2 primary keys from the foreign tables are a
perfect
natural key. Therefore, it seems to me, that Option II is a bit of
over
kill, especially since you would want to put a Unique Index on the
combined
foreign keys anyway.

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


"rpw" wrote in message
...
Hi everyone,

I'm interested in hearing the opinions of people who use (or
choose
not to
use) combination keys in their table structure. What do you think
the
advantages and disadvantages of using combination keys are?

If I were to have these tables:

tblMainTopic
MainID

tblSubTopic
SubID

Then I have two options for relating the two above tables in a
junction
table.

Option I:

tblManyToMany
MainID { These two foreign keys
SubID { are joined as a combination key

Option II:

tblManyToMany
m2mID 'auto-number primary
MainID 'foreign key
SubID 'foreign key

Thanks to all who take the time to post their opinions.

--
rpw

No need to duck, young lady. I would most likely do the same thing. I just
cringe at the thought of using a surrogate primary key as the sole means of
uniquely identifying a record.

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


"tina" wrote in message
...
true. when using the foreign keys from the parent tables as the combo
primary key of the child table, no problem. but in the few instances when
i've used that child table in turn as the parent of another table, i've
opted for a unique index on the combined foreign key fields where
appropriate, and a surrogate primary key. cringes and covers head with
arms


"Lynn Trapp" wrote in message
...
Hi Tina!
Ok, I'll go easy on you -- this time! A single field primary key is nice
for
creating relationships between tables but, in my opinion, unnecessary in
the
case of a junction table, as described by rpw. Basically, each field in
the
table is the child of only one parent table.

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


"tina" wrote in message
...
personally, i normally don't use a combination primary key in any
table
that
is the "parent" in a parent/child relationship with another table,
because
i
don't like multi-field foreign keys. but that's just me. (be gentle,
Lynn!
g)


"rpw" wrote in message
...
Hi Lynn,

Thank you for responding. Sorry for asking pea-brain questions, but
does
the junction table having child tables or the number of fields
making
up
the
combination PK have any influence on the decision?

"Lynn Trapp" wrote:

rpw,
In my opinion, the only reason for ever using an AutoNumber field
for
a
primary key is when there is not an easily identifiable natural
key.
In
a
junction table the 2 primary keys from the foreign tables are a
perfect
natural key. Therefore, it seems to me, that Option II is a bit of
over
kill, especially since you would want to put a Unique Index on the
combined
foreign keys anyway.

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


"rpw" wrote in message
...
Hi everyone,

I'm interested in hearing the opinions of people who use (or
choose
not to
use) combination keys in their table structure. What do you
think
the
advantages and disadvantages of using combination keys are?

If I were to have these tables:

tblMainTopic
MainID

tblSubTopic
SubID

Then I have two options for relating the two above tables in a
junction
table.

Option I:

tblManyToMany
MainID { These two foreign keys
SubID { are joined as a combination key

Option II:

tblManyToMany
m2mID 'auto-number primary
MainID 'foreign key
SubID 'foreign key

Thanks to all who take the time to post their opinions.

--
rpw

thanks, Lynn! maybe continued association will cause some of those good
"purist" habits to rub off on me. bows and smiles, surreptitiously wiping
brow


"Lynn Trapp" wrote in message
...
No need to duck, young lady. I would most likely do the same thing. I just
cringe at the thought of using a surrogate primary key as the sole means
of
uniquely identifying a record.

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


"tina" wrote in message
...
true. when using the foreign keys from the parent tables as the combo
primary key of the child table, no problem. but in the few instances
when
i've used that child table in turn as the parent of another table, i've
opted for a unique index on the combined foreign key fields where
appropriate, and a surrogate primary key. cringes and covers head with
arms


"Lynn Trapp" wrote in message
...
Hi Tina!
Ok, I'll go easy on you -- this time! A single field primary key is
nice
for
creating relationships between tables but, in my opinion, unnecessary
in
the
case of a junction table, as described by rpw. Basically, each field
in
the
table is the child of only one parent table.

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


"tina" wrote in message
...
personally, i normally don't use a combination primary key in any
table
that
is the "parent" in a parent/child relationship with another table,
because
i
don't like multi-field foreign keys. but that's just me. (be gentle,
Lynn!
g)


"rpw" wrote in message
...
Hi Lynn,

Thank you for responding. Sorry for asking pea-brain questions,
but
does
the junction table having child tables or the number of fields
making
up
the
combination PK have any influence on the decision?

"Lynn Trapp" wrote:

rpw,
In my opinion, the only reason for ever using an AutoNumber
field
for
a
primary key is when there is not an easily identifiable natural
key.
In
a
junction table the 2 primary keys from the foreign tables are a
perfect
natural key. Therefore, it seems to me, that Option II is a bit
of
over
kill, especially since you would want to put a Unique Index on
the
combined
foreign keys anyway.

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


"rpw" wrote in message
...
Hi everyone,

I'm interested in hearing the opinions of people who use (or
choose
not to
use) combination keys in their table structure. What do you
think
the
advantages and disadvantages of using combination keys are?

If I were to have these tables:

tblMainTopic
MainID

tblSubTopic
SubID

Then I have two options for relating the two above tables in a
junction
table.

Option I:

tblManyToMany
MainID { These two foreign keys
SubID { are joined as a combination key

Option II:

tblManyToMany
m2mID 'auto-number primary
MainID 'foreign key
SubID 'foreign key

Thanks to all who take the time to post their opinions.

--
rpw

Thanks for your kind words.

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'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.

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.

Thanks again for your comments. :-)

"Lynn Trapp" wrote:

So I'd like to apologize for asking
people to re-visit a common issue.

Truly nothing to apologize for, rpw. It's good to revisit this question from
time to time.

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

You're most welcome, Tina! Keep up the good work and you'll get it right
yet...g bows and smiles back, sees the surreptitious brow wiping...g

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


"tina" wrote in message
...
thanks, Lynn! maybe continued association will cause some of those good
"purist" habits to rub off on me. bows and smiles, surreptitiously
wiping
brow


"Lynn Trapp" wrote in message
...
No need to duck, young lady. I would most likely do the same thing. I
just
cringe at the thought of using a surrogate primary key as the sole means
of
uniquely identifying a record.

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


"tina" wrote in message
...
true. when using the foreign keys from the parent tables as the combo
primary key of the child table, no problem. but in the few instances
when
i've used that child table in turn as the parent of another table,
i've
opted for a unique index on the combined foreign key fields where
appropriate, and a surrogate primary key. cringes and covers head
with
arms


"Lynn Trapp" wrote in message
...
Hi Tina!
Ok, I'll go easy on you -- this time! A single field primary key is
nice
for
creating relationships between tables but, in my opinion,
unnecessary
in
the
case of a junction table, as described by rpw. Basically, each field
in
the
table is the child of only one parent table.

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


"tina" wrote in message
...
personally, i normally don't use a combination primary key in any
table
that
is the "parent" in a parent/child relationship with another table,
because
i
don't like multi-field foreign keys. but that's just me. (be
gentle,
Lynn!
g)


"rpw" wrote in message
...
Hi Lynn,

Thank you for responding. Sorry for asking pea-brain questions,
but
does
the junction table having child tables or the number of fields
making
up
the
combination PK have any influence on the decision?

"Lynn Trapp" wrote:

rpw,
In my opinion, the only reason for ever using an AutoNumber
field
for
a
primary key is when there is not an easily identifiable
natural
key.
In
a
junction table the 2 primary keys from the foreign tables are
a
perfect
natural key. Therefore, it seems to me, that Option II is a
bit
of
over
kill, especially since you would want to put a Unique Index on
the
combined
foreign keys anyway.

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


"rpw" wrote in message
...
Hi everyone,

I'm interested in hearing the opinions of people who use (or
choose
not to
use) combination keys in their table structure. What do you
think
the
advantages and disadvantages of using combination keys are?

If I were to have these tables:

tblMainTopic
MainID

tblSubTopic
SubID

Then I have two options for relating the two above tables in
a
junction
table.

Option I:

tblManyToMany
MainID { These two foreign keys
SubID { are joined as a combination key

Option II:

tblManyToMany
m2mID 'auto-number primary
MainID 'foreign key
SubID 'foreign key

Thanks to all who take the time to post their opinions.

--
rpw

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.


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

"Lynn Trapp" wrote:

You may find that a Google search
on "surrogate keys" would return you some interesting, and lively,
discussions of the subject.

I'll do that later on today...

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?

I'll keep working on that. In the meantime I looked at my db again and the
'duplicates' are not actually duplicate records, but rather multiple entries
on the same day by the same employee. This could have happened for any
number of reasons. As I study this issue deeper, I'll consider the indexing
of date, EmpID, (maybe start and end times too) vs. combination key.

I'm in the fortunate position of not having any time pressure on this
project so I can take the time to do it 'right'. But then on the other hand,
I have to fit this into free time between work and home - so development is
very slow and sometimes I have to 're-learn' how to do things.




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.

I must have a different book than you (or maybe I got the title wrong?) - I
thought that this one's by Michael Hernandez.


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

Lynn Trapp wrote:

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.

DD for MM was written by Mike Hernandez.

Rebecca's book that Lynn refers to is
Designing Relational Database Systems, 2nd Edition
www.awprofessional.com


--
Joan Wild
Microsoft Access MVP

DD for MM was written by Mike Hernandez.

Oops.... I'm reading Rebecca's book right now and must have had it on my
brain. Thanks Joan.

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