Complex Multi Field Key Question

[Guest]

I have a key that needs to be based upon 3 fields: "Service", "Cycle" and
"Rate Code". I have a query that contains each of these items along with
relevant financial data driving the rates for clients. I am pulling this
data via ODBC (not sure if that matters).

I need to tie this "multi field Key" to 2 tables "Account" and
"Subscription". These two tables have a one to one relationship between them
(Account ID). The "Account" table has the "Rate Code" and "Cycle" fields,
while the "Subscription" table has the "Service" field. In essence, the
"Service", "Cycle" and "Rate" fields are needed to determine what the rate in
dollars each customer is currently on by a relationship to the "Current Rate"
field in the "Subscription" table.

For example, the customer may be on a SUNDAY subscription, MONTHLY billing
cycle and a 80 rate code which would indicate they are charged 1.00 per
issue.

I have tried linking the Rate Query fields mentioned above to the two tables
and Access gives me an outer join error. I am not that experienced and was
hoping for some help from the experts.

Thanks in advance.

 

[Guest]

Hi Tim,

I have a general dislike for combined field primary keys. You should be able
to accomplish the same result by using a multifield unique index. This would
allow you to add a meaningless autonumber primary key, which you can then use
for joining to other tables. (In your 1:1 relationship, one table would need
a number / long integer data type to join to the autonumber; you cannot join
two autonumber fields). Here is an article that you might be interested in
reading:

The case for the surrogate key (see article # 4)
http://www.access.qbuilt.com/html/articles.html

Here is a posting from Utter Access that I bookmarked a few years back. I
think the author makes some reasonable arguments on this topic:

http://www.utteraccess.com/forums/s...=247916&page=0&view=collapsed&sb=5&o=&fpart=1


Good Luck,

Tom Wickerath, Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

 

[Guest]

Thanks for the insight, Tom.

Hi Tim,

I have a general dislike for combined field primary keys. You should be able
to accomplish the same result by using a multifield unique index. This would
allow you to add a meaningless autonumber primary key, which you can then use
for joining to other tables. (In your 1:1 relationship, one table would need
a number / long integer data type to join to the autonumber; you cannot join
two autonumber fields). Here is an article that you might be interested in
reading:

The case for the surrogate key (see article # 4)
http://www.access.qbuilt.com/html/articles.html

Here is a posting from Utter Access that I bookmarked a few years back. I
think the author makes some reasonable arguments on this topic:

http://www.utteraccess.com/forums/s...=247916&page=0&view=collapsed&sb=5&o=&fpart=1


Good Luck,

Tom Wickerath, Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

 

[RoyVidar]

Tom Wickerath wrote in message

Here is a posting from Utter Access that I bookmarked a few years
back. I think the author makes some reasonable arguments on this
topic:

http://www.utteraccess.com/forums/s...=247916&page=0&view=collapsed&sb=5&o=&fpart=1

From that article:

"A very tempting, but incorrect, Primary Key for tblJunction is a
composite one consisting of the two foreign key fields ID1 and ID2.
However, the Primary Key, as always, should be a single-field,
preferably AutoNumber."

Composite primary key incorrect???
A primary key should be single field???

I haven't been able to find anything supporting that in my database
literature. Could anyone point out any references to serious database
literature supporting this?

 

[Guest]

Hi Roy,

Composite primary key incorrect???
A primary key should be single field???

This is the opinion of the person who wrote the article, although it is not
shared by all developers.

I haven't been able to find anything supporting that in my database
literature. Could anyone point out any references to serious database
literature supporting this?

I did some research tonight, looking at LOTS of books in my library and a
few web-based resources. Here is what I found, which may or may not fit your
definition of "serious database literature":

From Access 2002 Desktop Developer's Handbook
by Paul Litwin, Ken Getz and Mike Gunderloy (page 88)

"The decision as to which candidate key is the primary one rests in your
hands; no absolute rule dictates which candidate key is best. Fabian Pascal,
in his book SQL and Relational Basics, notes that the decision should be
based on the principals of minimality (choose the fewest columns necessary),
stability (choose a key that seldom changes), and simplicity/familiarity
(choose a key that is both simple and familiar to users).


From SQL Queries for Mere Mortals
by Michael Hernandez and John Viescas (page 38)

"Define a Simple Primary Key when you can because it's more efficient and is
much easier to use when establishing a table relationship. Use a Composite
Primary Key only when it's appropriate (for example, to define and create a
linking table)."


Designing Effective Database Systems
by Rebecca M. Riordan (page 33)

"Some people are under the impression that composite keys are somehow
incorrect, and that they must add an artificial identifier--either an
identity or autonumber field--to their tables to avoid them. Nothing could be
further from the truth. Artificial keys are often more convenient, as we'll
see, but composite keys are perfectly acceptable."


Database Normalization Tips
by Luke Chung
President of FMS
December 2001, last updated March 2005

http://www.fmsinc.com/tpapers/genaccess/databasenorm.html

In general, a key field should have these characteristics:

Should be One Field
It is possible to define multiple fields as the key fields of a table, but a
single field is preferable. First, if multiple fields are necessary to define
uniqueness, it takes up more space to store the key than a single numeric
field. Second, additional indexes on the table also have to use the
combination of the key fields which takes up more space than if it were a
single field. Finally, identifying records in the table requires grabbing a
combination of fields. Far better to have a CustomerID number than a
combination of other fields to define a customer.


So, perhaps the author of the Utter Access article was a bit strong in his
language, but his recommendation is supported by others. The point that he
was trying to emphasize was that it is easier to work with a simple key
versus working with a composite key. As always, YMMV (Your Mileage May Vary).

Hope this helps.

Tom Wickerath, Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

 

[Roger Carlson]

The assertion in the article (that it is *always* incorrect to have a
multiple field primary key for a junction table) is rubbish. If it is a
*pure* junction table (that is, the table's ONLY use is to implement a
Many-to-Many relationship), then the *preferred* way is to make the primary
key a combination of the two foreign keys (especially if these foreign keys
relate to autonumber primary keys in the base tables). By adding a
surrogate key (autonumber primary key), you are simply maintaining an
additional index for no reason.

That said, if the junction table participates in any *other* relationships,
it is useful to create a surrogate key and create a unique index made up of
the two foreign keys. Even so, it's not mandatory (see Problem 3 below).

Let me discuss the author's three problems:

Problem 1a:
If you want to delete a record from your junction table, you'd have to
supply values for both fields, rather than a single value. So you would
have to do this:
Currentdb.Execute "DELETE FROM tblSudentClasses WHERE StudentID = " &
me.cboStudent & " AND ClassID = " & me.lstClasses.

instead of this:
Currentdb.Execute "DELETE FROM tblStudentClasses WHERE StudentClassID = " &
me.lstClasses

Perfectly true. But his assertion that it is faster and more effiecient is
just not so. It is, of course, a tad more difficult to code, but in terms
of execution, the the difference would be negligible. In fact, since Access
stores primary keys as a clustered index, it may even be faster! You'd have
to do a benchmark to test it, but to a human difference will be
indistinguishable.

Problem 1b:
"if you ever decide to allow a student to take a class multiple times you
have to change this code to include your new field of the PK (like class
date)"
This is true, but if it is ever possible that a student can take a class
multiple times, then you should have taken that into account in your initial
database design. And even if you didn't, and you had to make this change,
you'd still have to modify your table structure because you'd want to store
this date and the junction table would be the logical place. And you would
*still* want to maintain real-world uniqueness in your junction table, which
would require a unique index on the three fields anyway.

Problem2:
"What if you wanted to select a record with a multi-field PK from a combo
box?
John Calculus
John Chemistry
Jane French
Jane Biology"

This case is very true, but does not apply to the Junction table. Remember,
we are talking about foreign keys that are related to surrogate keys in the
main tables. You would never have values as above. This argument is
pertinent to the notion of using multiple field primary keys in general, but
not the specific case of a junction table.

Problem3:
"Now you have tblTests with TestID and want to create tblStudentClassTests
to show what each student got on each test for each class."

This is the case above where the junction table is not a pure junction
table, ie one that participates in another relationship. In this case, it
IS preferred to create a surrogate key for the primary key, but it is not
MANDATORY. His contention is that you'd HAVE to store values from both
fields in the composite key in the related table is just plain wrong.

Let's take the case above. You've created your database with
tblStudentClass as a junction table. Later, you are asked to modify the
database to store test scores. You create tblTest and realize this is also
a M:M relationship that requires its own junction table
(tblStudentClassTests). Rather than relating the table on the primary key
of tblStudentClass, you'd create an autonumber field and add a Unique Index
on it (call it StudentClassID). The you could relate your table on this new
field, rather than the primary key. While we usually create relationships
on primary key/foreign key, it doesn't have to be so. Any field with a
unique index will work. (Note: an autonumber field with a unique index is
also guaranteed to be non-null).

Now I'm not saying I'd design it like that from scratch. If I *knew* a
junction table would participate in other relationships, I *would* create a
surrogate key. However, if I found myself in the situation where I had to
add a new relationship after the fact, I would NOT have to resort to
relating on multiple fields.

All of this is not to say I'm a fan of Natural Keys. I'm not. I use
Surrogate Keys in almost every table EXCEPT in pure junction tables.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

 

[RoyVidar]

Tom Wickerath wrote in message

Hi Roy,


This is the opinion of the person who wrote the article, although it
is not shared by all developers.


I did some research tonight, looking at LOTS of books in my library
and a few web-based resources. Here is what I found, which may or
may not fit your definition of "serious database literature":

From Access 2002 Desktop Developer's Handbook
by Paul Litwin, Ken Getz and Mike Gunderloy (page 88)

"The decision as to which candidate key is the primary one rests in
your hands; no absolute rule dictates which candidate key is best.
Fabian Pascal, in his book SQL and Relational Basics, notes that the
decision should be based on the principals of minimality (choose the
fewest columns necessary), stability (choose a key that seldom
changes), and simplicity/familiarity (choose a key that is both
simple and familiar to users).


From SQL Queries for Mere Mortals
by Michael Hernandez and John Viescas (page 38)

"Define a Simple Primary Key when you can because it's more efficient
and is much easier to use when establishing a table relationship.
Use a Composite Primary Key only when it's appropriate (for example,
to define and create a linking table)."


Designing Effective Database Systems
by Rebecca M. Riordan (page 33)

"Some people are under the impression that composite keys are somehow
incorrect, and that they must add an artificial identifier--either an
identity or autonumber field--to their tables to avoid them. Nothing
could be further from the truth. Artificial keys are often more
convenient, as we'll see, but composite keys are perfectly
acceptable."


Database Normalization Tips
by Luke Chung
President of FMS
December 2001, last updated March 2005

http://www.fmsinc.com/tpapers/genaccess/databasenorm.html

In general, a key field should have these characteristics:

Should be One Field
It is possible to define multiple fields as the key fields of a
table, but a single field is preferable. First, if multiple fields
are necessary to define uniqueness, it takes up more space to store
the key than a single numeric field. Second, additional indexes on
the table also have to use the combination of the key fields which
takes up more space than if it were a single field. Finally,
identifying records in the table requires grabbing a combination of
fields. Far better to have a CustomerID number than a combination of
other fields to define a customer.


So, perhaps the author of the Utter Access article was a bit strong
in his language, but his recommendation is supported by others. The
point that he was trying to emphasize was that it is easier to work
with a simple key versus working with a composite key. As always,
YMMV (Your Mileage May Vary).

Hope this helps.

Tom Wickerath, Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

Thank you Mr. Wickerath,

Your description "perhaps the author of the Utter Access article was a
bit strong in his language" is something I can agree with ;-)

The point he was making, was that composite primary keys are incorrect
for junction tables. I'm afraid I'm still convinced there's little or
no
support in database literature for that.

Here's a quote from one of my books on primary keys in general
(Database
Systems, C.J.Date, sixt edition, 1995, page 79)
"The primary key is a unique identifier for the table - that is, a
column or column combination with the property that, at any given time,
no two rows of the table contain the same value in that column or
column
combination".

 

[RoyVidar]

The assertion in the article (that it is *always* incorrect to have a
multiple field primary key for a junction table) is rubbish. If it
is a *pure* junction table (that is, the table's ONLY use is to
implement a Many-to-Many relationship), then the *preferred* way is
to make the primary key a combination of the two foreign keys
(especially if these foreign keys relate to autonumber primary keys
in the base tables). By adding a surrogate key (autonumber primary
key), you are simply maintaining an additional index for no reason.

That said, if the junction table participates in any *other*
relationships, it is useful to create a surrogate key and create a
unique index made up of the two foreign keys. Even so, it's not
mandatory (see Problem 3 below).

Let me discuss the author's three problems:

Problem 1a:
If you want to delete a record from your junction table, you'd have
to supply values for both fields, rather than a single value. So you
would have to do this:
Currentdb.Execute "DELETE FROM tblSudentClasses WHERE StudentID = " &
me.cboStudent & " AND ClassID = " & me.lstClasses.

instead of this:
Currentdb.Execute "DELETE FROM tblStudentClasses WHERE StudentClassID
= " & me.lstClasses

Perfectly true. But his assertion that it is faster and more
effiecient is just not so. It is, of course, a tad more difficult to
code, but in terms of execution, the the difference would be
negligible. In fact, since Access stores primary keys as a clustered
index, it may even be faster! You'd have to do a benchmark to test
it, but to a human difference will be indistinguishable.

Problem 1b:
"if you ever decide to allow a student to take a class multiple times
you have to change this code to include your new field of the PK
(like class date)"
This is true, but if it is ever possible that a student can take a
class multiple times, then you should have taken that into account in
your initial database design. And even if you didn't, and you had to
make this change, you'd still have to modify your table structure
because you'd want to store this date and the junction table would be
the logical place. And you would *still* want to maintain real-world
uniqueness in your junction table, which would require a unique index
on the three fields anyway.

Problem2:
"What if you wanted to select a record with a multi-field PK from a
combo box?
John Calculus
John Chemistry
Jane French
Jane Biology"

This case is very true, but does not apply to the Junction table.
Remember, we are talking about foreign keys that are related to
surrogate keys in the main tables. You would never have values as
above. This argument is pertinent to the notion of using multiple
field primary keys in general, but not the specific case of a
junction table.

Problem3:
"Now you have tblTests with TestID and want to create
tblStudentClassTests to show what each student got on each test for
each class."

This is the case above where the junction table is not a pure
junction table, ie one that participates in another relationship. In
this case, it IS preferred to create a surrogate key for the primary
key, but it is not MANDATORY. His contention is that you'd HAVE to
store values from both fields in the composite key in the related
table is just plain wrong.

Let's take the case above. You've created your database with
tblStudentClass as a junction table. Later, you are asked to modify
the database to store test scores. You create tblTest and realize
this is also a M:M relationship that requires its own junction table
(tblStudentClassTests). Rather than relating the table on the
primary key of tblStudentClass, you'd create an autonumber field and
add a Unique Index on it (call it StudentClassID). The you could
relate your table on this new field, rather than the primary key.
While we usually create relationships on primary key/foreign key, it
doesn't have to be so. Any field with a unique index will work.
(Note: an autonumber field with a unique index is also guaranteed to
be non-null).

Now I'm not saying I'd design it like that from scratch. If I *knew*
a junction table would participate in other relationships, I *would*
create a surrogate key. However, if I found myself in the situation
where I had to add a new relationship after the fact, I would NOT
have to resort to relating on multiple fields.

All of this is not to say I'm a fan of Natural Keys. I'm not. I use
Surrogate Keys in almost every table EXCEPT in pure junction tables.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

Mr. Carlson,

thank you very much for your clear and detailed post, to which I agree
completely!

One additional information, re problem 2. When working with a combo
based on a table with composite primary key, set the BoundColumn
property of the combo to 0.

The first thing to notice, is that when one select John Chemistry in
the
mentioned sample - then John Chemistry will remain selected. Why?

When the bound column property is set to 0, the listindex is what one
address when assigning or retrieving the combos value property, which
is
unique. So, the article is true that the combo "just wants to know what
record number you're talking about" ;-)

Then it should be just some smallish programming to assign and maintain
the correct combo selection.

 

[Tony Toews]

"A very tempting, but incorrect, Primary Key for tblJunction is a
composite one consisting of the two foreign key fields ID1 and ID2.
However, the Primary Key, as always, should be a single-field,
preferably AutoNumber."

Composite primary key incorrect???
A primary key should be single field???

This is a religious argument. <smile>

Tom Ellison argues quite effectively for composite primary keys.
However the Access UI needs to be tweaked to better support composite
primary keys.

Using the term incorrect would be a bit strong IMO. It's more a
matter of taste.

That said I only every use autonumber primary keys. I will frequently
use non duplicate multi-field indexes to restrict what is entered. Ie
only one person can register for a given course.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm