Skip to main content

Source: ebisu/docs/archive/old-database-diagrams.md | ✏️ Edit on GitHub

Taxonomic Database Visual Documentation

This document contains comprehensive Mermaid diagrams visualizing the database structure, relationships, and data flow of the taxonomic system.

Table of Contents​

  1. Complete Database Schema
  2. Source Management System
  3. Reference Data Structure
  4. Taxonomic Data Architecture
  5. Species Integration System
  6. Data Import Flow
  7. Query Patterns

Complete Database Schema​

This diagram shows all tables and their relationships in the taxonomic database.

Source Management System​

This diagram shows how the original_sources table manages data provenance across the system.

Reference Data Structure​

This diagram shows the reference data tables and their relationships.

Taxonomic Data Architecture​

This diagram shows the structure of the three main taxonomic data sources.

Species Integration System​

This diagram shows how species from different sources are integrated.

Data Import Flow​

This diagram shows the complete data import process and dependencies.

Query Patterns​

This diagram shows common query patterns and data access paths.

Database Statistics​

Migration Execution Timeline​

Key Design Decisions​

Usage Examples​

Finding Species Information​

-- Get complete species information
SELECT * FROM unified_species_view 
WHERE normalized_name LIKE '%thunnus%';

-- Get species with high-quality matches
SELECT * FROM species_master_view_enhanced 
WHERE match_quality = 'EXCELLENT' 
  AND has_trade_code = true;

Trade Code Lookups​

-- Find species by trade code
SELECT s.*, a.alpha3_code, a.english_name
FROM asfis_species a
JOIN asfis_species_mappings asm ON a.id = asm.asfis_id
JOIN species_name_registry s ON asm.species_id = s.species_id
WHERE a.alpha3_code = 'TUN';

-- Get cascade assignments
SELECT * FROM taxonomic_cascade_resolved tcr
JOIN species_name_registry s ON tcr.species_id = s.species_id
WHERE cascade_id IN (
    SELECT cascade_id FROM taxonomic_cascade_config 
    WHERE source_code = 'TUN'
);

Country Profiles​

-- Get complete country profile
SELECT 
    c.*,
    eu.is_eu,
    foc.is_foc,
    ilo.is_c188_ratified,
    ilo.date_ratified
FROM country_iso c
LEFT JOIN country_iso_eu eu ON c.id = eu.country_id
LEFT JOIN country_iso_foc foc ON c.id = foc.country_id
LEFT JOIN country_iso_ilo_c188 ilo ON c.id = ilo.country_id
WHERE c.alpha_3_code = 'USA';

Notes​

  • All diagrams are designed to be rendered with Mermaid v10+
  • Colors and styles are kept simple for clarity
  • Relationships show cardinality (one-to-many, many-to-many)
  • Performance considerations are highlighted throughout
  • Query examples demonstrate intended usage patterns