Re: Schema / SQL advise

>> Let's try some real DDL

CREATE TABLE Teams
(team_id INTEGER NOT NULL PRIMARY KEY,
 name CHAR(25) NOT NULL,
 description VARCHAR(250) NOT NULL,
 ... );

CREATE TABLE Personnel
(emp_id INTEGER NOT NULL PRIMARY KEY,
 name CHAR(25) NOT NULL,
 phone CHAR(10) NOT NULL,
 email VARCHAR(64) NOT NULL CHECK (email LIKE '%_at_%'),  ... );  

>>

 Rules:  

1. There can be any number of Teams
2. There can be any number of People
3. Each person can belong to any number of teams, and can either be a team member or a team leader.
4. A person can be a team leader on any or all of their teams, if they're not the leader they are a member.
5. Each team can have nay number of people, but only one team leader.
6. Teams and people can be added and deleted at any time.

<<

Good specs!!

>> Option A ... <<

There are no "fields"; tables have columnds and there a huge difference. Not thinking about it is probalby why you made the mistake in both of the design options you gave of putting a non-1NF comma delimited list of employee identifiers.

CREATE TABLE TeamAssignments
(team_id INTEGER NOT NULL

         REFERENCES Teams(team_id)
         ON DELETE CASCADE
         ON UPDATE CASCADE,
 emp_id INTEGER NOT NULL
         REFERENCES Personnel(emp_id)
         ON DELETE CASCADE
         ON UPDATE CASCADE,
 role CHAR(1) NOT NULL DEFAULT 'L'
              CHECK role IN ('L', 'M'), -- l= leader, m= member

        (SELECT * 
           FROM TeamAssignments
          WHERE role = 'L'
          GROUP BY team_id
         HAVING COUNT(role) > 1));

That constraint is legal SQL-92, but it is not implemented in all SQL products.

Your queries can be done easily with TeamAssignment. Received on Sun Jul 22 2001 - 01:32:07 CEST