Hi there,

This question might be a little off-topic because it isn't related to
DB2 - sorry about that.

I am reading the book about DBMS from Thomas Connolly to gain deeper
understanding about DBMS' and stuggle with one of the recommendations
of the logical design. Connolly "recommends" to replace M:N with two
1:N relations. Unfortunatelly, he didn't give a good reason for that
and it seems to work on DB2 9.7.4 though.

Is there a reason to avoid M:N relations (in general)?

Thanks in advance.

Kai

What I ment to say was: the "idea" of a table, that connects two other
tables, works for me without massive performance penalties on 9.7.4.


On 2011-08-04 17:55:50 +0200, Kai said:

On 2011-08-04 18:42, Kai wrote:
I'm not sure I follow, but I'm not familiar with Connolys book. Let's
say we have two relations STUDENTS and COURSES that have a N:M
relationship, that is a student may take many courses, and a course may
have many students.

ENROLL
STUDENTS : N ------ M : COURSES


When implemented in sql the relationship ENROLL usually becomes a new
relation (aka table). The relationship between ENROLL and STUDENTS is
typically 1:N and the same goes for COURSES and ENROLL. If I get it
right you seem to accept this above but question it below?


/Lennart

This is how I've done that in the past (and how I've learned it at
University). Connolly says, however, we should always replace M:N
relations with two 1:N relations, like this:

ENROL
STUDENTS : N ----- 1: REGISTRY : 1 ----- M : COURSES

Well, this is how it is implemented but, and that is what is wondering
me, Connolly says we should do this while creating the logical design.
For me, this makes it more difficult to read and to understand after a
while.

Let me rephrase my first question: is it bad practice to use M:N
relations in ERDs? If so, why?

Thanks in advance
Kai

On 2011-08-05 10:25, Kai wrote:
[...]
Ok, I see now that I probably misread your post. At the logical level I
see no reason to remove M:N relationships. IMHO doing so will only
clutter the logical view with unnecessary details.

/Lennart

I believe we have good reason though in real world we may just keep it for some special consideration or just by non-mature modeling practice.

as we're considering modeling, FK is very important and it is how tables become "related". so the argument is, with many-to-many, we lose the abilityof dbms enforced integrity by foreign keys. or say, a normalized model cannot represent many-to-many with foreign keys.

Interesing point. I've never seen it from that perspective!


On 2011-08-17 16:36:32 +0200, Yonghang Wang said:

CREATE TABLE Husbands
(husband_id INTEGER NOT NULL PRIMARY KEY,
…);

CREATE TABLE Wives
(wife_id INTEGER NOT NULL PRIMARY KEY,
…);

The reason for putting the relationship into a separate table is to
control cardinality (1:1, 1:m, n:m), degree (unary, binary, n-ary) and
optionality (optional or mandatory)

CREATE TABLE Marriages
(husband_id INTEGER NOT NULL UNIQUE
REFERENCES Husbands (husband_id)
ON DELETE CASCADE,
wife_id INTEGER NOT NULL UNIQUE
REFERENCES Wives(wife_id)
ON DELETE CASCADE,
<< Marriage attributes, like wedding date >>);

The NOT NULL and CASCADE make (husband, wife) compulsory, Try the rest
of the combinations of just uniqueness:

CREATE TABLE Polygamy
(husband_id INTEGER NOT NULL
REFERENCES Husbands (husband_id)
ON DELETE CASCADE,
wife_id INTEGER NOT NULL UNIQUE
REFERENCES Wives(wife_id)
ON DELETE CASCADE,
<< attributes >>);

CREATE TABLE Polyandry
(husband_id INTEGER NOT NULL UNIQUE
REFERENCES Husbands (husband_id)
ON DELETE CASCADE,
wife_id INTEGER NOT NULL
REFERENCES Wives(wife_id)
ON DELETE CASCADE,
<< attributes >>);

CREATE TABLE Dating_Couples
(husband_id INTEGER NOT NULL
REFERENCES Husbands (husband_id)
ON DELETE CASCADE,
wife_id INTEGER NOT NULL
REFERENCES Wives(wife_id)
ON DELETE CASCADE,
PRIMARY KEY (husband_id, wife_id),
<< attributes >>);

Now play with NULL|NOT NULL for optional|mandatory.