SD's latest request for the search interface is that, when a trial file has more than one associated trial, respite, and/or outcome, only the first record in each of those subtables should be searchable. For example, if a trial file has two outcomes - 'Transported' and 'Military Service' - only the 'Transported' outcome should be searchable. I had planned to accomplish this in the search query by imposing a LIMIT, for example:

SELECT .... FROM trial_files WHERE outcomes.outcome_id IN (SELECT outcome_id FROM outcomes WHERE outcomes.trial_file_id_fk = trial_files.trial_file_id LIMIT 1)

However, it turns out that, as of version 5.5, MySQL doesn't yet support LIMIT in subqueries:

#1235 - This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'

So that approach won't work. It's for the best, though, since that relies on the order of the records in the database. What I will do now is add a "rank" column to the respites, outcomes, and trials tables. Only records with a rank of 1 will be searchable. In the detail view for a record, respites, outcomes and crimes will be displayed in order of rank. The SQL:

ALTER TABLE  `trials` ADD  `rank` INT NULL DEFAULT NULL;
ALTER TABLE  `outcomes` ADD  `rank` INT NULL DEFAULT NULL;
ALTER TABLE  `respites` ADD  `rank` INT NULL DEFAULT NULL;

I've manually added ranks to the 1790s data in the database; future rank will be taken from the order of the elements in the XML files.