Re: Simple linking table question
Date: Fri, 21 Mar 2003 12:35:48 +0100
Actually I'm not sure which model my situation fits into. The three entities I am dealing with here are people, events and notes - maybe I should explain a bit more what this is all about.
A person goes to an event (eg a seminar). This is kept track of in the db with a tblPeople, a tblEvents, and a relationship table tblPeople_Events. In general, there is a many:many relationship between People and Events.
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]
That takes care of the "real-world" aspect.
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. When that other person in the organisation logs in, the first thing they see on the screen is the memo/note from their manager. Hence I need a system to attach Notes to Clients, People and other entities.
One special case of this is when the entity is a "PersonEvent". This works as follows: 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. As I said, in general, there is a many:many relationship between People and Events. But the information concerning one particular invitation relates one particular person to one particular event (hence a "PersonEvent" entity).
Our hypothetical manager logs into the system, and sees that Mr.Smith has declined an invitation to Event XYZ. He calls Mr.Smith and finds out why he has declined. Then he attaches a note to this PersonEvent explaining why Mr.Smith is not coming. The next user should find this note both on Mr.Smith's Person Screen, and on Event XYZ's Event Screen.
So that's my 3-entity problem - how do you attach a Note to a PersonEvent? Hope the explanation makes some sense! Where does that leave my db design question?
Thanks for all the help,
JON Received on Fri Mar 21 2003 - 12:35:48 CET