Database Design: American Horror Story - Amber Callan
portfolio_page-template-default,single,single-portfolio_page,postid-421,qode-quick-links-1.0,ajax_fade,page_not_loaded,,vertical_menu_enabled,qode-title-hidden,side_area_uncovered_from_content,qode-theme-ver-11.1,qode-theme-bridge,wpb-js-composer js-comp-ver-5.1.1,vc_responsive

For my Computing and Information Systems class, we dealt extensively with Microsoft Access databases and Microsoft Excel spreadsheets. In order to display my proficiency with databases, I created a database for my favorite TV show, American Horror Story. This serves as a sample database with relationships, queries and database set-up. Ideally this would be created to be in the back-end of an interface where users could call on the information in the database and receive answers to all of their American Horror Story questions.


About the Database

Our database is about “American Horror Story”, the popular TV show that airs on FX which is currently in its fourth season. Not only is it our favorite TV show, but the show has been critically acclaimed which makes it particularly popular. The actors, especially Jessica Lange, have received Emmy’s, Golden Globe Awards and Screen Actors Guild Awards for their performances. The series currently holds high ratings for the FX network and the first season, entitled “Murder House”, was named the biggest new cable series of 2011.

American Horror Story is an anthology, meaning each season contains the same actors who portray different characters in different settings with a unique storyline. For example, Jessica Lange played a nun in Season Two but played a headmistress of a witch school in Season Three. Because of the complexity in these normal TV show qualities, American Horror Story sometimes needs another level of explanation to new fans.

While this element of the show is particularly unique, a TV show also has a typical set of characteristics that differ from season to season and episode to episode. The directors, writers, characters and viewership changes with every episode and season. Therefore, we have two areas of focus within the one database which will provide interesting relationships.


About the Database Audience

This database will be used by a diverse audience. First, it can be used by long-time fans of the show who would like more information on AHS. There are many relationships that can be uncovered through our database that they could not find normally. For example, seeing the episode count per character or looking at a guest star database will be unique to our database.

Next, our database can be used by new fans to learn more information about how the show functions as an anthology. Because each season changes setting, characters and location, it can be confusing to a new fan. Therefore, they will be able to use our database to follow the actors through their roles each season and could use it for more information on the characters, setting, seasons, etc.

Lastly, it could be used by individuals researching the more statistical side of a TV show in terms of ratings and the number of viewers. Our database will not only contain the “fun” side of American Horror Story, but will include information that journalists or statisticians may want.

Ultimately, we see this database helping to create an elaborate American Horror Story site because it will provide extensive information on the show. Therefore, anyone from die-hard fans to curious newcomers will be able to have all of the show information on hand.

About the Database Contents

Our database will contain the following:

  • A Character Table (Character ID, Character name, actor ID, occupation, season number, character type )
  • An Episode Table (Episode ID, season number, episode number, episode title, air date, number of viewers)
  • A Season Table (Season number, season name, location, time period)
  • An Actor Table (Actor ID, Actor name)
  • A Writers Table (Writer ID, Writer Name)
  • A Directors Table (Director ID, Director Name)
  • A Junction Table for Character to Episode
  • A Junction Table for Episode to Director
  • A Junction Table for Episode to Writer

Here’s part of our Character Table:


Each of these is needed because they interact to produce a TV show. Here we outline the many relationships that exist to show why these tables are needed.

  1. Characters and Episodes will be in a many-to-many relationship because each character can be in multiple episodes and each episode can contain multiple characters.
  2. Characters and Seasons will be in a many-to-many relationship because in American Horror Story each character is typically in one season yet a few cross-over to multiple seasons and one season can contain multiple characters.
  3. Characters and Actors will be in a one-to-many relationship because a character has to be played by one actor, but one actor can play many characters in American Horror Story.
  4. Episodes and Seasons will be in a one-to-many relationship because each season can have multiple episodes, but each episode will only belong to one season.
  5. Characters to Episodes will be in a junction table as well as Characters and Seasons in order to establish a many to many relationship
  6. Episodes and Directors will be in a many-to-many relationship because each episode can be directed by multiple directors and each director can direct multiple episode.
  7. Episodes and Writers will be in a many-to-many relationship because each episode can be written by multiple writers and each writer can write multiple episode.

Here are the relationships we are creating:



ahsdb2About the Queries

Our database needs plenty of queries to answer important questions. Following is a list of the current queries and why our audience might find them useful.

  1. Crosstab Query – Season Viewership – Season viewership is always interesting for those who want to see the statistical side of a TV show. Therefore, our crosstab query contains the total number of viewers for each season followed by the viewership per episode.
  2. Unmatched Query – Episodes Without Matching Characters – Because each episode contains many characters, it would be easy to input an episode and leave the characters out due to it taking too much time. This query can help us locate which episodes need a character input and fix this mistake that we foresee occurring.
  3. Find Duplicate Query – Find duplicates for actors – Because the actors stay consistent, but the characters change, it might be easy for someone to input another actor to correspond with a new character. Using this query, we can easily see which actors are duplicated in our table and fix this mistake that we foresee occurring.
  4. How many characters are in each season? – Because characters are not consistent for each seasons, our audience will want to know how many are in each season. This will allow them to compare the amount of characters between seasons. In this query, we should find the season number and the count of characters.
  5. How many characters does one actor play? – Because every season the actors play different characters, our audience may want to see how many actors are involved in all four seasons and how many just played in one season.  This query would help because actors are not always in every season. In this query, we should find the count of characters grouped by the actor.
  6. How many episodes does each director direct? – Because usually the episodes that have the most action are directed by a certain actor while the deep, emotional, slow episodes are directed by another.  This could help the audience figure out what to expect for that particular episode depending on the directors specific style. In this query, we should find the episode count grouped by the director.
  7. How many episodes does each writer write? – Because the writers change almost every episode, our audience might find it interesting to look at how many episodes each writer writes. This might also be interesting because there are some episodes that are more prominently written by certain directors such as the season premieres and the season finales. This is because these episodes are commonly written by the shows creators.  In the query, we should be able to find out how many episodes each writer wrote.
  8. How many episodes is each character in? – Because not all actors that play characters are always a prominent role.  One character played by a certain actor could be the main character on season, while the next season’s actor character is only a guest star or just plays a supporting role.
  9. Season Viewership – Because as the seasons progress, the season viewership also increases.  From an audience’s point of view, one can see the popularity of the show and its cult following getting larger and larger.  It is now one of the most popular show’s the network has ever aired. It is also interesting to see more statistics on viewership, such as minimum amount of viewers each season. This expands on our crosstab query from earlier.
  10. Which characters does a specific actor play? – This is important because every season an actor may play a different character. This query allows our audience to type in an actors name and see all the different characters that they play.
  11. Which episodes were written by the creators? – There is a common occurrence that the creators were the ones to write the pilots and season premieres.  This is interesting because from an audience standpoint, with the writers also being the creators, it is cool to see what direction they are trying to take the show.


About Our Reports


Next, our database will require several reports to display information. Below is a list of our current reports and why our audience might find them useful.

  • Actors and Character Report – This report is unique for the American Horror Story show. Because the actors change characters every season, the most interesting part of the show is seeing which actors play which characters. In this report, we get a list of all the actors (sorted by last name) and the names of all the characters that they have played.
  • Characters and Actor Count Report – This report goes another step further from our Actors and Character Report because it counts up the number of characters each actor has played and highlights in red the actors that have been involved in every season so far.
  • Episodes, Directors and Writers – This report combines several aspects of each episode into one report. Sorted by the Episode ID, the report shows information about each episode and then gives their respective writer & director.





Here is our report on Characters & Actor Count. It highlights the actors that have played characters in all four seasons.



About Our Validation

Lastly, we incorporated several validation measures including input masks, validation rules and validation text to ensure our data stays consistent.

Input Masks – An input mask is applied for the time period for seasons (“YYYY”) and air date for episodes.

Validation Rule / Validation Text -Writer ID, Actor ID, Character ID, Episode ID, Episode Number, Season Number and Director ID all contain validation rules to make them a positive, numeric value with appropriate validation text. Air Date contains a validation rule to make all values after October 4th (when the show first aired on October 5th). Time period contains a validation rule to make the input a number between 1000-9999 to incorporate all four digit years. Character type contains a validation rule to make the input read as either “Recurring Cast” Or “Main Cast” Or “Guest Star”. Lastly, number of viewers contains a validation rule to make the input a positive number without formatting.

All of the input masks and validation rules contain appropriate text to help the user discover reasons behind the error.

The Future of this AHS Database

If this project was for a client, we definitely could’ve included more information that would require re-watching the series. We could have included more character statistics (character deaths, amount of flashbacks, etc.), the ages of the characters, the number of lines each character has, how the character enters and exits the season and the relationships between characters.

In terms of the design, we would definitely want to include a beautiful user interface and update our data to be recent (currently we’re two episodes behind). In addition to this, we’d need a more extensive database design.

We might want to include information on marketing, filming locations, run times, plot, reception, awards, DVD releases and more. We could have an awards table that includes big awards that a television show might receive, the award date, character-specific or actor-specific awards. We could also include connections between the seasons (hints from previous seasons) or season themes.

New tables we would want to include:

  • Awards (Name of award, specific reason, award description, etc.)
  • Flashbacks (Character who had the flashback, what time period it went to, which episode it was in, which season, etc.)
  • Character Relationships (Type of relationship, which characters it is between, etc.)
  • Plot (Themes, hints for the next season, etc.)

New fields we would want to include on existing tables:

  • Actors (Actor age, birthday, height and more)
  • Characters (Character age, nicknames and more)
  • Directors (Director age, years in the field)
  • Writers (Writer age, years in the field)
  • Episodes (Episode rating,
  • Seasons (Locations filmed)

We would also want to include many more queries on the new information and relating the old information. For example, we could include queries on:

  • The types of awards, when they were given and how many were given during each season
  • The average amount of characters each actor has played, the amount of starring roles they have held and duplicate occupations
  • The amount of characters each writer has written for, the number of actors that each director has directed
  • The episode that each character first played in and the last episode they played in

Lastly, we would add in more reports. We would obviously include reports about the information we were going to add in. For example, if we were to include an awards table, we would include a report for the awards.