Skip to main content

Design for Exceptions

This post is part of an ongoing series
  1. Design for Exceptions
  2. Spreading the Sheets and Letting Relations In
  3. Forming a New Reality
There's more to come... we're starting simple and working up... this will slowly become more complex to include SQL Server, ASP.Net web apps and ArcGIS.


One of the biggest mistakes I run into every day is databases and spreadsheets which weren't designed for the exceptions. I get it, most of your data fits in your model. But then you run into data that doesn't fit your world view and you call me. And that's when things get ugly.

We need an example.

Let's use ZipCodes. We all know how these work right? Each city has a zip code. Here's some examples from here in Southwest Idaho:
CityZipCode
Eagle83616
Kuna83634
Idaho City83631

That wasn't so hard was it? Our spreadsheet is done!

Not so fast cowboy. What do we do with Meridian? It has three Zips: 83642, 83646, 83680.

You've seen this before though and you got it taken care of don't you? That's hierarchical!
Cities
CityIDCity
1Eagle
2Kuna
3Idaho City
4Meridian


(I'll add the City in parentheses for clarity.)
ZipCodes
ZipCodeIDZipCodeCityID
1836161 (Eagle)
2836342 (Kuna)
3836313 (Idaho City)
4836424 (Meridian)
5836464 (Meridian)
6836804 (Meridian)


You mad dawg! You got it. But wait, the USPS is kinkier than that. 83714 is valid in Garden City, Boise and Hidden Springs! USPS Postal Code Lookup

Now what?! Welcome to the real world where everything is miscellaneous. Hierarchies only exist in human relationships and these days with a 50%+ divorce rate that isn't even true. Nice family bush you got there.

So what to do? You got a many-to-many on your hands. It's funny how common this pattern is... I'd call it more the rule than the default, yet somehow we always want to split things into distinct categories. I think it is a relic of the filing cabinet age that'll disappear in the few years. Google won, Yahoo lost the hierarchy war. Ontology is overrated and this is the pattern behind the free tagging web and real relationships in the real world. It's time for desktop databases to do a better job of handling these relationships. But I'll leave that for another post. For now you need a new pattern. Three tables:
Cities
CityIDCity
1Eagle
2Kuna
3Idaho City
4Meridian
5Boise
6Garden City
7Hidden Springs

ZipCodes
ZipCodeIDZipCode
183616
283634
383631
483642
583646
683680
783714

(I've added the City name/Zip Code in parenthesis for clarity.)
Cities_By_ZipCode
IDCityIDZipCode()
111(Eagle-83616)
222(Kuna-83634)
333(Idaho City-83631)
444(Meridian-83642)
545(Meridian-83646)
646(Meridian-83680)
757(Boise-83714)
867(Garden City-83714)
977(Hidden Springs-83714)

(In reality Boise has 29 Zip Codes and Garden City has two, but let's keep it a little simpler for this explanation)

I picked Zip Codes because they are a human constructed order that's outgrown it's own hierarchy. We could have just of easily used the classic teachers to students model and then dropped in co-teachers (my Dad had that job once), or tried to decide if a tomato is a fruit or veggie, or whether hot burns like fire or jalapeño... the world is full of them. If you look deep enough it's hierarchical relationships that are really uncommon.

In my next post, Spreading the Sheets and Letting Relations In, I'll give some examples of how to work with many-to-many relationships in MS Access.

UPDATE: Above I tangentially mentioned Clay Shirky's talk Ontology is Overrated and it's worth more than that. Like all of Shirky's talks it's brilliant, but this one is one of his best. Five years old and still a great read.
Labels:

Comments

Post a Comment

Popular posts from this blog

Attachment Reminder - and more for MS Outlook

I just did it again. We don't like to admit it, but we all have. You write a long letter describing the attachment, press send and then 10 seconds later remember you didn't actually attach the message. I finally decided to do something about it. Turns out it isn't too hard. Chiefly because Jimmy Peña at Code for Excel and Outlook already did all the hard work of writing up an excellent MS Outlook Etiquette Check Macro that does all the dirty work for you. What's left for you to do? In MS Outlook go to Tools > Macros > Visual Basic Editor Under the Project Panel (far left) Browse to Project1 > Microsoft Office Outlook Objects > ThisOutlookSession Double-click ThisOutlookSesson to Open (if you haven't been here before this will be a big blank canvas) Visit Code for Excel and Outlook Etiquette Check Code and select "Copy to Clipboard" at the top of the code. Or you can also copy from the code I've modified below if you prefer....
5 comments
Read more

Simple HTTP Redirect with Querystring in IIS7

HTTP Redirect seems simple enough. Always was in IIS6 and in IIS7 there's even a button labeled HTTP Redirect that promises relative redirects.  It looks like it'll be as easy Apache finally.  That is until you try to redirect a querystring.  Then everything bombs. Turns out it still is relatively easy, except you have to know that Microsoft changed $S$Q to $V$Q. Why? $Ss and $Gs I suspect. And How. In our example we'll redirect all pages under http://olddomain.com/content to http://mydomain.com/content. Pick the virtual directory you want to redirect. e.g. http://olddomain.com/content Click HTTP Redirect under IIS in the IIS management console. In the HTTP Redirect Dialog: Check Redirect requests to this destination Enter your new path ending with $V$Q.  e.g. http://mydomain.com$V$Q Counter-intuitively check Redirect all request to exact destination (instead of relative destination) Choose the appropriate Status Code (Permanent or Temp...
5 comments
Read more

ArcGIS One-to-Many Labeling

ArcGIS is just plain lousy at dealing with any relationship that isn't one-to-one. We all have a slew of hacks just to deal with this limitation. I for one regularly am creating temporary cross-tab queries so I can represent multiple sample results at a collection point, fish surveyed at a cross-section and a host of other relationships. The classic example for mapping comes from the cadastral community and condominium lots. It's an odd situation where more than one person has title to the same piece of ground. How do you represent this? I've got a new trick thanks to Mohammed Hoque's article in ArcUser Magazine. We're going to do a database query inside a label expression, loop through the results and output the entire list to label. For our example we'll use Outfitting Areas in Idaho and we'll label them with the Outfitters and Guide License Numbers and Outfitter Names. 1.) Open ArcGIS and add your spatial layer with the unique identifier shared with...
15 comments
Read more