Re: Help for advanced SQL query

From: Randall Roberts <randall_at_filer.org.nospam>
Date: Fri, 1 Jun 2001 18:16:02 -0700
Message-ID: <3b183e79_3@news.pcmagic.net>

Gene, Spencer, et. al.

In Gene's original post he said that there was a many to many relationship between Object and Map. This prompted Daniel to observer that a third table to resolve the many to many was required.

If you have a many to many relationship without the third table it is "unresolved" and it violates the relational model... (somehow... don't ask me to go re-read Dr. Codd). If you join Object to Map directly, not through the intersection, you will get a sort of cartesian product. If that's what you're looking for then I've missed the point, and you needn't read the rest of this.

Gene says that there is a third table related to by the cluster IDs. I thought the cluster_id might fit in here somehow, but it doesn't have the foreign keys back to the two tables. If you'll look at the my PowerPoint slides there is working SQL that demonstrates the implementation of a many to many where a song can be on more than one album, and (of course) an album can have more than one song.

Its at www.laoug.org in the speaker presentation library. The file is DBA_music.zip.

Best!

Randall

Spencer <spencerp_at_swbell.net> wrote in message news:B7ER6.154$Na6.10085_at_nnrp1.sbc.net...
> "gene" <usenet_at_smalltime.com> wrote in message
> news:9a95c0dd.0105311344.35eed2b7_at_posting.google.com...
> > "Randall Roberts" <randall_at_filer.org.nospam> wrote in message
 news:<3b13099e_1_at_news.pcmagic.net>...
> > > Yes, as Daniel said, you have to resolve a many to many relationship
 with a
> > > third table, which he and I call an intersection entity... but which
 I've
> > > heard called a "join table", "resolution table", and a couple of other
> > > things in my career.
> > >
> >
> > Thanks for your replies. There actually is a third table (alluded to
> > by the cluster_ids), though it seems different from the third table
> > that you're describing. Here is a simplified description of the
> > tables:
> >
> > table OBJECT
> > ------
> > object_id (PK)
> > name
> > cluster_id (FK)
> >
> > table MAP
> > -----
> > map_id (PK)
> > position
> > source
> > cluster_id (FK)
> >
> > table CLUSTER
> > -----
> > cluster_id (PK)
> > name
> >
> > If I were to create an additional table directly relating the maps and
> > the objects:
> >
> > table OBJECT_MAP
> > ------
> > map_id (FK)
> > object_id (FK)
> >
> > it would just be the same product of the two tables as I get by doing
> > the join, or am I missing something?

>

> i don't think you've missed anything. but just to clarify...

>

> let's assume that we are talking about equi-joins, that is, joins that
> use an equals comparison operator...

>

> the existing CLUSTER table will allow you to join rows from the MAP
> table to rows from OBJECT table where the values of the cluster_id
> column are equal.

>

> if you were to add the new OBJECT_MAP table, as you show, it would
> be possible to add a row to "relate" a row from the MAP table to a row
> from the OBJECT table, without requiring that the rows have a matching
> value for cluster_id.

>

> your OBJECT_MAP table is a classic example of a "third table" that
> resolves a many-to-many relationship. in some cases, there is add'l
> information carried on the relationship table.

>

> so it really depends... do you want to require that the rows from OBJECT
> and the rows from MAP have a matching cluster_id ? in that case, you
> don't even really have a need to reference the CLUSTER table, just do
> a join where OBJECT.cluster_id = MAP.cluster_id.

>

> if you need to relate OBJECT and MAP by some condition other than
> matching cluster_id, then you may need a table like OBJECT_MAP.

>

> let me provide another example for you to consider:

>

> an ORDER table, each row represents an order from a customer
> and a PRODUCT table, each row represents a specific product.

>

> a customer may request several products on a single ORDER
> and a single PRODUCT may be requested on several orders

>

> since there is a many-to-many relationship between the ORDER
> table and the PRODUCT table, we resolve this by adding a third
> table, typically named LINE_ITEM, which has a foreign key to the
> ORDER table and a foreign key to the PRODUCT table.

>

> the LINE_ITEM table can also carry other useful information, like
> the number of units ordered, the unit price, and so on.

>

> HTH

>
> Received on Fri Jun 01 2001 - 20:16:02 CDT