Re: Separate PK in Jxn Tbl?

<CDMAPoster_at_fortunejames.com> wrote in message news:a12c7757-aea7-4d11-bf7c-3bd4b7feb442_at_n20g2000hsh.googlegroups.com... On Jan 25, 9:12 am, Jamie Collins <jamiecoll..._at_xsmail.com> 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 multifield  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. Received on Sat Jan 26 2008 - 13:26:13 CET