471,306 Members | 853 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,306 software developers and data experts.

Can't get many-to-many relationship to work

I don't know how to ask this question but I'll try:

I'm making a database (Access 2003) for an Opera buff. I have tables related like:

each Opera has many Productions (Madame Butterfly has an SF Opera production)
each Opera has many Roles (Madame Butterfly has Cio-Cio-San)
each Role has many Persons (Cio-Cio-San is played by Patricia Racette)

But the Person playing the Role depends on the Production. So, just like the program you get at the opera, I have a table Cast. Cast has foreign keys from Opera, Role, and Person.

I can't get my Cast queries to behave. I'm not surprised, because Cast is stuck in the middle and Opera is loose at the ends:

Opera > Production > Cast < Role < Opera

Not a very technical description but the relationship doesn't feel right. Cast is sort of fixing the many-to-many relationship between Production and Role (each Production has many Roles and each Role appears in many Productions). But having the same Opera table on the one side of both Production and Role I think messes things up.

If you understand what I'm trying to do and see a better way to do it, please let me know. Thanks!
Mar 1 '07 #1
1 2619
MMcCarthy
14,534 Expert Mod 8TB
tblOpera
OperaID (Primary Key - Autonumber)
OperaName

This table will contain a unique list of Operas

tblSinger
SingerID (Primary Key - Autonumber)
SingerName

Each singer will only appear once in this table.

tblProduction
ProductionID (Primary Key - Autonumber)
ProductionName
ProductionDate
OperaID (Foreign Key referencing the Primary key of tblOpera)

Each Production will be listed in this table.

tblRole
RoleID (Primary Key - Autonumber)
RoleName
OperaID (Foreign Key referencing the Primary key of tblOpera)

Each role will be listed in this table and reference the opera it's in.

tblCast
CastID (Primary Key - Autonumber)
SingerID (Foreign Key referencing the Primary key of tblSinger)
RoleID (Foreign Key referencing the Primary key of tblRole)
ProductionID (Foreign Key referencing the Primary key of tblProduction)

Now the cast consists of a singer, the role and the production.


Mary
Mar 1 '07 #2

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

10 posts views Thread by GluedToTheScreen | last post: by
1 post views Thread by Michel | last post: by
3 posts views Thread by Charley | last post: by
5 posts views Thread by The Biscuit Eater | last post: by
2 posts views Thread by I Report, You Decide | last post: by
4 posts views Thread by berehneh | last post: by
7 posts views Thread by lawrence | last post: by
2 posts views Thread by live | last post: by
reply views Thread by rosydwin | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.