Database Plant Trait Normalization

Status: Decided

Assignee: Christoph Schreiner

Problem

All plant data lives in a single, fully denormalized plants table. One row mixes three different kinds of data:

  • Identity: id, unique_name, common_name_en, common_name_de
  • Taxonomic hierarchy: rank, self-referential foreign keys family, genus, species, variety
  • Traits: ~15 mostly-nullable attribute columns such as shade, light_requirement, water_requirement, soil_texture, herbaceous_or_woody, life_cycle, etc.

The traits could be split out of plants into a separate table - either as a plain 1:1 side table or in a genuinely normalized form (storing each value only once instead of flattened per row).

Why the hierarchy is denormalized is already decided in database_plant_hierarchy.md and is out of scope here. This decision is only about the traits: there is no decision explaining why they live directly on plants. It records why that denormalized design was chosen and which assumptions would have to change for us to reconsider.

Example: a wide, sparse table

  • Every row carries all 15+ trait columns regardless of rank. Current fill ratios range from ~5 % for harvest_time to ~78 % for spread, so a lot of plants store many NULLs.
  • Because the scraper flattens inheritance into every row, a trait value (e.g. water_requirement) is also copied into every descendant that inherits it. This redundancy follows from the (already decided) hierarchy denormalization, and a 1:1 trait table would not remove it.

Constraints

  • The scraper owns the data. Plants are produced by the scraper pipeline and inserted/rebuilt in bulk. It is accepted that plants can only be inserted and updated by running the scraper pipeline; they are not edited live.
  • The backend only reads plants. No backend code writes to it. The read interface should be preserved - which also means the table can later be replaced by a view without backend changes.
  • Overrides at any rank. The design must let attributes be defined and overridden on any taxonomic rank.
  • PostgreSQL + Diesel. The stack is PostgreSQL with the Diesel ORM (database.md).
  • Traits stay available per plant. The scraper-resolved trait values must remain readable for each plant regardless of how they are stored.

Assumptions

  • plants stays effectively read-only and scraper-rebuilt; we will not need fine-grained, live, per-trait writes from users or the backend.
  • The number and width of trait columns stays manageable; a wide table with many nullable columns is acceptable.
  • Query performance and Rust/Diesel compile times remain acceptable with the wide table.
  • Should we ever decide to normalize, doing it later is cheap: because the backend only reads plants, we can introduce a trait table and expose it through a plants view without breaking the backend.

Solutions

Alternative A - Keep traits as columns on plants (status quo)

Keep identity, hierarchy references and all traits in one table.

Pros:

  • Simplest possible queries: reading a plant and its traits needs no join.
  • No backend/ORM changes; nothing to migrate.
  • Consistent with the scraper writing one flat row per plant.

Cons:

  • Wide, mostly-nullable table.
  • A new or dropped trait requires a schema change on the plants table.

Alternative B - Split traits into a 1:1 plant_traits side table (vertical partitioning)

Keep identity + hierarchy in plants; move the trait columns into a plant_traits table keyed 1:1 by plant_id.

Pros:

  • plants becomes narrow (identity + hierarchy); traits are isolated.
  • Smaller core rows might provide compile-/query-speed benefit (!1930).
  • Schema stays type-safe (typed columns), unlike a JSONB blob (see backend_JSON.md).

Cons:

  • Adds a mandatory join to the common case: plant details, search and the heatmap all read traits together with the plant.
  • A 1:1 split gives no protection against update/insert/delete anomalies (there is exactly one trait row per plant), so it buys almost none of the usual normalization benefit.
  • Does not remove the inherited-attribute redundancy - traits are still flattened per row.
  • Backend and ORM models must change.

Alternative C - Store traits in a JSONB column

Replace the trait columns with a single plants.extra_properties JSONB column.

Pros:

  • New traits need no schema change.
  • Avoids many nullable columns.

Cons:

  • Inconsistent with backend_JSON.md, which decided to phase JSONB out in favour of typed columns.
  • Loses type safety, constraints and indexing that typed columns give for free.
  • When filtering by traits typed columns would perform better. Currently we don't filter based on traits, but some planned use cases would require us to (e.g. plant suggestions).

Alternative D - Store each trait only at its override rank, resolve inheritance in a view

Stop flattening inherited values into every row. Store a trait value only on the rank that actually defines/overrides it, and reconstruct the effective per-plant value at read time by walking up the hierarchy (COALESCE from cultivar up to family) in a recursive view or function.

Pros:

  • Actually removes the inherited-attribute redundancy: each value is stored exactly once, at its definition rank.
  • Setting a value for a whole family becomes a single-row change.

Cons:

  • Reintroduces the runtime inheritance resolution that database_plant_hierarchy.md deliberately moved into the scraper - more complex and slower reads.
  • Tightly coupled to the hierarchy storage, so it overlaps the (already decided) hierarchy denormalization.
  • The scraper would still have to determine the override points.

Alternative E - Normalized write model + denormalized read view

Make a separate trait store (e.g. a 1:1 side table, or values stored once per rank as in D) the source of truth that the scraper writes to, but expose today's flat plants shape through a (materialized) view so backend reads stay unchanged.

Pros:

  • A clean, type-safe write model while reads stay flat and join-free - the backend is untouched because the view preserves the plants interface.
  • This is also the migration path that makes any later normalization non-breaking.

Cons:

  • A plain view re-adds the joins on every read; a materialized view needs a refresh step after each scraper run plus extra storage.
  • Two representations to keep in sync (separate tables + view), more moving parts.
  • Does not remove redundancy unless combined with D.

Decision

We keep Alternative A: plant traits stay as typed columns on plants. We do not split them out now.

We revisit this decision only if one of these assumptions breaks:

  1. plants stops being read-only / scraper-rebuilt - users or the backend need to edit individual traits live or perform partial updates → a normalized, write-friendly schema becomes valuable.
  2. The table grows too wide or too sparse - many new, rarely-filled trait groups → a plant_traits side table (Alternative B) reduces width and NULLs.
  3. Measured query performance or Rust/Diesel compile times degrade because of the wide table - the compile-/speed argument from !1930 was never measured; a real measured regression would justify Alternative B.
  4. We need 1:N traits per plant, or per-source / per-region provenance → traits must move into their own 1:N table.

Because the backend only reads plants, any of these can be addressed later via a migration that introduces the new table(s) and re-creates plants as a view (Alternative E) - so there is no need to take on the work or the risk now.

Rationale

  • Normalization mostly pays off on writes, by preventing update/insert/delete anomalies and redundancy. The plants table is written only by full scraper runs and is otherwise read-only, so that core benefit is largely irrelevant.
  • Traits are read together with the plant. Plant details, search and the heatmap all read the traits with the plant, so the 1:1 split (B) adds a join to the common case while adding no anomaly protection.
  • The only option that truly removes the redundancy (D) is the most expensive. It reintroduces the runtime inheritance resolution the hierarchy decision deliberately avoided, and reaches into that out-of-scope decision.
  • Deferring is cheap and reversible. Because the backend only reads plants, we can later introduce separate tables behind a plants view (E) without breaking the backend - we lose little by waiting, and avoid backend rewrites, extra ORM models and release risk now.
  • Drawbacks we accept: a wide, mostly-NULL table, and adding or dropping a trait is a schema change on the central plants table.

Implications

  • No schema, backend or ORM change now; no migration.
  • The reconsider-triggers above are the contract for future contributors: if one is hit, reopen this decision.
  • If we later normalize, the migration should re-create plants as a view (Alternative E) to preserve the backend read interface.
  • Database plant hierarchy - the hierarchy denormalization (out of scope here, but the reason traits are flattened per row).
  • Use of JSON/JSONB in the database - normalized columns vs JSONB; the plant_traits side-table sketch originated there as an illustration, not a proposal.
  • Database - PostgreSQL + Diesel, which constrain the viable options.

References

  • https://issues.permaplant.net/2513
  • https://issues.permaplant.net/1835
  • https://issues.permaplant.net/1835#note_293297
  • https://pulls.permaplant.net/1930#note_278621