Database design for a tv series app

@Kobayakawa You’re right to have one table for people. People should be related to individual episodes, not series. If you need to find the cast for a series, then just find the cast for each episode in that series.

In terms of relating people, you could have a table for cast members, and then another table for crew members (such as director, producer etc). Both would be pivot tables with foreign keys on person and episode.

@martinbean What about people types? How to store them? How to query with types?

@Kobayakawa You wouldn’t store “types”. Their role would be stored in the pivot table when you associate a person with an episode, either as a cast or crew member.

Consider two tables:

• episode_cast
• episode_crew

Both tables would have an episode_id and a person_id column. The episode_cast could have a role column (designating the role the person played in the episode). The episode_crew table could have a similar column to store the role they fulfilled, i.e. director, producer, writer etc.

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. many popular movie apps like Cinema APK are using it 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.

Isn't this already done: IMDB

@KOBAYAKAWA - You will want a person to have one to many relationship as a single person could have one or as many as all people types associated to them.

To design a database for a TV series app considering the relationships between people (actors, directors, writers, etc.) and episodes/series, you can follow these steps:

1. Create a People Table: This table will store information about individuals involved in TV shows, such as actors, directors, writers, etc. Include columns for the person's name, type (actor, director, etc.), and any other relevant information.

   CREATE TABLE People (
       person_id INT PRIMARY KEY AUTO_INCREMENT,
       name VARCHAR(255),
       type VARCHAR(50) -- actor, director, writer, etc.
       -- Add other relevant columns
   );
   

2. Create a Series Table: This table will store information about TV series.

   CREATE TABLE Series (
       series_id INT PRIMARY KEY AUTO_INCREMENT,
       title VARCHAR(255),
       -- Add other relevant columns
   );
   

3. Create an Episodes Table: This table will store information about episodes within TV series.

   CREATE TABLE Episodes (
       episode_id INT PRIMARY KEY AUTO_INCREMENT,
       series_id INT,
       title VARCHAR(255),
       season_number INT,
       episode_number INT,
       -- Add other relevant columns
       FOREIGN KEY (series_id) REFERENCES Series(series_id)
   );
   

4. Create a Roles Table: This table will store information about roles within episodes, linking people to specific roles they play in episodes.

   CREATE TABLE Roles (
       role_id INT PRIMARY KEY AUTO_INCREMENT,
       episode_id INT,
       person_id INT,
       character_name VARCHAR(255),
       -- Add other relevant columns
       FOREIGN KEY (episode_id) REFERENCES Episodes(episode_id),
       FOREIGN KEY (person_id) REFERENCES People(person_id)
   );
   

   This table will allow a person to be associated with multiple roles across different episodes and series.

5. Create a People_Series Table: This table will establish the relationship between people and TV series, indicating their involvement in a particular series.

   CREATE TABLE People_Series (
       person_id INT,
       series_id INT,
       -- Add other relevant columns (e.g., role type)
       PRIMARY KEY (person_id, series_id),
       FOREIGN KEY (person_id) REFERENCES People(person_id),
       FOREIGN KEY (series_id) REFERENCES Series(series_id)
   );
   

   This table will allow a person to be associated with multiple series in various capacities (e.g., actor in one series, director in another).

With this database design, you can capture the relationships between people, episodes, and series effectively. The Roles table allows for the association of people with specific roles in individual episodes, while the People_Series table tracks their involvement across entire series.