Re: Separate PK in Jxn Tbl?

From: David W. Fenton <>
Date: 27 Jan 2008 20:43:27 GMT
Message-ID: <Xns9A32A005FF53Ff99a49ed1d0c49c5bbb2_at_64.209.0.89>

Marshall <> wrote in :

> 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         
usenet at dfenton dot com
Received on Sun Jan 27 2008 - 21:43:27 CET

Original text of this message