wrote in message
...
On Jan 25, 9:12 am, Jamie Collins wrote:
(quote)
What part of simpler don't you understand :-). Only one expression in
the ON is simpler. Needing less indexes is simpler. Not having to
look for your multi-key fields is easier, although your point that
Relationships can handle that is valid. If the AutoNumber key has a
one-to-one relationship with the multi-key fields then it's fine to
use it. There's no down side that I can see. I also like to rely on
coding to detect inconsistent data rather than on error trapping, so I
have to check the multi-key values anyway before adding a new record.
I think that your idea about enforcing constraints at both the table
level and in code is an excellent idea. The OP wanted to know what
people did and why. I still don't see any reason put forward for me
to change to a multi-field key. Are totals queries easier when multi-
field keys are used? BTW, "reduced the amount of denormalization"
works just as well. Real databases experience denormalizing
influences.

(end quote)

Simplicity is in the eye of the beholder.

I think it's simpler to rely on constraints enforced by the DBMS to prevent
duplicate entries
than it is to write code to accomplish the same thing.

When you want to delete an entry form a junction table, you almost always
know the two FKs that uniquely determine the entry to be deleted. You
almost never know the value of the superflous surrogate key. So it's simple
to use the two FK's as the criterion for deletion than it is to look up the
ID field, and then use that as the basis for deletion.

The above comment depends entirely on how you go about organizing you code.
I like to keep my code simple. At least "simple" in my own eyes.

The use of multi-key fields in star schemas doesn't make individual queries
any simpler. But it expands by orders of magnitude the number of different
combinations that can be used as selection criteria when computing totals or
other aggregates. This makes the entire system simpler, on a lerger scale.

I realize that star schema discussions may be out of place in MS access
newsgroups. The same is true in c.d.t. There is almost nothing of
theoretical interest in star schemas.