Skip to content

GitLab

  • Projects
  • Groups
  • Snippets
  • Help
    • Loading...
  • Help
    • Help
    • Support
    • Community forum
    • Submit feedback
    • Contribute to GitLab
  • Sign in
Genesys Backend
Genesys Backend
  • Project overview
    • Project overview
    • Details
    • Activity
    • Releases
  • Repository
    • Repository
    • Files
    • Commits
    • Branches
    • Tags
    • Contributors
    • Graph
    • Compare
  • Issues 45
    • Issues 45
    • List
    • Boards
    • Labels
    • Service Desk
    • Milestones
  • Operations
    • Operations
    • Incidents
  • Packages & Registries
    • Packages & Registries
    • Container Registry
  • Analytics
    • Analytics
    • Repository
    • Value Stream
  • Wiki
    • Wiki
  • Members
    • Members
  • Collapse sidebar
  • Activity
  • Graph
  • Create a new issue
  • Commits
  • Issue Boards
  • Genesys PGR
  • Genesys BackendGenesys Backend
  • Issues
  • #250

Closed
Open
Opened Apr 19, 2018 by Matija Obreza@mobrezaOwner
  • Report abuse
  • New issue
Report abuse New issue

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.

Assignee
Assign to
2.5
Milestone
2.5
Assign milestone
Time tracking
None
Due date
None
Reference: genesys-pgr/genesys-server#250