Data model cleanup

Genesys data model has historically used several database tables for different sections of MCPD: accession + accessioncollect + accessionbreeding + accessionexchange, etc. These “section” tables have a 1-to-1 relationship to records in the core accession table and were originally introduced as a measure to improve database query speeds in view of data sparsity.

This model makes for complicated SQL querying and data export as those additional tables need to be joined to execute the search and need to be loaded to access all passport data for any accession.

The updated data model will undo this database design and bring all columns back to a single table with auxiliary tables for multi-value descriptors: accession names and other identifiers, storage and remarks. These are required for fast and correct querying (e.g. by STORAGE). Instead of generating MCPD-format multi-value strings from sub-tables (e.g. REMARKS) we will pre-generate them and store them in a separate passport table (accession_export?) with all data correctly merged using semicolon (;). This table will be kept up-to-date as part of upsert operation.

Implementation details

This change of the data model affects all aspects of Genesys: the API, JSP pages, querying, elastic search. If possible, we would keep the original AccessionColl and other classes and populate them in the service methods so that no change is required to JSPs. Because of the change in the data model, the filter codes (coll.collMissId) may need adjusting.