Siliceous and Delicious

Struggled some more with setting up the tables. I had changed my schema last week, because I had discovered there was something of a redundancy in the Slides table. Both the Slide ID and the Hole ID would contain the Hole ID (for the reason I described earlier—the non-Hole-ID part of the Slide ID is not unique). So I thought I’d implement this as a composite primary key, the primary key for the Slides table consisting of a Slide ID and a Hole ID column. This means that both of those columns need to be in the child table, Individuals. As I was trying to implement this, I realized having a composite primary key would make for unwieldy joins and searches, for the sake of a relatively low savings in storage (redundancy of the 4-character hole designation in the Slides table, which will have a few 100 entries, so on the order of maybe a few thousand characters).

Once I had fixed the table setup to reflect this design change, it came time to return to testing the tables set up so far (everything except for Measurements), to see if it works. The kind of query I want to be able to make first: show me all the individuals from a certain hole. By my current understanding of SQL, the query should look something like this:

SELECT indiv_id, species, comment, preservation FROM holes h, slides s, individuals i WHERE h.hole_id=s.hole_id AND s.slide_id=i.slide_id AND h.hole_id=’0699A’

This, in fact, works. And is a substantial cause for celebration! Pretty satisfying, I can hardly believe I’ve taught myself enough SQL to do this in a couple of short weeks. Very cool. Next tasks: add the measurements table, and address the foreign key constraints issue from last week.

Asked Andy whether it would be OK to move the microscope—he made the very good point that moving the microscope is a major undertaking since it will most likely cause the optics to get jiggled out of alignment, and would then require a technician to be called in to re-center everything and get it all working again. With that in mind, it might be easier to just run to the computer store and pick up an external hard disk, and then get a set-up going in the back room with my laptop.

The measurements table seems to be working now, too. And I’m able to, for example, retrieve measurements of a particular type from a particular species found in a specified hole:

SELECT h.hole_id, i.indiv_id, species, meas_type, meas_value FROM holes h, slides s, individuals i, measurements m WHERE h.hole_id=s.hole_id AND s.slide_id=i.slide_id AND i.indiv_id=m.indiv_id AND m.meas_type=’Length’ AND h.hole_id=’0709C’ AND i.species=’Pterocorys oblongatus’

This is deeply awesome.