Re: Database design question

On Mon, 06 Nov 2000 17:24:22 GMT, Gary Benson <gary.benson_at_digitalmail.com> wrote:

>Basically, I have a database of technical papers, each with a variable
>number of authors. The authors are currently stored as a semicolon
>delimited text field.

This is called either a many-to-many relationship or an n:m relationship. (Same thing, different terminology.) Resolve n:m relationships kind of like this:

 CREATE TABLE Authors (

    AuthorID LONG DEFAULT (0),
    AuthorFullname TEXT(50) NOT NULL,
    CONSTRAINT PK PRIMARY KEY (AuthorID)  );

 CREATE TABLE Works (

    WorkID LONG DEFAULT (0),
    WorkTitle TEXT(50) NOT NULL,
    CONSTRAINT PK PRIMARY KEY (WorkID)
 );

 CREATE TABLE AuthorWorks (

    AuthorID LONG DEFAULT (0),
    WorkID LONG DEFAULT (0),
    CONSTRAINT PK PRIMARY KEY (AuthorID, WorkID),     CONSTRAINT AuthorsFK
      FOREIGN KEY (AuthorID) REFERENCES Authors (AuthorID),     CONSTRAINT WorksFK
      FOREIGN KEY (WorkID) REFERENCES Works (WorkID)  );

Does that make sense?  

-- 
Mike Sherrill
Information Management Systems