I want to make a database where I record the guests of each episodes of a tv show. I want to be able to do the following stastics:
What guest had the most appearences?
Which two (or three) guests appeared together the most?
How many unique guests did the show have each month/year?
What are the gender distribution between the guests?
What show had the highest number of guests?
What is the average number of guests on each episode of the show?
I also collect the description of each episode, so I want to find the most common topic for the episodes, which topic has which guests etc. The topics is retrieved from keywords in the episode description. An episode has everywhere from 2 to 10 topics.
...and possible more statistics I find that I want along the way.
However, how should I model it? Since each guest can have hundreds of apparences, I don't think an relational database where I map guest to episode is smart ->
| Guest Name | | Episode appeared in |
Since the second column can be filled with values.
Note that each episode can have ten to twenty guests as well.
Is a FACT table a better design, where I have the following:
|Date/Episode number | | Guest ID |
with an own guest info table?