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:
(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:
From which we now have our final color list** (user additions will be possible of course, but strongly discouraged).
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.
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 Type | Collar, Band, Round, Square | |
| Tag Location | Right Leg, Left Leg, Right Wing, Left Wing, Neck, Nose, Right Ear, Left Ear | |
| Tag Construction | Plastic, Metal, Leather, Ink (tattoo) | |
| Color | Red, Green, Yellow, Black, White | |
| Mark | R-2345, 562626849057, TIGGER, Y-7892-23 |
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.
Comments