Re: Many to Many with a Twist

From: Joe Celko <71062.1056_at_compuserve.com>
Date: 2000/07/23
Message-ID: <8ldlnf$46a$1_at_nnrp1.deja.com>#1/1


>> Imagine three tables C, R and B. C has a primary key of CKEY, R is
RKEY and B is BKEY. Another table NEWS has a primary key of NEWSKEY. <<

It would really help if you would post DDL instead of a vague narative. Meaningful names are nice, too! I have the horrible feeling that those prefixed names c_key, b_key and r_key are all actually the same attribute under three different names based on the table in which they appear and not on a correct data model.

>> One model is to put an intersection table between NEWS and C,
NEWS and B, and NEWS and R. <<

Nope; look up JPNF in my book SQL FOR SMARTIES. This will give you Fifth Normal Form (5NF) problems.

>> But when I add more tables at the level of C, R and B then I will
need more intersection tables. Is there a more elegant solution? Please be specific about which would be the key and foreign key columns of any alternative solution. <<

Let's avoid NULLs by using a zero as the marker for "no match"

CREATE TABLE Cee
(c_key INTEGER NOT NULL DEFAULT 0

       PRIMARY KEY,
 ...);

CREATE TABLE Bee
(b_key INTEGER NOT NULL DEFAULT 0

       PRIMARY KEY,
 ...);

CREATE TABLE Ree
(r_key INTEGER NOT NULL DEFAULT 0

       PRIMARY KEY,
 ...);

CREATE TABLE News
(news_key INTEGER NOT NULL PRIMARY KEY,
 b_key INTEGER

       REFERENCES Ree(b_key)
       ON DELETE CASCADE
       ON UPDATE CASCADE,
 c_key INTEGER
       REFERENCES Ree(c_key)
       ON DELETE CASCADE
       ON UPDATE CASCADE,
 r_key INTEGER
       REFERENCES Ree(r_key)
       ON DELETE CASCADE
       ON UPDATE CASCADE,

 ...);

Without a better specification, I do not know if you can get rid of the newskey column and simply use PRIMARY KEY (b_key, c_key, r_key). It would be nice if it were unique.

--CELKO--
Joe Celko, SQL and Database Consultant
When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc) which can be cut and pasted into Query Analyzer is appreciated.

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Sun Jul 23 2000 - 00:00:00 CEST

Original text of this message