Re: database design.. question

From: drop the numbers <123tiseo.paul_at_123mayo.edu>
Date: Thu, 16 May 2002 20:28:07 GMT
Message-ID: <MPG.174de0e9ff58166a98970a@news.easynews.com>

In article <3CE206C8.371C2770_at_elterix.pl>, lukasz_at_elterix.pl says...
> Hi,
> 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...
> Question:
> what is the best sollution to store information about people
> participationg in an event?
> best regards,
> Lukasz

        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 - 15:28:07 CDT