DB Design

[aaronleopold]

Leaving this issue open for the future, but currently the database schema is rather monolithic. This was an intended decision in the beginning of this projects life, however there is definitely room for abstraction if desired and would be my recommendation in the future. This was initially mimicking another database schema which was more alike spesql's currently. I think an ideal restructure would be:

<labname>Table
There would be multiple tables, where it references a single lab groups data. This would mainly hold the cataloging information (catalogNumber, projectNumber, notes, etc), as well as hold references to corresponding tuples in the other tables

taxonomies
This would hold on to all taxonomic data, as to be reused.

localities
this would hold on to location data for a tuple in one of the lab tables

loans
this would hold the loan status information for a given catalogNumber

.... etc ....

The UI would thankfully remain mostly the same, its mainly the backend logic and query generation behind the scenes that would need some restructuring. There are many routes to take, but one would be the option to toggle the automatic joining/loading in relations for all queries, so that functionally it feels the same to use the application.

A general rough replacement could be something like (I didn't take the time to add any cascade actions and skipped some of the fields but):

CREATE TABLE `labs` (
  `lab_id` INT NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(45) NULL,
  PRIMARY KEY (`lab_id`));


CREATE TABLE `taxonomies` (
  `taxonomy_id` INT NOT NULL AUTO_INCREMENT,
  `order_` VARCHAR(45) NULL,
  `superfamily` VARCHAR(45) NULL,
  `family` VARCHAR(45) NULL,
  `subfamily` VARCHAR(45) NULL,
  `tribe` VARCHAR(45) NULL,
  `subgenus` VARCHAR(45) NULL,
  `genus` VARCHAR(45) NULL,
  `specificEpithet` VARCHAR(45) NULL,
  `infraspecificEpithet` VARCHAR(45) NULL,
  PRIMARY KEY (`taxonomy_id`));

CREATE TABLE `collectionEvents` (
  `collection_id` INT NOT NULL AUTO_INCREMENT,
  `recordedBy` VARCHAR(255) NULL,
  `identifiedBy` VARCHAR(255) NULL,
  `dateIdentified` DATE NULL DEFAULT NULL,
  `verbatimDate` VARCHAR(30) NULL,
  `collectedYear` INT NULL DEFAULT NULL,
  `collectedMonth` INT NULL DEFAULT NULL,
  `collectedDay` INT NULL DEFAULT NULL,
  `fieldNotes` TEXT NULL,
  `otherCollectors` TEXT NULL,
  PRIMARY KEY (`collection_id`));

CREATE TABLE `collectionLocations` (
  `location_id` INT NOT NULL AUTO_INCREMENT,
  `stateProvince` VARCHAR(100) NULL,
  `country` VARCHAR(100) NULL,
  `municipality` VARCHAR(100) NULL,
  `locality` VARCHAR(100) NULL,
  `elevationInMeters` VARCHAR(20) NULL,
  `decimalLatitude` DECIMAL(10,8) NULL,
  `decimalLongitude` DECIMAL(11,8) NULL,
  `geodeticDatum` VARCHAR(25) NULL,
  `corrdinateUncertainty` VARCHAR(25) NULL,
  `verbatimLatitude` VARCHAR(25) NULL,
  `verbatimLongitude` VARCHAR(25) NULL,
  PRIMARY KEY (`location_id`));

CREATE TABLE `loans` (
  `loan_id` INT NOT NULL AUTO_INCREMENT,
  `to` VARCHAR(45) NULL,
  `at` DATETIME NULL,
  `return` DATETIME NULL,
  PRIMARY KEY (`loan_id`));

CREATE TABLE `storages` (
  `storage_id` INT NOT NULL AUTO_INCREMENT,
  `rack` VARCHAR(45) NULL,
  `freezer` VARCHAR(45) NULL,
  `rack` VARCHAR(45) NULL,
  `box` VARCHAR(45) NULL,
  `tubeSize` VARCHAR(45) NULL,
  PRIMARY KEY (`storage_id`));

CREATE TABLE `specimen` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `catalogNumber` VARCHAR(20) NOT NULL,
  `otherCatalogNumber` VARCHAR(25) NULL DEFAULT NULL,
  `recordNumber` VARCHAR(25) NULL DEFAULT NULL,
  `projectNumber` VARCHAR(50) NULL,
  `otherIdentifier` VARCHAR(25) NULL,
  `lab_id` INT NOT NULL,
  `taxonomy_id` INT NULL,
  `collection_id` INT NULL,
  `location_id` INT NULL,
  `loan_id` INT NULL,
  `storage_id` INT NULL,
  `reared` CHAR(1) NULL,
  `withholdData` CHAR(1) NULL,
  PRIMARY KEY (`id`),
  UNIQUE INDEX `catalogNumber_UNIQUE` (`catalogNumber` ASC) VISIBLE,
  INDEX `fk_specimen_labs1_idx` (`lab_id` ASC) VISIBLE,
  INDEX `fk_specimen_taxonomies1_idx` (`taxonomy_id` ASC) VISIBLE,
  INDEX `fk_specimen_collectionEvents1_idx` (`collection_id` ASC) VISIBLE,
  INDEX `fk_specimen_collectionLocations1_idx` (`location_id` ASC) VISIBLE,
  INDEX `fk_specimen_loans1_idx` (`loan_id` ASC) VISIBLE,
  UNIQUE INDEX `otherCatalogNumber_UNIQUE` (`otherCatalogNumber` ASC) VISIBLE,
  INDEX `fk_specimen_storages1_idx` (`storage_id` ASC) VISIBLE,
  CONSTRAINT `fk_specimen_labs1`
    FOREIGN KEY (`lab_id`)
    REFERENCES `labs` (`lab_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_specimen_taxonomies1`
    FOREIGN KEY (`taxonomy_id`)
    REFERENCES `taxonomies` (`taxonomy_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_specimen_collectionEvents1`
    FOREIGN KEY (`collection_id`)
    REFERENCES `collectionEvents` (`collection_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_specimen_collectionLocations1`
    FOREIGN KEY (`location_id`)
    REFERENCES `collectionLocations` (`location_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_specimen_loans1`
    FOREIGN KEY (`loan_id`)
    REFERENCES `loans` (`loan_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_specimen_storages1`
    FOREIGN KEY (`storage_id`)
    REFERENCES `storages` (`storage_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION);

CREATE TABLE `entryEvents` (
  `entry_id` INT NOT NULL,
  `dateEntered` DATE NULL,
  `recordEnteredBy` VARCHAR(45) NULL,
  PRIMARY KEY (`entry_id`));

One hiccup that prevented me from doing this during the TypeScript rework was that UFIT did not allow actions or triggers for some reason.

[aaronleopold]

https://github.com/FLMNH-MGCL/spesql/tree/mikro - this branch won't be ready for quite some time, but in my spare time over the weekends I'll be occasionally working on this branch.