Help needed with relationships--Concerts, Performers, and Repertoire

[geraldh]

Dear All,

I am just setting up a database for a concert presenter of classical, jazz, world music, dance shows.

We present about 70-80 concerts each season

We are in our 6th season, and I am trying to organize all the data from the beginning to present (about 500 concerts).

I am trying to set up the following tables:

1) Concerts (purpose: to create a snapshot of each concert's data)

2) Performers (main purpose: listing each performer's concert appearances and the repertoire he/she has played; secondary purpose: personal contact information)

3) Repertoire (main purpose: to display which repertoire has been performed when, and by whom, so that repertoire is not repeated too soon).

Now, the problem I having is how to build the Parent-Child Relationships.

One Concert--> many Performers and many pieces of Repertoire.

One Performer--> many different Concerts.

One piece of Repertoire--> could be performed by different Performers at different Concerts.

So all the tables could have a number of Many-to-Many relationships.

I've tried drawings, mindmaps, but I now I think I need some assistance. Is there an elegant solution to this mess?

Thank you very much...

yyztoronto416

[comment]

I believe it should be:

Concerts -< ConcertRepertoire >- Repertoire

and:

ConcertRepertoire -< Roles >- Performers

[geraldh]

I believe it should be:

Concerts -< ConcertRepertoire >- Repertoire

and:

ConcertRepertoire -< Roles >- Performers

Thank you for a quick response. I'm sorry not familiar with the conventions with symbols yet.

Are you suggesting that ConcertRepertoire is the Parent of Concerts and Repertoire?

And Roles is the Parent of Concert Repertoire and Performers?

Thank you

Edit: how is "Roles" different from "Performers"? Do you mean "pianist" as a "Roles" and "givenname surname" as a "Performer"?

[comment]

No, it's exactly the opposite: ConcertRepertoire is the child of both Concerts and Repertoire; IOW, it's a join table resolving a many-to-many relationship Concerts >-< Repertoire into two one-to-many relationships. Each record in the ConcertRepertoire represents a composition performed at a concert.

[geraldh]

Ah! Thank you.

I am familiar with the join table as a way to resolve this problem. I will try this out....

Thank you again

[geraldh]

Consultant,

I've been mindmapping your suggestion and now I see the reasoning behind your suggestion (I have to admit I was skeptical at first.).

Thank you very much.