By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,572 Members | 963 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,572 IT Pros & Developers. It's quick & easy.

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

P: 1
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
Share this Question
Share on Google+
1 Reply

Expert Mod 10K+
P: 14,534
OperaID (Primary Key - Autonumber)

This table will contain a unique list of Operas

SingerID (Primary Key - Autonumber)

Each singer will only appear once in this table.

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

Each Production will be listed in this table.

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

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

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.

Mar 1 '07 #2

Post your reply

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