CODEWITHSUNDEEP
mysqldatabase-design
Can Vural
Can Vural

Reputation: 2067

Database design for a tv series app

I'm developing a web application about TV shows. And i need help with the database design.

I made a list of what i need, but i can't figure out how to design it. I have the basic tables. Series, episodes, seasons etc. What i can't do is how to relate people with episodes/series. Here is my list:

There should be multiple people type. Actor/director/writer/guest etc.

I don't think creating seperate table for each type is a good idea. So there should be one people table. And i need to store the type somewhere.

A person may be in 1 or many series.

This can be done with people_serie table with foreign keys to people and series tables. But i need a way to relate a person to episodes too.

An actor may play 1 or many roles.

This can be done with person_role table.

This is where its getting complicating.

I hope i make it clear what the problem is.

Upvotes: 1

Views: 1595

Answers (1)

Zohar Peled
Zohar Peled

Reputation: 82474

Well, you're correct not to split the People table.

the first thing to do is add a Roles table, that will contain role id and role title (each column should be unique - you don't want 2 different ids for the Actor role...)

TblRoles
RoleId    RoleTitle
-------------------
1         Director
2         Writer
3         Actor

Then you add a PersonToSeries table, that will hold it's own id, the person's id and the series's id. This table will hold every person ever working on that series, being a regular staff member or a 1 episode guest. 

TblPersonToSeries
PTSId   PersonId   SeriesId  
---------------------------
1       1          1
2       3          8
3       4          7

The next table you will need is a PersonToEpisode table, that will hold the PersonToSeries id and the episode id, and the role id.
In this table you only need to keep integer ids so it's very light weight, and you will specify for each record in PersonToSeries the episodes it is relevant for.

TblPersonToEpisode
PTEPTSId     RoleId
-------------------
1            2 
2            3
3            1

When a person is usually the director of a series, but makes a guess appearance in an episode of that series, you can simply add 2 rows in PersonToEpisode for that PersonToEpisode with a different role id. (one for Actor and one for Director)

TblPersonToEpisode
PTEPTSId     RoleId
-------------------
13           1 
13           2

Upvotes: 1

Related Questions