::::: : the wood : davidrobins.net

A short story about attributes

News ·Tuesday October 24, 2006 @ 02:13 EDT (link)

There is a way which seemeth right unto a man, but the end thereof are the ways of death.

—Proverbs 14:12

Once upon a time there was a developer that needed a database to track photographs. But this database needed to be "future-proof"—what if more information needed to be added later? "Aha!" thought the developer, "I'll just create a table of attributes. Two, actually, one for integer attributes and one for string attributes. Each attribute will have an index, yea, CAPTION shall be 1001, CATEGORY shall be 1002, etc. Images shall be described by sets of (image id, attribute, value) pairs." And the developer saw that it was good, although only two attributes, both captions, were ever added to the database over several years.

Experienced architects will realize that this developer, um, who looks a lot like me, actually, had actually invented a database within a database, and done a fairly stunningly poor job at it, at that, for the following reasons:

Every type needs a new attribute table, and all text will be lumped together as text columns, which probably isn't the most efficient way to work.
On any decent database, empty or null varchar columns don't take up much (if any) space. Premature optimization is the root of all evil; unless there are hundreds of possible attributes, a regular flat table will suffice (given the hits I get, a flat file would work, but the interface isn't nearly as convenient).
With regular tables, the data can be properly typed (and constrained), and the database can make intelligent decisions about types.

So, let this be a lesson to y'all. For those interested, the image directory now has columns id (autogenerated index), name (can be transformed to the image filename), caption (description for the directory or image), date (date the photograph was taken, from the imported file date), access (whether the photograph is viewable externally). I'm still fine-tuning a few things, like how to break up photographs into linear sets; I'd rather do it by event than by when I decided to dump the photos from the camera, which is how they are now. I do have two more tables for tags (categories): category (id and name), and category_map (unique image and category pairs, with foreign key references and on delete cascades).

Note to self: use cdrecord -dev=ATA -scanbus to check and cdrecord -dev=ATA:n,n,n -v -speed=n -data cd.iso; in particular, don't use ATAPI or SCSI device with 2.6 kernels; ATA is superior (according to the LKML, although all methods give some sort of bizarre warning).