Brave new world of Web Services

So I’m writing a programmer Kata (that is an exercie I expect to repeat several times, just because I want to improve my skills as a developer).  I’m writing a a better movie meetup application.  The first use case is a user adds an event.  But the user has to look up the movie theater location, time, place, either manually–and that is a lot to ask from a user,  or look it up in a table I maintain, and that is too much to ask from me.  But Movies.com, Fandango.com, Google.com/movies, Yahoo, imdb.com, Netflix.com  and more already have movie and/or theater and showtime information.

Movies.com and Fandango.com are both restrictive in their TOS. You can link to their home page, other wise, they want you to f*-off.  No API’s outside of RSS feeds anyhow.

Fortunately, other companies have API’s with liberal TOS, i.e. free, allow sufficietly large number of requests per day and allow commercial use, Google and Netflix the best of the TOSs.

Google.com has API’s, but google.com/movies has to be screen scraped. We got the technology, but I can’t tell if I have the right.

Yahoo has the API’s, but they will give you the location of the theater(s), but not showtimes.

Netflix.com has the API, but they only have movie review and description information.

All of these services could be cancelled at any moment, could change their business model, or change their interface.  So my application will still need to have all the code necessary to input theaters, movies, and showtimes manually.

All of a sudden, this is a lot of work to avoid duplicating the work of listing theaters, movies and showtimes available for going out to the movies with a group of friends!

How to Overengineer the Reference Table/Code Table

You probably store this in the database as a table.  It could also have been column constrain, although a column constraint won’t let you run a select. None of these easly allow you to treat the reference table as an enum in C#, unless you use code generation.

Worst
Create Table  States_Ref (Id int, Description Varchar(50))

The code is numeric,  so humans can’t look at raw tables to see data problems.  The data type is too large.  Worse would be bigint, or worst of all, just using image as a datatype in case there are a google of different states.  If all reference tables use Id/Description, then select queries go from

Select customer_id, state_id, type_id, category_id … etc

to

Select customer_id, t1.description, t2.description, t3.description… and now all queries require alias. Ugh.

Better
Create Table  States_Ref (States char(2), Description Varchar(200)).  Everyone can memorize CO, no one can memorize that 31 means Colorado.  Obviously, alphanumeric codes are for the maintenance developers and uber-power users.  If your users are brain damaged, the actual reference value would be hidden.  In the real world, IT staff will eventually have to learn many of the numeric codes (But don’t get clever with alpha codes, don’t embed a mini-db into the codes, like CO_MTN_STATE_US.)

Best
Create Table  States_Ref (States char(2), State_Name Varchar(200), State_Name_Short Varchar(50), Active bit)

The alphanumeric codes are space efficient and human memorizable.  The Active bit allows certain codes to be phase out without going back to legacy data and changing the old code.  The descriptive columns include the table name (In data tables, ie. not reference tables, I wouldn’t add the table name to every column, though)

C# and ASP.NET
Bad
: Put the drop down list into a DataSet, reload every time.
Better Yet
: Put the list into a Sorted Dictionary or Sorted List. (And do the sorting server side in SQL 1st of course), reload everyt time.
Better Yet:
Batch up requests for reference tables, return them all at once in a multi query  result set.
Best: Put the Sorted Dictionary into the ASP.NET Cache (Or viewstate at least).   DataSets and DataTables both are too big.  Don’t cache the user control– it causes peculiar behaviors

UI-
Worst- Dropdowns that require a mouse to search and select.
Better- List boxes that allow viewing all rows (sometimes the list is too long and screen space to sparse, but if you can, show all the items)
Best- JavaScript instant filters for long lists

Binding-
Worst- Fail to load or show a blank if the value is no longer Valid
Best- Add value to list if binding and the value isn’t on the list.

Any more ideas?