My last post proved quite popular so I am wondering if I can combine a post about IT and Star Trek.

Years ago I used to have lists of Star Trek episodes, which included such information like original air date, production number, episode title and brief description.

One thing that was hard to keep track of was how many episodes were written by a specific person. This is because episodes are written by multiple people. A column called writer would then need to contain multiple people, another option would be to have columns called writer1, writer2 etc. This wouldn’t help either as you wouldn’t know which column a specific writer had been saved in.

manytomanyThe relationship between writer and episode is known as a many to many relationship. An episode can have many writers and a writer can have written many episodes. To achieve this structure in a SQL database you will need three database tables as it is not possible to create a many to many join between two tables. The first table will contain all the episodes, the second table will contain all the writers, the third table known as a junction table, will contain the relationship between the two.

Let’s do an example so we can see how this would work. Gene Roddenberry creator of Star Trek wrote the pilot episode ‘The Cage’. So Gene would be added to the writers table with an id of 1 and The Cage would be added to the episode table with an id of 1. In the junction table, it has two columns episode and writer, so we would enter 1 and 1 into these columns.

Select * from Episode e
Join EpisodeWriter ew on e.id = ew.EpisodeId
Join Writer w on w.id = ew.WriterId

But if Gene Coon and Gene Roddenberry had writing credits on The Cage we would need to add Gene Coon to the writing table and add an extra entry to the episodewriter table.

I am going to do more posts based on this as I expand the database structure to include other information, I may go on to create stored procedures for bringing back certain information or I may use this as an example to talk about coding a user interface.

This post has already been read 921 times!

Simon Foster on GithubSimon Foster on LinkedinSimon Foster on Twitter
Simon Foster
Web Developer
I have worked in SysAdmin and IT Management but now work as a Web Developer. I love everything IT related and I am trying to learn as much as I can especially about DevOps. Why not follow me on twitter?

3 thoughts on “To boldly go where no SQL has gone before

  1. How about making this more generic: it’s been known for actors to also write and direct, so if you restricted yourself to a Writer table, you’ll end up duplicating data. Instead, consider a Credit table that holds details on the person, and an EpisodeCredit table that holds EpisodeId, CreditId and CreditType, where CreditType could be any of ‘writer’, ‘actor’, ‘director’, ‘producer’, etc.

    Then your query becomes:

    SELECT e.* FROM Episode AS e JOIN EpisodeCredit AS ec ON e.Id = ec.EpisodeId JOIN Credit AS c ON ec.CreditId = c.Id WHERE c.Name = ‘Gene Rodenberry’ AND ec.CreditType = ‘writer’

    Or, to find any episode that GR wrote or directed, change the last clause to:

    […] AND ec.CreditType IN (‘writer’, ‘director’)

    • Thanks for your comment, I was thinking about expanding the database structure in a similar way to how you describe in a future post. Many writers joined the staff and later became producers and there are loads of actors that later directed episodes.

Comments are closed.