Skip to main content

Color Considerations

I love the odd places my job takes me. Today I am building a database to house all of the "marks" we place on animals we capture. Tags, Collars, Leg Bands, RFID Tags (they're in fish and your passport), Tattoos, Radio Collars, GPS Collars, Backpacks, Wing Tags.... the list is seemingly endless.

The fun thing about designing databases is figuring out ahead of time all the exceptions. For the moment at least, I now know more details about tagging animals than many field biologists.

So far we have the following attributes:

Tag TypeCollar, Band, Round, Square
Tag LocationRight Leg, Left Leg, Right Wing, Left Wing, Neck, Nose, Right Ear, Left Ear
Tag ConstructionPlastic, Metal, Leather, Ink (tattoo)
ColorRed, Green, Yellow, Black, White
MarkR-2345, 562626849057, TIGGER, Y-7892-23
(We'll leave radio frequencies out of our discussion and focus purely on the visual aspects)

The challenging part of this is deciding what to leave in, what to leave out.

I can probably discard Tag Construction and just allow color to cover it. But discarding color and moving everything over to tag type (e.g. Yellow Right Ear Tag, Pink Fluffy Collar) would result in an unusable dropdown-list-from-hell.

In designing databases, you search for the optimal combination of fields which allows the user to subset the information when necessary, but not fragment it so far that information cannot be located or easily entered.

For example, when looking for a Wolverine with a Yellow Ear Tag (but you don't know the Tag Number) one may query by Species, Tag Color and Tag Location and get a list of yellow-tagged wolverines in return.

This all sounds simple, but for this to work you can't have a free text field for color. If you do, and many of our historical spreadsheets and databases do just this, you get the following "Y" , "Yellow", "Yell", "Ylw" and "Yellew". So oddly enough, you need a pick list for color. And the same rules apply. Not too many or you'll end up with "Marigold", "Wheat", "Burnt Umber*" and "Mustard" and when searching for "Yellow" you won't find any wolverines. Add too few and the appropriate color may not be available (e.g. Pink).

We need a standard list of colors. Wikipedia assists:
In the 1969 study Basic Color Terms: Their Universality and Evolution, Brent Berlin and Paul Kay describe a pattern in naming "basic" colors (like "red" but not "red-orange" or "dark red" or "blood red", which are "shades" of red). All languages that have two "basic" color names distinguish dark/cool colors from bright/warm colors. The next colors to be distinguished are usually red and then blue or green. All languages with six "basic" colors include black, white, red, green, blue and yellow. The pattern holds up to a set of twelve: black, grey, white, pink, red, orange, yellow, green, blue, purple, brown, and azure (distinct from blue in Russian and Italian but not English).

From which we now have our final color list** (user additions will be possible of course, but strongly discouraged).
ColorID Color
-99 Unknown
1 Black
2 White
3 Red
4 Green
5 Blue
6 Yellow
7 Grey
8 Pink
9 Orange
10 Purple
11 Brown
12 Azure

Granted, graphic artists and Donnie Hoyle will tell me that we should probably have two lists, one for hue and one for saturation, but these are biologists we're talking about. I'll catch enough flack for "Azure".

More Reading:

* There actually is no "Burnt Umber" in a Crayola pack, although it is my favorite random color name. It is an amalgamation of two other Crayola colors, "Burnt Sienna" and "Raw Umber," both fine colors in their own right.
** The optimal solution would probably not use named colors at all, but instead a graphical color picker where one used an eyedropper to capture the color. Then searches would be performed using a fuzzy threshold slider (like photoshop's select by color) to select tags with a similiar hue and saturation. One problem with this approach however would be that it would lack color blind usability. And again, these are biologists, not graphic artists.

Color Considerations is cross-posted on my personal blog Stupid Dingo.


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…