mysql - Database design for a tv series app -

i'm developing web application tv shows. , need database design.

i made list of need, can't figure out how design it. have basic tables. series, episodes, seasons etc. can't how relate people episodes/series. here list:

there should multiple people type. actor/director/writer/guest etc.

i don't think creating seperate table each type idea. there should 1 people table. , need store type somewhere.

a person may in 1 or many series.

this can done people_serie table foreign keys people , series tables. need way relate person episodes too.

an actor may play 1 or many roles.

this can done person_role table.

this getting complicating.

• a role may in 1 or many episodes in serie.
• a person may belong more 1 type in serie. ex: actor , director

i hope make clear problem is.

well, you're correct not split people table.

the first thing add roles table, contain role id , role title (each column should unique - don't want 2 different ids actor role...)

tblroles roleid    roletitle ------------------- 1         director 2         writer 3         actor 

then add persontoseries table, hold it's own id, person's id , series's id. table hold every person ever working on series, being regular staff member or 1 episode guest.

tblpersontoseries ptsid   personid   seriesid   --------------------------- 1       1          1 2       3          8 3       4          7 

the next table need persontoepisode table, hold persontoseries id , episode id, , role id.
in table need keep integer ids it's light weight, , specify each record in persontoseries episodes relevant for.

tblpersontoepisode pteptsid     roleid ------------------- 1            2  2            3 3            1 

when person director of series, makes guess appearance in episode of series, can add 2 rows in persontoepisode persontoepisode different role id. (one actor , 1 director)

tblpersontoepisode pteptsid     roleid ------------------- 13           1  13           2