So lately I’ve been working on updating a website I use to keep track of my movies. The wife and I have collected over 1000 movies and TV show seasons, and it’s hard to remember what we have and don’t have, especially when we see a movie we like on sale. So anyway, some years ago I created a website to keep track of these things. At first, and for the last few years, it was very simple. Each movie was a single row in a table, and the actors and genres and whatnot were just fields in that table. Each movie basically lived in it’s own world, one movie might star “Samuel L Jackson”, and another could star “Samuel L. Jackson”. Since it was just for my wife and I, we lived with these limitations, we could just be careful about how we entered movies.
Of course, early entries were done entirely by importing CSV files into the database directly, but eventually I added a web interface to add and edit movies in the database (since my wife is not very SQL knowledgeable). Of course, this page was very limited. You could enter whatever you wanted into text fields (title, starring, comments) and it had pre-determined check marks for genres and media (actually, I added media after we bought our first Blu-ray player, before then, everything was DVD). When we were dating we had an “owner” field, but we’ve since abandoned that.
Anyway, my mother-in-law was commenting about their movie collection (maybe 500-600, and decent collection for sure). My wife mentioned out database, and since it would only involve copying the source code to another directory and duplicating the structure of one MySQL table, I agreed to setup a website for the in-laws. Five minutes after sitting down at my computer, and I had a website all ready to go, just needed the list of movies. However, I examined the website, and determined it lacked panache. Actually, it lacked usability, and I set myself to the task of re-working the website to make it more friendly. The last such update, some months ago, was to pass around a validation key so that the password didn’t have to be re-typed when entering multiple movies at once, not a technological accomplishment by any means.
It began with a focus on the database itself. It seemed pointless to me to have to re-enter the couple-hundred movies my in-laws and I surely have in common. Especially so when my movies are already cataloged by genre and actor (where appropriate, seriously, can you name one actor from “Killer Klowns from Outer Space” without IMDB or Wikipedia?). I split the single movie table into actors, media, genres, and movies, and created relational tables between them. This, of course, made the SQL statements much more complex, where before it was simply “SELECT * FROM movie WHERE actor LIKE ‘Tim Robbins”, the code now involves multiple joins and grouping statements. Still, once I optimized the query, it didn’t seem to take any longer than before.
Once the database was setup, I worked on improving the interface. The page to add/edit movies, as stated before, was minimal. You could enter any actor name you wanted, and title, and as long as the password was correct, you could insert that data into the database. Now, it checks the title you have entered against the database (using AJAX) and offers suggestions (example: “Tron Legacy” –> “Tron: Legacy”, actually, we don’t own that, but anyway…). It also has a browse-able list of actor name, and click on the name adds it to the movie. No more typos leading to duplicated actors. Anyway, it was much more user friendly now, but these changes could only be demonstrated when adding a new movie. My next task was to update the display pages.
Well, actually, updating the display pages had to be done after I updated the database, since my tables were now different, but it looked exactly the same, which isn’t very fun. I decided to clean-up the clutter (and reduce my database hits) by removing the actors and genres from the main display page. Some movies (*cough* Ocean’s 13 *cough*) had a lot of actors and were cluttering up the screen. However, in order to determine the difference between “Alice and Wonderland” and “Alice in Wonderland” (one the animated Disney classic, the other the live-action movie directed by Tim Burton), I still needed to display the genres and actors. I wrote-up a little AJAX query, and soon enough, clicking on a movie title pops-up a box with all the movie details.
Finally, I figured I wanted a faster way to find a movie than by clicking on the starting letter and scrolling through the list. I implemented a final AJAX query and now have a search field on every page. This way, if I’m at the store and want to know if I already own “The Dark Knight”, I can be reminded that we already own 2 DVD copies and 1 Blu-ray copy without having to scroll through all the movies staring with D.
Of course, there were many other changes to get my website to conform to HTML5 and CSS3, but I think the website should now be much more user-friendly (so I don’t have to answer too many question from my MIL). Next on my plate is an Android app that will interface with the website and allow caching of movies (when you’re at the back of Target and can’t seem to get a data connection) and searching for quick lookups. Luckily the in-laws didn’t go with iPhone’s, cause I couldn’t help them there.
Oh, hah, here’s the link to the movie website. It’s open for browsing, but no editing without the password. http://movies.nzh.name