Re: Separate PK in Jxn Tbl?

"JOG" <jog_at_cs.nott.ac.uk> wrote in message news:2b0cf2d3-8b8e-4dc5-9255-3db75df415c4_at_d4g2000prg.googlegroups.com... On Jan 24, 7:03 am, "Brian Selzer" <br..._at_selzer-software.com> wrote: (quote)
An actual example I experience springs to mind - I have witnessed a database where student projects were recorded via a schema of Project Partners:{id:autonumber, RoleA:person, RoleB:person}, with PK(id). None of the partnerships were aware of any "id" in the real world, and simply submitted their partnership choices on paper to admin. A clerical error resulted in 2/3 of the data being entered twice, which left a lot of people flapping about the number of markers required until the error was found. If the schema had used the natural {RoleA, RoleB} key there would have been no issue.

But then for all I know, MS Access might allow duplicates anyhow.... (/quote)

I have begun to mess around with MS Access, as a retirement hobby, after working professionally with some SQL DBMS products.

AFAIK, MS Access enforces the no duplicates rule, and the no missing data rule, for every PK that's declared. There are other ways to express these constraints, but the easiest way to get them is to declare a PK.

For that reason, I prefer not to create a new ID with an autonumber for any junction table. Tony have have his reasons for going the other way. Until I know what they are, I remain unpersuaded.

The fact tables in a star schema have the same property as a junction table, except that the number of FKs that are components of the PK may be larger than 2, and generally is larger.

I've just started messing with star schemas in MS Access. It's too soon for me to offer even a guess as to whether this is a smart idea or a stupid idea. All I know is that it will provide some cheap amusement for my retirement. Received on Thu Jan 24 2008 - 19:43:21 CET