Many to Many Relations in Database

[WWGD]

I am pulling my hair trying to find a straight answer to this, after looking it up in
different books, websites:
Say we have a many-to-many relationship in a RDB (Relational DB). Is there a standard
way of creating a junction, bridge, etc. table? From what I know,. the bridge table will contain
the primary keys of both table. Will the bridge table contain _exactly_ these two fields, or does
this depend on the particulars of the databases involved?

I would also appreciate some help in this: once we have a junction table. Say we want to
conduct a query between the two tables that are in many-to-many relationships. How do we then
conduct a query, do we do it on the join of one of the many-many tables and the junction table?

Thanks.

 

[Svein]

Is there a standard
way of creating a junction, bridge, etc. table? From what I know,. the bridge table will contain
the primary keys of both table. Will the bridge table contain _exactly_ these two fields, or does
this depend on the particulars of the databases involved?

I would usually just use those two fields.

I would also appreciate some help in this: once we have a junction table. Say we want to
conduct a query between the two tables that are in many-to-many relationships. How do we then
conduct a query, do we do it on the join of one of the many-many tables and the junction table?

That depends. If your junction table just contain the primary keys, setting up a query would usually involve some knowledge outside the junction table. I usually set up a query using all tables and then specify what records I want from one table. The query would then return all the relevant records from the other table.

 

[jtbell]

In my database for my classical-music collection, I have a many-to-many relationship between "performer" and "performance", implemented with a junction table "performance_performer" that contains only the numeric IDs (which I use as primary keys) from both tables. A simple query for all performances by a specific performer might look like this:

select performance.description
from performance, performer, performance_performer
where performer.lastname = "Heifetz"
and performance_performer.performerid = performer.id
and performance.id = performance_performer.performanceid;

 

[Svein]

In my database for my classical-music collection, I have a many-to-many relationship between "performer" and "performance", implemented with a junction table "performance_performer" that contains only the numeric IDs (which I use as primary keys) from both tables.

I also have a music collection database. In my case I have a table named "Track" that links to several tables: Album, Composer and Performer. In addition, it contains the track number and the track title.

 

[jtbell]

In your case, "track" is a physical entity which also can be used to relate other entities. In my case, "performance_performer" is a purely conceptual entity that expresses a many-to-many relationship. A single performance can have more than one performer taking part in it (e.g. soloist(s) and conductor for a concerto), and a single performer can appear in many different performances (usually of different compositions).

 

[WWGD]

Thanks, all.

 

[WWGD]

I was told today that a bridge/junction table should include, together with both primary keys, any field that is functionally-dependent on those two keys. I think the argument is that not including it would violate some normalization; I think the third.