Discussion: What are the advantages/disadvantages to combination k

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

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

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

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

I use surrogate AutoNumber PK because when I need to identify a Record in
code, I can use a single-field PK rather than multi-Field PK coding (a bit
more messy as I have to identify by 2 or more values rather than one).

But like Lynn wrote, I still have to set Unique Index on the ForeignKeys,
anyway. Perhaps, Lynn's system is more correct for the database purists,
though.

--
HTH
Van T. Dinh
MVP (Access)




"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)

Thank you tina and Van for jumping in.

So far I've can only see that there are only two advantages to the combo PK.
The first is saving field space (fields are expensive, records are cheap).
But that savings is lost as soon as there is a child table. The second is
the cost of indexing the combined fields. I understand that there is a limit
to the number of indexes? Is the speed of record retrieval faster with a
single field PK than combined/indexed fields? Are there any more advantages
to a combo PK?

The advantages to the single PK are easier parent/child relation structure
(fewer fields to duplicate and drag in the relationship window) and less
typing (chance for error?) when writing code or SQL involving the PK. Are
there other advantages?

Are there any disadvantages to either method?

Now that I think about it a little more, if you had a list or combo box used
to select a record from a table that has a combo PK, can you set multiple
columns as the bound column?

"Van T. Dinh" wrote:

I use surrogate AutoNumber PK because when I need to identify a Record in
code, I can use a single-field PK rather than multi-Field PK coding (a bit
more messy as I have to identify by 2 or more values rather than one).

But like Lynn wrote, I still have to set Unique Index on the ForeignKeys,
anyway. Perhaps, Lynn's system is more correct for the database purists,
though.

--
HTH
Van T. Dinh
MVP (Access)




"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)

I have just finished reading another thread "How to ID a record" in which Ken
Snell refers to the issue of 'natural' vs 'surrogate' keys as being a great
debate and there being much to find about the debate by googling.

After reading that thread I realized that my question was basically the
same, merely phrased less eloquently. So I'd like to apologize for asking
people to re-visit a common issue. And, I'd like to thank Lynn Trapp, tina,
and Van T. Dinh for adding their comments to this thread.

I now have a better understanding of the issue and will continue to use
surrogate keys instead of natural combination keys, my reason being primarily
because of the easier coding.

Thanks again for participating.. :-)

rpw,
Yes, the number of fields could influence your decision and your database's
performance

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


"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

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

I use surrogate AutoNumber PK because when I need to identify a Record in
code, I can use a single-field PK rather than multi-Field PK coding (a bit
more messy as I have to identify by 2 or more values rather than one).


Definitely on of the nice use of AutoNumber but so many people get trapped
into thinking that it provides a way to avoid redundant data that they skip
the next step. Perhaps, the AutoNumber has just been oversold and over
used.

But like Lynn wrote, I still have to set Unique Index on the ForeignKeys,
anyway. Perhaps, Lynn's system is more correct for the database purists,
though.

Definitely a purist here. g

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