Skip to main content

Recording a Booby Observation for Posterity

CC Licensed photo by Rileypie
Building on the database that spurred my Color Considerations, I spent the better part of today debating the best way to log observers in a database. Having documented it to explore the pros and cons with my coworkers, I figured I'd share my musings with y'all as well. What way do you think is best? Is there a better pattern I left out?

On a blue-footed booby capture Nov. 11, 2007 we had four biologists recorded on the capture event: Mike, Roy, Phil and Jay.

How do I record this? Let’s review a few options:

Two Integer Fields to an Observer lookup table
Observer1ID: “272”     (MIKE)
Observer2ID: “312” (ROY)

NEGATIVE: The rest of the observers disappear in the data abyss.

One Integer Field to an Observer lookup, One Free Text Field
Observer1ID: “272”     (MIKE)
OtherObservers: “ROY, PHIL, JAY”

NEGATIVE: Nothing lost, but only able to reliably query on Observer1… Other Observers are listed, but there is no way to find PHIL. He could be “PHIL, P., PHILLIP, PHILIP, or FILLUP”

Observation Table linked to Observation_Observer Table by ObservationID

Observation_Observer Table has the following Structure for example record, “123”
ObservationID    ObserverID   Rank
123 272 1
123 312 2
123 21 3
123 128 4

NEGATIVE: The weakness of this model is that it is possible to enter an observation with no observer since enforcing the creation of child record in a database is difficult and one would have to rely on the interface for data integrity. Additionally, querying subtables is just a pain and often done incorrectly by users.

Modified Option 3 – With Observers assigned roles.
Observation Table linked to Observation_Observer Table by ObservationID

Observation_Observer Table has the following Structure for example record, “123”
ObservationID    ObserverID   RoleID
123 272 1 (Primary Observer)
123 312 2 (Secondary Observer)
123 21 6 (Sample Collector)
123 128 2 (Secondary Observer)

NEGATIVE: This model shares most of the same weaknesses as Option 3. The addition of roles does codify the each of the observer's parts better, but introduces another weakness that no Role is necessarily required. At least in Option 3 one could produce a 1-to-1 relationship by selecting the highest ranked Observer (lowest number) as long as one tested for nulls first.

Option 5
Modified Option 2 & 4 combined – Primary Observer in the Main Table with Secondary Observers in a subtable assigned roles.

Observation Table
PrimaryObserverID: “272”

Observation_Observer Table has the following Structure for example record, “123”
ObservationID    ObserverID   RoleID
123 312 2 (Secondary Observer)
123 21 3 (Sample Collector)
123 128 4 (Secondary Observer)

NEGATIVE: The only weakness of this is that entering secondary observers may be a little tiring over the free text field Option 2. One may also want to consider the ability to enter free text for anecdotal observations rather than creating another Observer record. Perhaps certain applications would enter structured data in the Observation Comment field and the PrimaryObserver would be “Anecdotal – See comments”. For most incidentals however, follow-up is very important making the addition of a new observer an obvious and necessary step so a biologist has a phone number or email to confirm the sighting.

I have my favorite. Which option do you think has the best combination of ease of use and future queryability? Do you have a better design pattern?


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 to
Pick the virtual directory you want to redirect. e.g. 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.$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/

The MIME type for KMZ files is

* application/

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…