Method To Create Database for Tv Shows

Question

This is my first question to stackoverflow so if i do something wrong please let me know i will fix it as soon as possible.

So i am trying to make a database for Tv Shows and i would like to know the best way and to make my current database more simple (normalization).

I would to be able to have the following structure or similar.

    Fringe  
        Season 1 
            Episodes 1 - 10(whatever there are)
        Season 2 
            Episodes 1 - 10(whatever there are)
        ... (so on)

    Burn Notice
        Season 1 
            Episodes 1 - 10(whatever there are)
        Season 2 
            Episodes 1 - 10(whatever there are)
        ... (so on)

    ... (More Tv Shows)

Sorry if this seems unclear. (Please ask for clarification)

But the structure i have right now is 3 tables (tvshow_list, tvshow_episodes, tvshow_link)

    //tvshow_list//
    TvShow Name | Director | Company_Created | Language | TVDescription | tv_ID

    //tvshow_episodes//
    tv_ID | EpisodeNum | SeasonNum | EpTitle | EpDescription | Showdate | epid

    //tvshow_link//
    epid | ep_link

The Director and the company are linked by an id to another table with a list of companies and directors.

I am pretty sure that there is an more simplified way of doing this.

Thanks for the help in advance,
Krishanthan Lingeswaran

Answer 1

I am pretty sure that there is an more simplified way of doing this.

Not as far as I know. Your schema is close to the simplest you can make for what I presume is the functionality you're asking for. "Improvements" on it really only make it more complicated, and should be added as you judge the need emerges on your side. The following examples come to mind (none of which really simplify your schema).

• I would standardize your foreign key and primary key names. An example would be to have the columns shows.id, episodes.id, episodes.show_id, link.id, link.episode_id.
• Putting SeasonNum as what I presume will be an int in the Episodes table, in my opinion, violates the normalization constraint. This is not a major violation, but if you really want to stick to it, I would create a separate Seasons table and associate it many-to-one to the Shows table, and then have the Episodes associate only with the Seasons. This gives you the opportunity to, for instance, attach information to each season. Also, it prevent repetition of information (while the type of the season ID foreign key column in the Episodes table would ostensibly still be an INT, a foreign key philosophically stores an association, what you want, versus dumb data, what you have).
• You may consider putting language, director, and company in their own tables rather than your TV show list. This is the same concern as above and in your case a minor violation of normalization.
• Language, director, and company all have interesting issues attached to them regarding the level of the association. Most TV shows have different directors for different episodes. Many are produced in multiple languages and by several different companies and sometimes networks. So at what level do you plan on storing this information? I'm not a software architect, so someone else can better answer this question than me, but I'd set up a polymorphic many-to-many association for languages, directors, and companies and an inheritance model that allows for these values to be specified on an episode-by-episode, season-by-season, or show-by-show basis, inheriting the value from its parent if none are provided.

Bottom line concerning all these suggestions: Pick what's appropriate for your project. If you don't need the functionality afforded by this level of associations, and you don't mind manually entering in repetitive data (you might end up implementing an auto-complete system to help you), you can gloss over some of the normalization constraints.

Normalization is merely a suggestion. Pick what's right for you and learn from your mistakes.

Answer 2

The basic concept of Normalization is the idea that you should only store one copy of any item of data that you have. It looks like you've got a good start already.

There are two basic ways to model what you're trying to do here, with episodes and shows. In the database world, we you might have heard the term "one to many" or "many to many". Both are useful, it just depends on your specific situation to know which is the correct one to use. In your case, the big question to ask yourself is whether a single episode can belong to only one show, or can an episode belong to multiple shows at once? I'll explain the two forms, and why you need to know the answer to that question.

The first form is simply a foreign key relationship. If you have two tables, 'episodes' and 'shows', in the episodes table, you would have a column named 'show_id' that contains the ID of one (and only one!) show. Can you see how you could never have an episode belong to more than one show this way? This is called a "one to many" relationship, i.e. a show can have many episodes.

The second form is to use an association table, and this is the form you used in your example. This form would allow you to associate an episode with multiple shows and is therefore called a "many to many" relationship.

There is some benefit to using the first form, but it's not really that big of a deal in most cases. Your queries will be a little bit shorter because you only have to join 2 tables to get episodes->shows but the other table is just one more join. It really comes down to figuring out if you need a "one to many" or "many to many" type relationship.

An example of a situation where you would need a many-to-many relationship would be if you were modeling a library and had to keep track of who checked out which book. You'd have a table of books, a table of users, and then a table of "books to users" that would have an id, a book_id, and a user_id and would be a many-to-many relationship.

Hope that helps!