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

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 destinationEnter your new path ending with $V$Q.  e.g. http://mydomain.com$V$QCounter-intuitively check Redirect all request to exact destination (instead of relative destination)Choose the appropriate Status Code (Permanent or Temporary)Apply Changes and Test

Maintaining Cross-Database Referential Integrity

It is often convenient to house your master tables in a separate database from application specific databases so that primary keys are copasetic and multiple databases have access to the same lookups.

Common examples would include employees, counties, regions and other lookup tables.

Once these tables are in a separate database however it is no longer possible to simply drag-and-drop a relationship between them to maintain referential integrity.

Here's a Solution
For this example we'll use an Observation table, tied to a master list of Sex, that's right, Sex (we could call it Gender to be all PC, but these are animals, not Pat in the Personnel Department.)

Step 1:
We create a new view called PIC_Sex in our Application Database by querying the table PIC_Sex in our Master Database:

CREATE VIEW dbo.PIC_Sex AS SELECT SexID, Sex, Rank FROM Master.dbo.PIC_Sex ORDER BY Rank
Yes, that's right, Male and Female! Slugs are awesome.

Step 2:
We create a new user-defined function xdf_Val…

Serving up KML in IIS 6

To serve up KML in IIS 6, you have to add a few MIME Types. The easiest way to do this is to apply new MIME Type settings globally by changing the properties on your server's "Web Sites" folder in IIS.


Google Earth reads KML and KMZ files. The MIME type for KML files is

* application/vnd.google-earth.kml+xml

The MIME type for KMZ files is

* application/vnd.google-earth.kmz

Source: Google KML Tutorial


To add a MIME type to a Web site or directory

1. In IIS Manager, right-click the Web site or Web site directory for which you want to add a MIME type, and click Properties.

2. Click the HTTP Headers tab.

3. Click MIME Types.

4. Click New.

5. In the Extension box, type the file name extension.

6. In the MIME type box, type a valid MIME type. If you define a MIME type that has already been defined at a higher level, you are prompted to select the level where the MIME type should reside.

To create a MIME type for an undefined MIME type, type an asterisk (*) in the Extension box, an…