The colorful wolf

Ok, so I'm designing this website that allows you to keep track of which TV shows you're watching. It lets you rate the shows and also mark how much episodes you've watched. So if you're watching a lot of TV shows you can easily figure out what's the last episode you watched. It'll also allow you to see how many minutes you've spent watching the show. Here's a picture to make things more clear:

I'm watching The Big Bang Theory, and so far I've on the 3rd season having just finished watching episode 3. Episode 3 of season 3 is the 43rd episode, chronologically, of TBBT. Based on this information, and knowing the length of each episode, I can calculate how much time I've spent watching this show.

Here's what I'm stuck on right now: imagine that I can import all information about TBBT from IMdb, and I import that info today. Then one month later a new episode of TBBT comes out, which I want to mark on my website. Oops, I already imported the info from IMDb one month ago, so my site doesn't know about this episode. Do I import from IMDb again? On the user's request? Automatically every night? All of these options are not really feasible because there would just be too many items to synchronize. So, I want to be able to input this information manually, independently from external sources.

So how do I do that, and how do I do that in a user-friendly way? TBBT is a fairly common example: every season has a different number of episodes, but the duration of each episode is the same. However, I cannot rely on that. Every TV show has their own quirks. Here's a list of the quirks I've come across so far:

• Not every episode has the same length. Some of the episode lengths are unknown (but might be deduced by the average episode length. eg. every Star Trek The Next Generation episode is ~45 minutes).
• Most TV shows start at episode 1, but some TV shows mark their first episode as Episode 0, or they release a prequel episode later that is called episode 0.
• Some TV shows have 'minor' episodes, eg. Episode 4.5 that happens betweens two episodes but is less relevant to the main story.
• Some TV shows have side stories, behind-the-scenes features or other episodes that cannot be added to any of the known seasons. Do these count when marking how many episodes you've watched? Even IMDb is pretty vague on how it handles these.
• Usually all important information from a season can be retrieved from its episodes. In some cases no details about some episodes in a season are available.

Try to look at these quirks with the intention of designing a data model that can store the relationship between shows, seasons and episodes while storing the duration of each. My first approach was a pure hierarchical solution, storing TV show objects that contain Season objects that contain Episode objects. However, the last point in the list means that I had to make some compromises. In some cases I can't find out about how many episodes a season has, or how long each episode is. Sticking to a pure hierarchy, I added dummy items with default durations to fill in the gaps, but I'm not satisfied with this solution.

So, here's an exercise for you, dear reader. How would you store and display this information? Keep in mind that your solution will need to be able to do the following:

• Store the show-season-episode hierarchy
• Store the duration of each element as best as possible
• Be robust against missing information. (A single missing episode should not break progress tracking of an entire TV show)
• Handle edge cases like episode 0 and episode 4.5
• Be at least as user-friendly as the picture shown above
• Offer a way for the user to change the duration of each item in a user-friendly way

As for that last item, let's assume for now that every user has his own set of items, so nothing he changes will break the user experience of any other user.

This is pretty much the biggest hurdle for the website that I'm planning to launch. If I can solve this in a generic way then I'm good to go. In fact, just writing this blogpost and defining the problem in words made things a bit more clear already. Let me know your thoughts!