How to represent this in a table?

Question

I have a collection of data like so

Programme title |  Episode  |  Subtitle  |  Performers  |  Description

Initially I normalised this into two table like so

PROGRAMME

progid  |  progtitle  |  description

EPISODE

epid  |  progid  |  episode  |  subtitle  |  description

I'm thinking I'd like to represent the performers in another table though, but not sure how to represent it. From what I can tell the following relationships exist

• One to Many: A programme can have many performers
• One to Many: A performer could feature in many programmes

I'm not sure how I would represent this?

EDIT Ah I see so I'd actually have tables like this for example?

PERFORMER

performerid  |  performer

PROGRAMME

progid  |  progtitle  |  description

EPISODE

epid  |  progid  |  episode  |  subtitle  |  description

PROG_PERFORMER

progid  |  performerid

Answer 1

It's many-to-many. One performer can be in multiple programs, and one program can have multiple performers.

There's plenty of information on the net (and in textbooks) about setting up many-to-may relationships. One such resource is here:

http://www.tekstenuitleg.net/en/articles/software/database-design-tutorial/many-to-many.html

Really, though it should be

• A Program has a one-many relationship with episodes
• An episode has a many-many relationship with performers.

This is enough to create a query that will list all performer/show/episode relationships.

Answer 2

Also, performers can change between episodes

Answer 3

beside performer table you have to create a relation table

performer table

performerid | name | ...

relation table

performerid | progid