Re: database design.. question
Date: Thu, 16 May 2002 20:28:07 GMT
In article <3CE206C8.371C2770_at_elterix.pl>, lukasz_at_elterix.pl says...
> I have a little problem with designing my database. I have to collect
> some events with their parameters (time, date, location). On the other
> hand I have a table with employees...
> what is the best sollution to store information about people
> participationg in an event?
> best regards,
What type of events?
Simple answer is you have a many-to-many between Employee and Event. At the physical level, a relational DB will have three tables: Employee, Event and EmployeeEvent. Event hold data for each Event and EmployeeEvent links many Employees to many Event, and for each link you can record data about the relation, such as when it happened.
CREATE TABLE Employee(
EmployeeID int NOT NULL, FirstName varchar(50) NULL, LastName varchar(50) NULL,
CONSTRAINT PK1 PRIMARY KEY NONCLUSTERED (EmployeeID) )
CREATE TABLE EmployeeEvent(
EmployeeID int NOT NULL, EventCode char(4) NOT NULL, EmpEventDate smalldatetime NOT NULL,CONSTRAINT PK2 PRIMARY KEY NONCLUSTERED (EmployeeID, EventCode), CONSTRAINT RefEmployee1 FOREIGN KEY (EmployeeID) REFERENCES Employee(EmployeeID),
CONSTRAINT RefEvent2 FOREIGN KEY (EventCode) REFERENCES Event(EventCode)
CREATE TABLE Event(
EventCode char(4) NOT NULL,
EventName varchar(100) NULL,
CONSTRAINT PK3 PRIMARY KEY NONCLUSTERED (EventCode) )
CREATE INDEX Ref11 ON EmployeeEvent(EmployeeID) CREATE INDEX Ref32 ON EmployeeEvent(EventCode)
(Any opinions expressed are strictly mine only and not my employer's)
Paul Tiseo, Intermediate Systems Programmer Reply To: 123tiseo.paul_at_123mayo.edu (drop the numbers) Received on Thu May 16 2002 - 22:28:07 CEST