Re: Group Membership - One-to-Many Dilemma

From: --CELKO-- <71062.1056_at_compuserve.com>
Date: 20 Dec 2002 13:31:17 -0800
Message-ID: <c0d87ec0.0212201331.3fb92725@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 - 15:31:17 CST