I apologize for wasting time down this rabbit hole, but, it's winter and I'd rather sit at my computer than pretend I enjoy going outside. Eventually I had to experiment with a database application on a PC, having spent my professional career as an applications programmer in the IBM world. I've got almost 2,000 images taken over two years. Putting them into a database would entail going back through and looking closely and I am bound to learn something about my subject in the process. LibreOffice is free, and what I use as an alternative to Microsoft Office. Generally, it works pretty well, although the database component (called simply 'Base') is pretty flaky. Flaky like it has crashed on me at least 30 times in the 10 days or so I've spent setting this up. Also, quirky with its own unique learning curve. Luckily there are YouTubes that provide the usual range of good-bad instructions. Now, the dry details.
I set up a relational database that is normalized - meaning a single piece of information is stored only once, and referred to by pointer from the places that data is used. For example, 'Stream Name' is used in each Site record, but the Site records don't have the name all spelled out, but a pointer to a record in a 'Stream' table where the name is stored once. If I need to correct the spelling of the stream name, I do it once in the stream table, and all records that reference it are corrected.
The design of the database is centered around Observation records that include 'where' 'what' and 'when'. The 'where' is the Site, the 'what' is the organism down to either family or genus level, and the 'when' is just a date. The Observation record 'extras' include an optional 'Notes' field, and a pointer to a locally-stored picture. The tables of the database are tied to each other by key fields as shown in this schematic:
- Relationships.jpg (101.14 KiB) Viewed 147 times
This is the data entry form for managing sites. I included Lat/Long so I could actually show my sites in mapping programs :
- Site.jpg (121.7 KiB) Viewed 147 times
The Organism table includes basic taxonomy down to family level, which is as far as I know about in many cases, and which is why I did not include Genus in this table. I created a separate Genus table for families that I thought I could identify to Genus level, which gets included as an optional field of an observation. This is the Organism maintenance form :
- Organism Family.jpg (77.99 KiB) Viewed 147 times
This is the Observation entry form, where I link the site to the organism and possibly genus, add notes and a picture.
- Form Observation.jpg (163.47 KiB) Viewed 147 times
Update 1/07/25 - I am going to re-implement this using MS Access: Libreoffice is way too buggy to be reliable for the amount of work it will take to enter the data. Will post updates as I go.