Re: Schema / SQL advise

From: --CELKO-- <joe.celko_at_trilogy.com>
Date: Sat, 21 Jul 2001 23:32:07 GMT
Message-ID: <7e67a7b3.0106251118.2cd26769@posting.google.com>

>> 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 '%@%'),  ... );  

>>

 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

 CONSTRAINT Teams_have_one_leader
 CHECK (NOT EXISTS

        (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 Sat Jul 21 2001 - 18:32:07 CDT