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

The PROG_PERFORMER table should be EPISODE_PERFORMER. If the performers change per episode, and you change the PROG_PERFORMER table, that will change performers for all episodes — TimC, Jan 21 '11 at 09:32

score 2 · Answer 1 · answered Jan 20 '11 at 16:41

2

Also, performers can change between episodes

answered Jan 20 '11 at 16:41

TimC

1,051
9
16

Good catch. +1. I thought of that but after I'd posted, but you posted it before I could add it to my answer. – David Jan 20 '11 at 16:43
then create your relation table with `performerid` and `epid` – lweller Jan 20 '11 at 16:44

David · Accepted Answer · 2011-01-20T16:45:51.637

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.

score 1 · Answer 3 · answered Jan 20 '11 at 16:41

1

beside performer table you have to create a relation table

performer table

performerid | name | ...

relation table

performerid | progid

answered Jan 20 '11 at 16:41

lweller

11,077
3
34
38

How to represent this in a table?

3 Answers3