Re: Help for advanced SQL query

"Randall Roberts" <randall_at_filer.org.nospam> wrote in message news:3b183e79_3_at_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.

daniel is correct.

> 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).

third normal form: each attribute is dependent on the key, the whole key, and nothing but the key, so help me Codd.

i think that the "violation" is actually of one of James Martin's ten (twelve? fifteen?) rules for relational database. wow, that spins the clock back a few years...

> If you join Object to Map directly, not through
> the intersection, you will get a sort of cartesian product.

using an equijoin on the cluster_id columns may limit the result set to something smaller than a full blown (n x n) cartesian product. it is not at all clear to me what a row in the "Object" table represents, what a row in the "Map" table represents, or what either of these have to do with a row in the "Cluster" table.

but the term "cartesian" is derived from the name of the mathematician Rene Descartes, who was a student of the famous Dehors. i've heard from some who were confused about who was a student of whom, and they get it backwards, putting Descart before Dehors.

> If that's what
> you're looking for then I've missed the point, and you needn't read the
 rest
> of this.

after my last comment, i suppose you needn't really read the rest of my comments either.

>
> 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.

exactly. the identifiers (keys) from the two tables should both be stored as part of the row of the third table. in some cases, there could be a unique constraint on the concatenated keys of the two tables. it depends on the model, the particular problem.

> 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.
>

thanks !

> 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 Sat Jun 02 2001 - 00:10:34 CDT