Re: Simple linking table question
Date: 21 Mar 2003 10:59:01 -0800
Message-ID: <c0d87ec0.0303211059.97663d4_at_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 - 19:59:01 CET