Re: Group Membership - One-to-Many Dilemma
Date: 20 Dec 2002 13:31:17 -0800
Message-ID: <c0d87ec0.0212201331.3fb92725_at_posting.google.com>
>> I have Staff, Teams and Roles. A team is made up of staff members
in different roles... Some roles are predefined, and every team must
have these roles filled (eg: every team must have a manager and a
supervisor). Other roles are user-defined and can be added to a team
as required (eg: clerk).<<
The first attempt would be:
CREATE TABLE Teams
(team_id CHAR(10) NOT NULL,
role CHAR(10) NOT NULL,
staff_id INTEGER NOT NULL
REFERENCES Staff(staff_id) ON UPDATE CASCADE ON DELETE CASCADE,
PRIMARY KEY(team_id, role));
If you had full SQL-92 constraints:
CREATE TABLE Teams
(team_id CHAR(10) NOT NULL,
role CHAR(10) NOT NULL,
staff_id INTEGER NOT NULL
REFERENCES Staff(staff_id) ON UPDATE CASCADE ON DELETE CASCADE,
PRIMARY KEY(team_id, role),
CHECK (2 = ALL(SELECT COUNT(*)
FROM Teams WHERE role IN ('manager', 'supervisor') GROUP BY team_id)));
But if you don't have this, we could define a team as having two required attributes:
CREATE TABLE Teams
(team_id CHAR(10) NOT NULL PRIMARY KEY,
manager INTEGER NOT NULL
REFERENCES Staff(staff_id) ON UPDATE CASCADE ON DELETE CASCADE, supervisor INTEGER NOT NULL REFERENCES Staff(staff_id) ON UPDATE CASCADE ON DELETE CASCADE,
CHECK (manager <> supervisor));
Then team members are another table.
CREATE TABLE TeamsMembers
(team_id CHAR(10) NOT NULL
REFERENCES Teams(team_id) ON UPDATE CASCADE ON DELETE CASCADE, role INTEGER NOT NULL REFERENCES Staff(staff_id) ON UPDATE CASCADE ON DELETE CASCADE,
PRIMARY KEY (team_id, role));
But this will allow a manager or supervisor to play another role in the team, and you might not like the way thigns will cascade when someone leaves the company. Received on Fri Dec 20 2002 - 22:31:17 CET