Simple linking table question

From: Jon Maz <jonmaz_at_surfeu.de.no.spam>
Date: Wed, 19 Mar 2003 19:53:00 +0100
Message-ID: <b5ae5m$hev$1@news.online.de>

Hi,

I'm used to making simple two-column linking tables, each column being the PK of another table, and the two columns together forming the CPK of the linking table (eg tblPeople_Events below). No problem there.

Now I want to make tableX, where tableX is a linking table that joins a single-PK table and a two-column-CPK linking table. How should this tableX look? Does it simply have three columns, and a three-column-CPK? Somehow that just looks odd to me (see tblPeople_Events_Notes below), but that's probably just my newbieness showing through...

First 2-column linking table:

        CREATE TABLE tblPeople_Events (
             EventID [int] NOT NULL PRIMARY KEY  CLUSTERED,
             PersonID [int] NOT NULL PRIMARY KEY  CLUSTERED,
         CONSTRAINT FOREIGN KEY EventID REFERENCES tblEvents[EventID]
         CONSTRAINT FOREIGN KEY PersonID REFERENCES tblPeople[PersonID]

"Normal" single PK table:

 CREATE TABLE tblNotes (

      NoteID [int] NOT NULL PRIMARY KEY

My "weird-looking" linking table to join the above two - can this possibly be correct???:

  CREATE TABLE tblPeople_Events_Notes (

          EventID [int] NOT NULL PRIMARY KEY CLUSTERED,
          PersonID [int] NOT NULL PRIMARY KEY CLUSTERED,
          NoteID [int]  NOT NULL PRIMARY KEY CLUSTERED,

  CONSTRAINT FOREIGN KEY EventID REFERENCES tblEvents[EventID]   CONSTRAINT FOREIGN KEY PersonID REFERENCES tblPeople[PersonID]   CONSTRAINT FOREIGN KEY NoteID REFERENCES tblNotes[NoteID]

TIA, JON PS I hope my DDL is correct, this is the first time I've put it into a posting... Received on Wed Mar 19 2003 - 12:53:00 CST