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.

Comments

Popular posts from this blog

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...

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...

Simple Record-Level Auditing in SQL Server & ArcSDE

I've had simple auditing set up in my databases for years thanks to our old DBA. This is not robust transaction logging, it just answers the questions "Who created this?", "When was this created?", "Who last edited this?" and "When was this last edited?". After witnessing a brand new table audit itself without triggers, I set about to understand the setup. This seemed curious to me, so I thought it might also be curious to you. It turned out to be a two step system which rendered my insert triggers superfluous (at least I think so, tell me if I'm missing something and I need insert triggers after all). Our solution uses a combination of two methods. For inserts we are going to tie our audit fields to User Defined Data Types which inherit Defaults we have specified. For updates we will build a trigger to log the changes. We will use the following fields in our tables to track inserts and updates: AUD_NUser - Creator AUD_NDate - Creati...