Re: String Search

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: Ross Presser (rpresser_at_imtek.com)
Date: 02/15/05 

Date: Tue, 15 Feb 2005 17:25:50 -0500

On Mon, 14 Feb 2005 06:53:03 -0800, RichieRich wrote:

> So I should create a table with rows for every project number and group
> belonged to? Then I'll have a table with rows for users and groups they
> belong to. And my Groups and Project tables.
>

This is the standard way many-to-many relationships are modeled. It
provides the most complete representation and the fastest performance. Any
other design requires unnecessarily complicated coding.

CREATE TABLE Users (
  UserID varchar(25) NOT NULL PRIMARY KEY,
  firstName varchar(25) NULL,
  lastName varchar(25) NULL
)

CREATE TABLE Groups (
  GroupID varchar(20) NOT NULL PRIMARY KEY,
  GroupDescription varchar(80) NULL
)

CREATE TABLE UserGroup (
  UserID varchar(25) NOT NULL
    REFERENCES Users (UserID),
  GroupID varchar(20) NOT NULL
    REFERENCES Groups (GroupID),
  PRIMARY KEY (UserID, GroupID)
)

CREATE TABLE Projects (
  ProjID varchar(25) NOT NULL PRIMARY KEY,
  ProjDescription varchar(80),
  BlahOtherFields int
)

CREATE TABLE ProjectGroup (
  ProjID varchar(25) NOT NULL
    REFERENCES Projects (ProjID),
  GroupID varchar(20) NOT NULL
    REFERENCES Groups (GroupID),
  PRIMARY KEY (ProjID, GroupID)
)
GO

CREATE VIEW What_Groups_Am_I_In AS
  SELECT U.UserID, U.firstName, U.lastName,
    G.GroupID, G.GroupDescription
  FROM Users U
    INNER JOIN UserGroup UG ON U.UserID=UG.UserID
    INNER JOIN Groups G ON UG.GroupID = G.GroupID
GO

CREATE VIEW What_Projects_Am_I_In AS
  SELECT U.UserID, U.firstName, U.lastName,
    P.ProjID, P.ProjDescription
  FROM Users U
    INNER JOIN UserGroup UG ON U.UserID=UG.UserID
    INNER JOIN ProjectGroup PG ON UG.GroupID=PG.ProjID
    INNER JOIN Project P ON PG.ProjID=P.ProjID
GO

Relevant Pages