Re: Many to Many Relationships

From: --CELKO-- <71062.1056_at_compuserve.com>
Date: 4 Dec 2001 10:36:00 -0800
Message-ID: <c0d87ec0.0112041036.75c1d6e0_at_posting.google.com>

>> I'm designing a database, in A2K, and I understand that in relational databases many to many relationships can't be directly implemented and that you need a junction table. What I am more concerned with is why. <<

>> This is my scenario: I have tblCase and tblLawyer. <<

1. Where? You did not post any DDL, so we have only the names of these tables.
2. Never use those silly, redundant prefixes on names in SQL. It looks like 1950's FORTRAN. SQL is a logical language and things should be given names that tell the reader what it means in the data model and NOT how it is physically stored.

Is a "tblLawyer" the guy who represents a class of furniture when they are sued? Since you used a single noun, I know that you have at most only one such lawyer in your data model. Seriously, get a copy of the NCITS L8 Metadata Standards Committee rules for naming data elments.

Is this a better statement of your problem:

Lawyers is a table of the lawyers who represent the buyer, the seller, or the bank in each deal. Why wouldn't I be able to make three attributes in Deals, entitled seller_lawyer_id, buyer_lawyer_id, and lender_lawyer_id and have each of those refer to a specific record in the Lawyers table. <<

In fact that is the only way to do it. If you had posted DDL, it should have looked something like this:

CREATE TABLE Deals
(deal_nbr INTEGER NOT NULL PRIMARY KEY,
 seller_lawyer_id NOT NULL INTEGER REFERENCES Lawyers(lawyer_id),  buyer_lawyer_id NOT NULL INTEGER REFERENCES Lawyers(lawyer_id),  lender_lawyer_id NOT NULL INTEGER REFERENCES Lawyers(lawyer_id),  << need some more constraints?? -- you never told us>>);

I re-wrote your next partagraph -- you had "records" which do not exist in SQL (sequential file systems have records), you talked about one table "contains" another (not 1NF), "instances" is an OO term, etc. This is a matter of gettign the concepts right, not just the terminology. Let's try:

This would be a many to many relationship, each deal of Deals table references multiple rows from Lawyers table and each Lawyer in Lawyers table can exist in more than one row of the Deals table. I know how to implement this by using a junction table, but I am more concerned with the why. <<

Join-Projection Normal Form. Let's consider a real example, using the data you did not post either.

Deals
buyer seller lender

---

'Smith' 'Jones' 'Celko'
'Smith' 'Wilson' 'Holme'
'Nelson' 'Jones' 'Holme'

This table is a three-way relationship, but because many CASE tools allow only binary relationships it might have to be expressed in an E-R diagram as three binary relationships, which would generate CREATE TABLE statements leading to these tables:

BuyerLender
buyer lender

---

'Smith' 'Celko'
'Smith' 'Holme'
'Nelson' 'Holme'

SellerLender
seller lender

---

'Jones' 'Celko'
'Wilson' 'Holme'
'Jones' 'Holme'

BuyerSeller
buyer seller

---

'Smith' 'Jones'
'Smith' 'Wilson'
'Nelson' 'Jones'

The trouble is that when you try to assemble the original information by joining pairs of these three tables together, thus:

SELECT BS.buyer, SL.seller, BL.lender   FROM BuyerLender AS BL,

       SellerLender AS SL, 
       BuyerSeller AS BS
 WHERE BL.buyer = BS.buyer

   AND BL.lender = SL.lender
   AND SL.seller = BS.seller;

you will recreate all the valid rows in the original table, such as ('Smith', 'Jones', 'Celko'), but there will also be false rows, such as ('Smith', 'Jones', 'Holme'), which were not part of the original table. This is called a join-projection anomaly.

There are also strong JPNF and overstrong JPNF, which make use of JOIN dependencies (JD for short). Unfortunately, there is no systematic way to find a JPNF or 4NF schema, because the problem is known to be NP complete. Received on Tue Dec 04 2001 - 19:36:00 CET