Search code examples
databasedatabase-normalizationdatabase-table

How to represent this in a table?

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
Solution

  • 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.