0

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?

  • 1
    If the only requirement is to gather how many appearances a certain guest has on a show, then all you would really need is a single table with the guestID in a column and the count of appearances in another column. A second table to record the guest information would also be wise. My point is, you are implying you want more than a basic count by guests but you only ask for 'statistics'. If you want a recommendation for a database design, please expand with specifics what you would like for those statistics. – Shooter McGavin Aug 25 '16 at 18:29
  • Thank you for you answer - I've now updated with examples on what I'm after. – bjornasm Aug 25 '16 at 18:50
  • 1
    Thanks for adding the detail to the question. Just curious, will this be for more than one show with episodes or are we doing this for just one show? – Shooter McGavin Aug 25 '16 at 19:17
  • 1
    could you give examples for what you mean by topics? can a single episode have multiple topics? i'm concerned that what topics there are and whether or not an episode is about a certain topic would be hard to objectively define. – Joe Borysko Aug 25 '16 at 20:16
  • I edited the original post, thank you. The topics is retrieved from keywords in the episode description. An episode has everywhere from 2 to 10 topic (keywords) – bjornasm Aug 26 '16 at 6:49
1

enter image description here

This is what I recommend for your requirements

2

I think given the requirements and that at some point you may want additional information that wasn't listed here, I would go with a fully normalized approach.
3 Tables: Guests, Episodes, Episode_Guests Then depending on if you want to do this for more than one show, another table for Shows(or series).
As Paparazzi mentioned, the Guests table should contain sex. The Episode table should contain a date. Also, if you are going to do this for multiple shows, the Episode table should also have a foreign key back to the Shows table.
The Episode_Guests table should record every instance of a Guest appearing on an Episode so all it would need is a foreign key relationship back to guests and another for Episode.

  • The FK back to Shows should be placed in Episodes. "This is an episode of that show." – TommCatt Aug 27 '16 at 0:36
1

It is a simple many to many table

episodeID 
guestID

The episode table should have a date

The guest table should have a sex

1

The following table structure is what I would use.

It covers all the information you're indicating you capture, and allows scalability if your shows/episodes grow, without having to make many modifications.

Guest
-GuestId (PK)
-Gender
-GuestName (Optional, as long as you have a way of identifying ID-Name)

Show
-ShowID (PK)
-ShowName

Episode
-EpisodeID (PK)
-ShowID
-EpisodeDate
-TopicType (Used for a generalization/grouping of topics)
-TopicDescription

Appearances
-AppearanceID (Optional PK if Guest appearance should be logged 2+ per episode)
-EpisodeID (PK)
-GuestID (PK)

Then you simply add records to each table individually, possibly creating constraints in the tables to not allow you to add an Appearance for a show, guest, or episode that doesn't yet exist.

  • 2
    ShowID shouldn't be in Appearances, since the show that an episode belongs to is defined in the Episode table. – Joe Borysko Aug 25 '16 at 19:37
  • 1
    That's a good point. Edited to reflect. – MguerraTorres Aug 25 '16 at 19:51
  • 1
    One more thing, I think there should be a separate Topic table with this design, since TopicDescription depends on TopicType. It also would allow standardizing the topics that can be chosen from. – Joe Borysko Aug 25 '16 at 20:13
  • 1
    That's a bit out of scope, in my opinion, as the OP has not stated how "in-depth" he/she would like to go with Topics (they only stated they want to track/report topics). Think of those two fields more as "Topic Type"/"Topic Subtype" than anything else. – MguerraTorres Aug 25 '16 at 20:16
  • 1
    One question - shouldn't/couldn't EpisodeID and GuestID in Appearances be FK and not PK, and AppearanceID could be a composite key, or? – bjornasm Aug 26 '16 at 11:47

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.