How each table looks (and how they connect)

farms (parent table)

fields (each field belongs to a farm through farm_id)

treatments (reference table for treatment definitions)

trial_plots (design/event table linking field_id + treatment_id + season_year)

harvests (event table linked to trial_plots by plot_id)

weather_daily (event table linked to farms by farm_id)

Link summary

• fields.farm_id links with farms.farm_id.
• trial_plots.field_id links with fields.field_id.
• trial_plots.treatment_id links with treatments.treatment_id.
• harvests.plot_id links with trial_plots.plot_id.
• weather_daily.farm_id links with farms.farm_id.

The core idea is that trial_plots stores treatment assignment at plot level, harvests stores measured outcomes for those plots, and weather_daily connects environmental context to the same farms.

Step A define table grain first

Grain means “what one row represents”.

Entity tables (farms, fields, treatments) one row per entity. Design/event tables (trial_plots, harvests, weather_daily) one row per assignment or time based observation.

If grain is unclear, everything after that gets messy (duplicates, wrong joins, wrong counts).

Figure. Quick grain and uniqueness map showing what one row represents and the matching UNIQUE(...) rule.

Step B choose primary keys and foreign keys

Use the same linking variables shown above and enforce them with primary keys (IDs in parent tables) and foreign keys (matching IDs in child/event tables). This gives you safe joins and enforces referential integrity. Performance note (PostgreSQL): foreign keys enforce integrity, but they do not automatically create indexes on the referencing columns. For large farm/event tables, it is often a good idea to index the foreign key columns you join on.

Step C define business uniqueness with constraints

Primary keys identify rows technically, but you also need domain level uniqueness.

Examples for this project

• in trial_plots, you usually expect one row per field_id + treatment_id + season_year + replicate_no.
• in harvests, you usually expect one row per plot_id.
• in weather_daily, you usually expect one row per farm_id + day.

That can be enforced with unique constraints

ALTER TABLE trial_plots
ADD CONSTRAINT uq_trialplot_field_treat_year_rep
UNIQUE (field_id, treatment_id, season_year, replicate_no);

ALTER TABLE harvests
ADD CONSTRAINT uq_harvest_plot
UNIQUE (plot_id);

ALTER TABLE weather_daily
ADD CONSTRAINT uq_weather_farm_day
UNIQUE (farm_id, day);

This protects your analysis from duplicate event records.

Step D pick data types and validation rules

Use types and constraints to prevent bad data early.

• DATE for dates (harvest_date, day).
• numeric types for quantities (yield_t_ha, rainfall_mm, area_ha).
• NOT NULL for required fields.
• CHECK for physical logic (for example non negative yield_t_ha).

Your schema is the first quality control layer.

Step E separate raw ingestion from clean analytics

A practical production pattern

1. Load external files into staging tables.
2. Validate and standardize.
3. Insert into core relational tables (farms, fields, trial_plots, harvests, weather_daily).
4. Build analysis views/CTEs from the clean core.

This keeps raw import problems away from final reporting.

Step F design for change

Field trial data evolves. Plan for that.

A practical habit is to sketch the schema on a piece of paper first. Writing it by hand is often the best way to think through table grain and links before coding.

Figure. Extension ready schema sketch with added event tables (for example pest_scouting and soil_tests).

New treatment levels can be added without redesigning trial_plots or harvests. new farms/fields can be inserted without touching historical records. additional event tables (for example pest scouting, soil tests) can be linked by keys.

Good schema design makes future extension easier and safer.

Mini design checklist (farm trial version)

Is each table grain explicit? Are primary and foreign keys defined? Are business level uniqueness constraints present? Are required columns NOT NULL where appropriate? Are sanity checks (CHECK) defined for critical numeric values? Can you answer your main trial questions with straightforward joins?

Query A specific columns

SELECT farm_id, farm_name, region
FROM farms;

Line by line

1. SELECT farm_id, farm_name, region choose exactly the columns you want.
2. FROM farms read rows from the farms table.

Expected result (illustrative)

Query B all columns with *

SELECT *
FROM treatments;

Line by line

1. SELECT * return all columns in the table.
2. FROM treatments source table.

Expected result (illustrative)

Note that SELECT * is fine for quick exploration. For analysis scripts, prefer explicit columns so your query is stable if table structure changes.

Query C numeric filter

SELECT field_id, field_name, area_ha
FROM fields
WHERE area_ha >= 12.0;

Line by line

1. SELECT ... keep only key columns for this question.
2. FROM fields table with field attributes.
3. WHERE area_ha >= 12.0 keep only fields at least 12 hectares.

Expected result (illustrative)

Query D year + date + AND

SELECT
    h.harvest_id,
    p.field_id,
    p.season_year,
    h.yield_t_ha,
    h.harvest_date
FROM harvests h
JOIN trial_plots p
    ON h.plot_id = p.plot_id
WHERE p.season_year = 2025
  AND h.harvest_date >= '2025-10-10';

Line by line

1. p.season_year = 2025 keep records from 2025 season.
2. AND both conditions must be true.
3. h.harvest_date >= '2025-10-10' keep later harvest dates.

Expected result (illustrative)

(…more rows match this filter in the sample data.)

Query E OR condition

SELECT farm_id, farm_name, region
FROM farms
WHERE region = 'North'
   OR region = 'South';

Line by line

1. region = 'North' OR region = 'South' either condition can match.

Expected result (illustrative)

A common mistake warning would be that OR can return many more rows than expected. Always sanity check row counts after broad filters.

PostgreSQL vs Oracle notes (WHERE)

Text comparison with = works similarly in both. Date literals are safest as DATE 'YYYY MM DD' in Oracle. PostgreSQL accepts 'YYYY MM DD' directly for a DATE column.

Query F top yields

SELECT
    h.harvest_id,
    p.field_id,
    p.season_year,
    h.yield_t_ha
FROM harvests h
JOIN trial_plots p
    ON h.plot_id = p.plot_id
ORDER BY yield_t_ha DESC
LIMIT 5;

Line by line

1. ORDER BY yield_t_ha DESC sort from highest yield to lowest.
2. LIMIT 5 keep only first 5 rows after sorting.

Expected result (illustrative)

PostgreSQL vs Oracle notes (ORDER BY/LIMIT)

PostgreSQL LIMIT 5. Oracle FETCH FIRST 5 ROWS ONLY (after ORDER BY).

Query G average yield by treatment

SELECT
    t.treatment_name,
    COUNT(*) AS n_harvests,
    ROUND(AVG(h.yield_t_ha), 2) AS avg_yield_t_ha,
    ROUND(SUM(h.yield_t_ha), 2) AS total_yield_t_ha
FROM harvests h
JOIN trial_plots p
    ON h.plot_id = p.plot_id
JOIN treatments t
    ON p.treatment_id = t.treatment_id
GROUP BY t.treatment_name
ORDER BY avg_yield_t_ha DESC;

Line by line

1. COUNT(*) number of rows in each group.
2. AVG(...) average yield in each group.
3. SUM(...) total yield in each group.
4. JOIN trial_plots recover treatment assignment for each harvested plot.
5. JOIN treatments bring treatment names.
6. GROUP BY t.treatment_name one summary row per treatment.
7. ORDER BY avg_yield_t_ha DESC best average first.

Expected result (illustrative, computed from the sample inserts)

Do not rank treatments using only avg_yield_t_ha. Also check n_harvests because very small sample sizes can look “best” by chance. In this toy dataset, Nitrogen_High has high mean but only 2 rows, so uncertainty is larger. In real programs, always inspect balance before drawing conclusions.

Query H HAVING after GROUP BY

SELECT
    p.season_year,
    COUNT(*) AS n_records,
    ROUND(AVG(h.yield_t_ha), 2) AS avg_yield
FROM harvests h
JOIN trial_plots p
    ON h.plot_id = p.plot_id
GROUP BY p.season_year
HAVING AVG(h.yield_t_ha) > 6.0
ORDER BY p.season_year;

Line by line

1. GROUP BY season_year one row per season.
2. HAVING AVG(yield_t_ha) > 6.0 filter groups after aggregation.
3. WHERE filters rows before grouping; HAVING filters grouped results.

Expected result (illustrative, computed from the sample inserts)

Figure. Join row explosion many to many joins multiply rows and aggregate to matching grain before joining.

Query I INNER JOIN (matched rows only)

SELECT
    h.harvest_id,
    f.field_name,
    fm.farm_name,
    t.treatment_name,
    p.season_year,
    h.yield_t_ha
FROM harvests h
INNER JOIN trial_plots p
    ON h.plot_id = p.plot_id
INNER JOIN fields f
    ON p.field_id = f.field_id
INNER JOIN farms fm
    ON f.farm_id = fm.farm_id
INNER JOIN treatments t
    ON p.treatment_id = t.treatment_id
ORDER BY h.harvest_id;

Line by line

1. Start from harvests because that is the main event table.
2. Join to trial_plots to recover assignment metadata.
3. Join to fields using field_id.
4. Join to farms through fields.
5. Join to treatments using treatment_id.
6. Result one rich analysis table with names instead of only IDs.

Expected result (illustrative)

Query J LEFT JOIN (keep all fields even if no 2026 harvest)

SELECT
    f.field_id,
    f.field_name,
    h.harvest_id,
    p.season_year,
    h.yield_t_ha
FROM fields f
LEFT JOIN trial_plots p
    ON f.field_id = p.field_id
   AND p.season_year = 2026
LEFT JOIN harvests h
    ON p.plot_id = h.plot_id
ORDER BY f.field_id;

Line by line

1. Left table is fields, so every field must appear.
2. Right side starts with trial_plots filtered to 2026 in the ON clause.
3. Since we have no 2026 rows, plot/harvest columns become NULL.
4. This is useful to detect missing records.

Expected result (illustrative)

Common mistake warning

If you put p.season_year = 2026 in WHERE instead of ON, you can accidentally turn your LEFT JOIN into INNER JOIN behavior.

Query K build a clean table for yield analysis

WITH weather_by_farm_year AS (
    SELECT
        farm_id,
        EXTRACT(YEAR FROM day)::INT AS season_year,
        ROUND(SUM(rainfall_mm), 2) AS total_rain_mm,
        ROUND(AVG(tmean_c), 2) AS avg_tmean_c
    FROM weather_daily
    GROUP BY farm_id, EXTRACT(YEAR FROM day)
),
analysis_table AS (
    SELECT
        h.harvest_id,
        p.plot_id,
        p.season_year,
        h.harvest_date,
        h.yield_t_ha,
        f.field_id,
        f.field_name,
        f.area_ha,
        f.soil_type,
        fm.farm_id,
        fm.farm_name,
        fm.region,
        t.treatment_id,
        t.treatment_name,
        t.fertilizer_kg_ha,
        w.total_rain_mm,
        w.avg_tmean_c
    FROM harvests h
    JOIN trial_plots p
        ON h.plot_id = p.plot_id
    JOIN fields f
        ON p.field_id = f.field_id
    JOIN farms fm
        ON f.farm_id = fm.farm_id
    JOIN treatments t
        ON p.treatment_id = t.treatment_id
    LEFT JOIN weather_by_farm_year w
        ON fm.farm_id = w.farm_id
       AND p.season_year = w.season_year
)
SELECT *
FROM analysis_table
ORDER BY harvest_id;

Line by line

1. weather_by_farm_year aggregate weather from daily to farm year level.
2. analysis_table join harvest + field + farm + treatment + weather summary.
3. LEFT JOIN with weather keep harvest rows even if weather summary is missing.
4. Final SELECT * FROM analysis_table inspect the clean combined dataset.

Expected result (illustrative)

PostgreSQL vs Oracle notes (CTE)

CTE syntax is very similar. PostgreSQL casting often uses INT; Oracle uses CAST(... AS NUMBER) style.

Query L mean, SD, min, max, median by treatment

SELECT
    t.treatment_name,
    COUNT(*) AS n,
    ROUND(AVG(h.yield_t_ha), 2) AS mean_yield,
    ROUND(STDDEV_SAMP(h.yield_t_ha), 3) AS sd_yield,
    ROUND(MIN(h.yield_t_ha), 2) AS min_yield,
    ROUND(MAX(h.yield_t_ha), 2) AS max_yield,
    ROUND(
      (PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY h.yield_t_ha))::NUMERIC,
      2
    ) AS median_yield
FROM harvests h
JOIN trial_plots p
    ON h.plot_id = p.plot_id
JOIN treatments t
    ON p.treatment_id = t.treatment_id
GROUP BY t.treatment_name
ORDER BY mean_yield DESC;

Line by line

1. AVG arithmetic mean.
2. STDDEV_SAMP sample standard deviation.
3. MIN and MAX range endpoints.
4. PERCENTILE_CONT(0.5) WITHIN GROUP median.
5. Grouping by treatment gives one summary row per treatment.

Expected result (illustrative, computed from the sample inserts)

PostgreSQL vs Oracle notes (stats)

AVG, MIN, MAX, COUNT are equivalent. STDDEV_SAMP exists in both. PERCENTILE_CONT exists in both, but formatting/casting style differs slightly.

Query M find missing temperature rows

SELECT weather_id, farm_id, day, rainfall_mm, tmean_c
FROM weather_daily
WHERE tmean_c IS NULL
ORDER BY weather_id;

Line by line

1. IS NULL is the correct way to test missing values.
2. tmean_c = NULL is wrong and will not work as expected.

Expected result (illustrative)

Query N replace NULL with fallback value

SELECT
    weather_id,
    farm_id,
    day,
    rainfall_mm,
    COALESCE(tmean_c, 20.0) AS tmean_filled_c
FROM weather_daily
ORDER BY weather_id;

Line by line

1. COALESCE(tmean_c, 20.0) if tmean_c is NULL, use 20.0.
2. Very useful for simple reporting, but be careful for scientific analysis.

Expected result (illustrative)

Warning: replacing NULL can hide data quality issues. For inference models, decide imputation strategy carefully.

PostgreSQL vs Oracle notes (NULL)

COALESCE works in both. Oracle also has NVL, which is Oracle specific.

Query O duplicate checks in trial_plots

SELECT
    field_id,
    treatment_id,
    season_year,
    replicate_no,
    COUNT(*) AS n_rows
FROM trial_plots
GROUP BY field_id, treatment_id, season_year, replicate_no
HAVING COUNT(*) > 1;

Line by line

1. Group by the key combination that should be unique.
2. HAVING COUNT(*) > 1 returns only duplicate combinations.

Expected result (illustrative)

Query P impossible values

SELECT
    h.harvest_id,
    p.field_id,
    p.treatment_id,
    p.season_year,
    h.yield_t_ha,
    h.harvest_date
FROM harvests h
JOIN trial_plots p
    ON h.plot_id = p.plot_id
WHERE h.yield_t_ha < 0
   OR h.yield_t_ha > 20;

Line by line

1. Yield lower than 0 is impossible.
2. Yield above 20 t/ha may be impossible for this crop context.
3. These thresholds depend on your agronomic reality.

Expected result (illustrative)

Query Q outlier flags using IQR rule

WITH q AS (
    SELECT
      PERCENTILE_CONT(0.25) WITHIN GROUP (ORDER BY yield_t_ha) AS q1,
      PERCENTILE_CONT(0.75) WITHIN GROUP (ORDER BY yield_t_ha) AS q3
    FROM harvests
),
bounds AS (
    SELECT
      q1,
      q3,
      (q3 - q1) AS iqr,
      (q1 - 1.5 * (q3 - q1)) AS lower_bound,
      (q3 + 1.5 * (q3 - q1)) AS upper_bound
    FROM q
)
SELECT
    h.harvest_id,
    p.field_id,
    p.season_year,
    h.yield_t_ha,
    b.lower_bound,
    b.upper_bound
FROM harvests h
JOIN trial_plots p
    ON h.plot_id = p.plot_id
CROSS JOIN bounds b
WHERE h.yield_t_ha < b.lower_bound
   OR h.yield_t_ha > b.upper_bound
ORDER BY h.yield_t_ha;

Line by line

1. First CTE computes quartiles (q1, q3).
2. Second CTE computes IQR and outlier bounds.
3. Final query flags rows outside bounds.

Expected result (illustrative)

Important interpretation note

Outlier flag does not mean “wrong”. It means “investigate”.

Query R field season control adjusted gains

WITH baseline AS (
    SELECT
        p.field_id,
        p.season_year,
        AVG(h.yield_t_ha) AS control_yield_t_ha
    FROM harvests h
    JOIN trial_plots p
      ON h.plot_id = p.plot_id
    JOIN treatments t
      ON p.treatment_id = t.treatment_id
    WHERE t.fertilizer_kg_ha = 0
    GROUP BY p.field_id, p.season_year
),
comparisons AS (
    SELECT
        fm.region,
        fm.farm_name,
        f.field_name,
        p.field_id,
        p.season_year,
        t.treatment_name,
        t.fertilizer_kg_ha,
        h.yield_t_ha,
        b.control_yield_t_ha,
        (h.yield_t_ha - b.control_yield_t_ha) AS gain_vs_control_t_ha
    FROM harvests h
    JOIN trial_plots p
      ON h.plot_id = p.plot_id
    JOIN treatments t
      ON p.treatment_id = t.treatment_id
    JOIN baseline b
      ON p.field_id = b.field_id
     AND p.season_year = b.season_year
    JOIN fields f
      ON p.field_id = f.field_id
    JOIN farms fm
      ON f.farm_id = fm.farm_id
    WHERE t.fertilizer_kg_ha > 0
)
SELECT
    region,
    treatment_name,
    COUNT(*) AS n_field_seasons,
    AVG(gain_vs_control_t_ha) AS avg_gain_t_ha,
    MIN(gain_vs_control_t_ha) AS min_gain_t_ha,
    MAX(gain_vs_control_t_ha) AS max_gain_t_ha
FROM comparisons
GROUP BY region, treatment_name
ORDER BY avg_gain_t_ha DESC;

Line by line

1. baseline one control value per field season.
2. comparisons compute gain = treatment yield control yield for same field season.
3. Final aggregation by region and treatment_name.
4. We report average and range of gains.

Expected result (illustrative, computed from the sample inserts)

Interpretation would be that higher fertilizer rates show larger gains, especially in the North. This is still a simple within-field comparison inside a toy dataset.
If your trial is randomized, difference vs control is a reasonable estimator, but missingness and design imbalance can still bias summaries.

Note that confounding means other factors may influence both treatment assignment and yield (for example soil class, rainfall timing, pest pressure, planting date). Even with field season control subtraction, we are not proving causality.

Query S treatment representation by region and season

SELECT
    fm.region,
    p.season_year,
    t.treatment_name,
    COUNT(*) AS n_plots
FROM harvests h
JOIN trial_plots p
    ON h.plot_id = p.plot_id
JOIN fields f
    ON p.field_id = f.field_id
JOIN farms fm
    ON f.farm_id = fm.farm_id
JOIN treatments t
    ON p.treatment_id = t.treatment_id
GROUP BY fm.region, p.season_year, t.treatment_name
ORDER BY fm.region, p.season_year, t.treatment_name;

Line by line

1. We count how many harvested plots appear for each treatment in each region season.
2. This is not a yield summary; it is a design summary.
3. If counts are highly unbalanced, interpret treatment means with caution.

Expected result (illustrative)

Query T area weighted average yield by treatment

SELECT
    t.treatment_name,
    ROUND(
      SUM(h.yield_t_ha * f.area_ha) / NULLIF(SUM(f.area_ha), 0),
      2
    ) AS area_weighted_mean_yield_t_ha
FROM harvests h
JOIN trial_plots p
    ON h.plot_id = p.plot_id
JOIN fields f
    ON p.field_id = f.field_id
JOIN treatments t
    ON p.treatment_id = t.treatment_id
GROUP BY t.treatment_name
ORDER BY area_weighted_mean_yield_t_ha DESC;

Line by line

1. Multiply each yield by field area (yield * area).
2. Divide by total area for that treatment.
3. This gives an area weighted mean, which can be more realistic for farm level decisions.

Unweighted means treat each field equally. Weighted means give more influence to larger fields. Neither is always “right”; the right choice depends on your decision question. If you are making logistics or tonnage decisions, weighted summaries are often more relevant.

Trial note: in real field experiments, weighting should usually use plot area (or equal weights per plot/replicate), not the whole field area.
Here we use field.area_ha only as a simple demo of weighted means.

Query U UPDATE to correct values

UPDATE harvests
SET yield_t_ha = 6.95
WHERE harvest_id = 11;

Line by line

1. UPDATE harvests target table to modify.
2. SET yield_t_ha = 6.95 new value.
3. WHERE harvest_id = 11 only this row.

Important warning would be that never run UPDATE without WHERE unless you truly want to modify all rows.

Query V DELETE to remove wrong rows

DELETE FROM harvests
WHERE harvest_id = 999;

Line by line

1. DELETE FROM remove rows from table.
2. WHERE ... define exactly which rows to remove.

Another important warning would be that always run the matching SELECT first to verify which rows will be deleted.

Query W INSERT ... SELECT to load transformed data

Assume weather_daily_staging is a temporary/staging table you created earlier (for example from a CSV import).

INSERT INTO weather_daily (farm_id, day, rainfall_mm, tmean_c)
SELECT
    farm_id,
    day,
    rainfall_mm,
    tmean_c
FROM weather_daily_staging;

Line by line

1. Target columns are listed explicitly.
2. Source rows come from another table or query.
3. This is very common in ETL pipelines.

Query X upsert in PostgreSQL (INSERT ... ON CONFLICT)

INSERT INTO treatments (treatment_id, treatment_name, fertilizer_kg_ha)
OVERRIDING SYSTEM VALUE
VALUES (4, 'Nitrogen_High', 180)
ON CONFLICT (treatment_id)
DO UPDATE SET
    treatment_name = EXCLUDED.treatment_name,
    fertilizer_kg_ha = EXCLUDED.fertilizer_kg_ha;

Line by line

1. Try to insert a row.
2. If key already exists (treatment_id), update instead.
3. Useful when syncing reference tables.

Note: OVERRIDING SYSTEM VALUE is needed here because treatment_id is an identity column defined as GENERATED ALWAYS.

Note that PostgreSQL commonly uses ON CONFLICT. Oracle workflows often use MERGE for similar behavior.

Query Y CASE WHEN for recoding inside queries

SELECT
    field_id,
    field_name,
    area_ha,
    CASE
        WHEN area_ha >= 12 THEN 'large_field'
        WHEN area_ha >= 10 THEN 'medium_field'
        ELSE 'small_field'
    END AS field_size_class
FROM fields;

Line by line

1. CASE creates a derived category.
2. Conditions are evaluated from top to bottom.
3. Very useful for reporting and rule based labels.

Query Z NOT EXISTS for missing link checks

SELECT
    f.field_id,
    f.field_name
FROM fields f
WHERE NOT EXISTS (
    SELECT 1
    FROM trial_plots p
    JOIN harvests h
      ON h.plot_id = p.plot_id
    WHERE p.field_id = f.field_id
);

Line by line

1. Start from all fields.
2. Keep only fields with no matching harvest rows.
3. This is one of the best QA patterns for relational data.

Safe workflow transactions for batch edits

Figure. Safe edit timeline start transaction, make changes, check, then COMMIT or ROLLBACK.

BEGIN;

UPDATE harvests
SET yield_t_ha = yield_t_ha + 0.10
WHERE plot_id IN (
  SELECT plot_id
  FROM trial_plots
  WHERE season_year = 2025
    AND field_id IN (1, 2)
);

-- Check your result before finalizing:
SELECT
    h.*
FROM harvests h
JOIN trial_plots p
  ON h.plot_id = p.plot_id
WHERE p.season_year = 2025
  AND p.field_id IN (1, 2);

-- If correct:
COMMIT;

-- If not correct:
-- ROLLBACK;

For any non trivial UPDATE or DELETE, use a transaction. It gives you a safety net while learning and in production.