3 way relation

[Eprend]

I am building a database for classical music CDs. A CD
can have many Works. A Work can be on several CDs. A
Soloist can perform different Works on different CDs. I
have 3 tables for CDs, Works and Soloists each with a
unique ID I have built relational tables to relate each
CD to each work using the IDs and another table to relate
each soloist to each work they perform.
How do I set up a table so I can define which Soloist is
on which Work on which CD? Do I have to somehow combine
the CD ID and the Work ID and relate the Soloist ID to
that? Any enlightenment will be appreciated.

 

[Allen Browne]

CD table (one record per CD).

Works table (one record for each work)

Soloist table (one record for each artist).

CdWorks table:
CdWorkID primary key
CdID foreign key to CD.CdId (what disk)
Track number indicating track of CD
WorkID foreign key to Works.WorkID

If a work only has one soloist, you could add a SoloistID field to the work
above. But if there could be multiple soloists features within a work, you
need another table with related records for the artists of the work:

CdWorksSoloist:
CdWorkID foreign key to CdWorks.CdWorksID
SoloistID foreign key to Soloist.SoloistID

There are other possible ways to analyze this data. For example, if you need
to be able to identify that two CDs contain the same *recording* of the same
work (i.e. by the same orchestra, on the same date), that would involve
further tables.

 

[Tim Ferguson]

How do I set up a table so I can define which Soloist is
on which Work on which CD?

Well, you should already have a table like FeaturedOn which lists which
Works are featured on which CD:

FeaturedOn
( CDNumber FK references CDs,
WorkID FK references Works,
Duration ' in seconds
StarRating ' from 0 to 5
etc.

Constraint pk (CDNumber, WorkID) primary key
)

so you can indeed create another table that identifies Performance in each
feature

Performances
( CDNumber,
WorkID,
SolistID FK references Soloists
Role ' baritone, tenor, piano etc
Outstanding ' true/ false

Constraint TakesPartIn (CDNumber, WorkID) FK references FeaturedOn,
Constraint pk (CDNumber, WorkID, SolistID) primary key
)


Hope that makes sense


Tim F

 

[Harvey Thompson]

I am building a database for classical music CDs. A CD
can have many Works. A Work can be on several CDs. A
Soloist can perform different Works on different CDs. I
have 3 tables for CDs, Works and Soloists each with a
unique ID I have built relational tables to relate each
CD to each work using the IDs and another table to relate
each soloist to each work they perform.
How do I set up a table so I can define which Soloist is
on which Work on which CD? Do I have to somehow combine
the CD ID and the Work ID and relate the Soloist ID to
that? Any enlightenment will be appreciated.

Eprend,

A Composer is associated with a Work. Soloists are associated with the
individual Performances of that Work, not the Work itself.
You need a Performance table.

Harvey Thompson
Bloomfield, Connecticut USA