Re: String Search

From: --CELKO-- (jcelko212_at_earthlink.net)
Date: 02/14/05 

Date: 14 Feb 2005 11:13:34 -0800

Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are. Sample data is also a good idea, along with clear
specifications.

>> I have an identity table with a list of items "groups". <<

What is "an identity table" -- after writing six books on SQL, I have
never heard that term.

>> I also have a list of users and the groups they are assigned to. <<

List? SQL only has tables; LISP and some other languages use list
structures.

>> Then I have another table of "projects" and what groups they belong
to. <<

I can make some guesses based on that vague spec, but I have business
rules about group membership, project assignments, etc.

CREATE TABLE Users
(ssn CHAR(9) NOT NULL PRIMARY KEY,
 ..);

CREATE TABLE Projects
(proj_nbr INTEGER NOT NULL PRIMARY KEY,
 ...);

CREATE TABLE ProjectGroups
(grp_nbr INTEGER NOT NULL
   REFERENCES Groups(grp_nbr)
   ON UPDATE CASCADE
   ON DELETE CASCADE,
 proj_nbr INTEGER NOT NULL
   REFERENCES Projects(proj_nbr)
   ON UPDATE CASCADE
   ON DELETE CASCADE,
 PRIMARY KEY (grp_nbr, proj_nbr),
 ...);

CREATE TABLE GroupAssignments
(ssn CHAR(9) NOT NULL
   REFERENCES Users(ssn)
   ON UPDATE CASCADE
   ON DELETE CASCADE,
 grp_nbr INTEGERE NOT NULL
   REFERENCES Groups(grp_nbr)
   ON UPDATE CASCADE
   ON DELETE CASCADE,
 PRIMARY KEY (ssn, grp_nbr)
 ..);

This makes all kinds of assumptions.

>> I used a column instead of another table that tied the projects to
the groups (I thought it would be less messy). So now the user needs
to query the projects table and only return those projects that the
user belongs to. <<

Why did you think that a project was an attribute (column) and not an
entity (table) in your data model? I am serious about that question.
What is your "native programming language"? Have you worked with a MV
system before this? Etc. People usually have some reasoning behind a
mistake like this. It helps me teach if I know that reasoning.

Relevant Pages

  • Re: Design for Category Feature
    ... surname VARCHAR NOT NULL, ... REFERENCES ContactCategories ... ON UPDATE CASCADE ... PRIMARY KEY ); ...
    (comp.databases)
  • Re: object model to table design mapping problem
    ... the INCITS H2 Database Standards Committee(nee ANSI X3H2 ... NOT NULL PRIMARY KEY, ... REFERENCES Courses, ... ON UPDATE CASCADE ...
    (microsoft.public.sqlserver.programming)
  • Re: Selecting several types
    ... Please post DDL, so that people do not have to guess what the keys, ... NOT NULL PRIMARY KEY, --let's use GTIN ... ON UPDATE CASCADE, ... REFERENCES ItemTypes ...
    (comp.databases.ms-sqlserver)
  • Re: Cascading update on primary key
    ... primary key depends on one of the 2 secondary tables. ... (mom CHAR(5) ... REFERENCES Mothers ... ON UPDATE CASCADE, ...
    (microsoft.public.sqlserver.programming)
  • Re: Deriving unique rows from historical data
    ... ON UPDATE CASCADE, ... REFERENCES Locations ... start_time DATETIME NOT NULL, ... Google how to code for this schema. ...
    (comp.databases.ms-sqlserver)