ER diagram

Not all columns are included in the ER diagram. For a complete documentation of all columns, see table descriptions.

erDiagram

seeds {
  INT id PK
  INT plant_id FK "NOT NULL"
  TEXT name "NOT NULL"
  TEXT variety
  INT created_by "NOT NULL"
}

plants {
  INT id PK
  TEXT unique_name "NOT NULL"
  TEXT[] common_name_en
  TEXT[] common_name_de
  TEXT family
}

spatial_relations {
  INT plant1 PK
  INT plant2 PK
  SPATIAL_RELATION_TYPE relation "NOT NULL"
}

temporal_relation {
  INT plant_earlier PK
  INT plant_later PK
  INT min_duration
  INT max_duration
  TEMPORAL_RELATION_EFFECT effect
}

maps {
  INT id PK
  TEXT name "NOT NULL"
  UUID created_by "NOT NULL"
  GEOMETRY geometry "NOT NULL"
}

layers {
  UUID id PK
  INT map_id FK "NOT NULL"
  LAYER_TYPE type "NOT NULL"
  TEXT name "NOT NULL"
}


plantings {
    UUID id PK
    UUID layer_id FK "NOT NULL"
    INT plant_id FK "NOT NULL"
    INT x "NOT NULL"
    INT y "NOT NULL"
}

drawings {
    UUID id PK
    UUID layer_id FK "NOT NULL"
    DRAWING_SHAPE_TYPE shape_type "NOT NULL"
    INT x "NOT NULL"
    INT y "NOT NULL"
}


users {
  UUID id PK
  TEXT country "NOT NULL"
  EXPERIENCE experience
  MEMBERSHIP membership
}

guided_tours {
  UUID user_id PK
  BOOLEAN editor_tour_completed "NOT NULL"
}

blossoms {
  TEXT title PK
  TEXT description
}


gained_blossoms {
  UUID user_id PK
  TEXT blossom FK "NOT NULL"
}

base_layer_images {
  UUID id PK
  UUID layer_id FK "NOT NULL"
  TEXT path "NOT NULL"
}


plants ||--o{ seeds : ""
relations }o--|| plants : "plant1"
relations }o--|| plants : "plant2"
layers }|--|| maps : "belongs to"
plantings }o--|| layers : "planted on"
drawings }o--|| layers : "drawn on"
plantings }o--|| plants : ""
maps }o--|| users : "created by"
blossoms ||--o{ gained_blossoms : ""
gained_blossoms }o--|| users : "gained by"
users ||--o| guided_tours : "completed by"
base_layer_images }o--|| layers : "belongs to"
users ||--o{ seeds : ""
plantings }o--|| seeds : ""

Plants Table

For performance reasons, we decided to disable some unused columns in the plants table for now.

For more information, see: PR #644

The following columns are commented out for now:

• genus: text
• medicinal_uses: text
• material_uses_and_functions: text
• botanic: text
• material_uses: text
• soil_water_retention: soil_water_retention[]
• environmental_tolerances: text[]
• native_geographical_range: text
• native_environment: text
• flower_colour: text
• flower_type: flower_type
• plant_references: text[]
• is_tree: boolean
• nutrition_demand: nutrition_demand
• article_last_modified_at: timestamp without time zone
• diseases: text
• germination_temperature: text
• introduced_into: text
• habitus: text
• medicinal_parts: text
• native_to: text
• plants_for_a_future: text
• plants_of_the_world_online_link: text
• plants_of_the_world_online_link_synonym: text
• pollination: text
• propagation_transplanting_en: text
• resistance: text
• root_type: text
• seed_planting_depth_en: text
• seed_viability: text
• slug: text
• utility: text
• when_to_plant_cuttings_en: text
• when_to_plant_division_en: text
• when_to_plant_transplant_en: text
• when_to_sow_indoors_en: text
• sowing_outdoors_en: text
• when_to_start_indoors_weeks: text
• when_to_start_outdoors_weeks: text
• cold_stratification_temperature: text
• cold_stratification_time: text
• days_to_harvest: text
• habitat: text
• spacing_en: text
• wikipedia_url: text
• days_to_maturity: text
• pests: text
• germination_time: text
• description: text
• parent_id: text
• external_id: text
• external_url: text
• root_depth: text
• external_article_number: text
• external_portion_content: text
• sowing_outdoors_de: text
• spacing_de: text
• required_quantity_of_seeds_de: text
• required_quantity_of_seeds_en: text
• seed_planting_depth_de: text
• seed_weight_1000_de: text
• seed_weight_1000_en: text
• machine_cultivation_possible: boolean
• edible_uses_de: text