Re: Separate PK in Jxn Tbl?

Marshall <marshall.spight_at_gmail.com> wrote in news:24e63bab-1720-4762-87a2-e1bcc76c130b_at_y5g2000hsf.googlegroups.com :

> Since natural keys are data that needs to be managed, this
> is an unsurprising claim. I don't throw out the data I'm supposed
> to be managing either. Also consider this thread is about junction
> tables. Imagine what you would have left if you threw out the
> natural key of a junction table: nothing! Just the capriciously
> introduced surrogate key in a table by its lonesome self.
>
> Perhaps I've just stumbled into a sanity check for table
> design: every projection of a table should have a meaningful
> predicate. (Do I hear JOG's ears pricking up?) In a junction
> table in which a surrogate key has been introduced, what's
> the meaning of the projection of the table over the s.k. column?
> Oops!

Junction tables that:

1. include nothing but the foreign keys of the tables they are joining,

AND 2. have no child tables

need not surrogate key.

Since I use surrogate keys in most of my schemas, most junction tables are two-columns, and those two columns are the PK.

Once I did have a schema where there was a child table to the junction table, and in that case, I wish I'd had a surrogate key (it was a pure natural key design, actually, and the worst project I've ever worked on; no one to blame but myself, as I designed the schema from scratch), as it made it awfully difficult to work with that table that was a child of the junction table.

-- 
David W. Fenton                  http://www.dfenton.com/ 
usenet at dfenton dot com    http://www.dfenton.com/DFA/