Re: Simple linking table question

From: --CELKO-- <71062.1056_at_compuserve.com>
Date: 21 Mar 2003 10:59:01 -0800
Message-ID: <c0d87ec0.0303211059.97663d4@posting.google.com>

A table has one and only one PRIMARY KEY!! And only one index can be clustered in a SQL Server table. Square brackets are proprietary and stop using that silly "tbl-" prefix, it is driving me nuts.

CREATE TABLE Attendence
(event_id INTEGER NOT NULL

        REFERENCES Events(event_id)
        ON DELETE CASCADE
        ON UPDATE CASCADE,
 person_id INTEGER NOT NULL
        REFERENCES Persons(person_id)
        ON DELETE CASCADE
        ON UPDATE CASCADE,

 PRIMARY KEY (event_id, person_id));

>> Next, "notes". The db is the back-end for an asp.net application.
The user
of this app may be a manager, who should be able to call up a client's details onto the screen, then add a "note" to this client, where this note
is essentially a memo to someone else in the organisation to do something in
relation to this client. <<

CREATE TABLE Notes
(event_id INTEGER NOT NULL,
 person_id INTEGER NOT NULL,
 note_nbr INTEGER NOT NULL,

 note VARCHAR(2000)DEFAULT '{{call event manager}}' NOT NULL,
 PRIMARY KEY (event_id, person_id, note_nbr),
 FOREIGN KEY (event_id, person_id)

 REFERENCES Attendence(event_id, person_id)  ON DELETE CASCADE
 ON UPDATE CASCADE); I assume that an attendee can have many notes.

>> a group of People is invited to a certain Event. The application
must track who has been invited, who has accepted the invitation, who actually turned up to the event etc. <<

That sounds like you need a group table on the side:

CREATE TABLE InvitationGroups
(group_name CHAR(15) NOT NULL,
 person_id INTEGER NOT NULL

        REFERENCES Persons(person_id)
        ON DELETE CASCADE
        ON UPDATE CASCADE,

 PRIMARY KEY (group_name, person_id));

Use this table to get all the names in a group for the Attendence table. Received on Fri Mar 21 2003 - 12:59:01 CST