database_structure.md

Database structure

This document describes the SQLite database produced by digimuh-ingest. The schema follows a star schema design: small dimension tables hold entity metadata; large fact tables hold time-series measurements with foreign keys pointing back to the dimensions.

Design decisions

Why SQLite?

The dataset is large (~8.9 GB CSV, ~842 million rows) but single-user and read-heavy. SQLite handles this well: no server process, a single portable .db file, built-in date/time functions, and indexed queries that are orders of magnitude faster than scanning thousands of CSV files.

Why a star schema?

The raw data arrives as thousands of per-animal CSV files with no relational structure. A star schema gives us:

• Normalised entity storage — animal IDs, sensor names, and barn names each appear once in their dimension table rather than repeated in every row.
• Provenance — every row carries a file_id foreign key to the source_files dimension, so any datum can be traced back to its original CSV.
• Extensibility — the animals dimension can later be enriched with breed, birth date, dam/sire, etc. without touching the fact tables.

Why timestamps stay inline (not normalised)?

Timestamps are stored directly in each fact table as ISO-8601 TEXT strings. A timestamp dimension table would itself contain hundreds of millions of rows (the gouna table alone has sub-second precision) and every query would need a join through it. SQLite's built-in date(), time(), and strftime() functions operate natively on ISO-8601 text, so inline storage is both simpler and faster.

Why the EU ear tag as INTEGER PRIMARY KEY?

EU ear tag numbers are 15-digit integers that are permanent, unique, and never change. SQLite's INTEGER PRIMARY KEY is special: it becomes an alias for the internal rowid, giving the fastest possible lookup. A surrogate key would add a layer of indirection (an extra join) for zero space savings — the 8-byte integer is stored in fact tables either way.

Dimension tables

animals

Column	Type	Description
animal_id	INTEGER PRIMARY KEY	EU ear tag number (15-digit); IS the rowid

Currently contains only the ID. Future columns: breed, birth date, sex, dam/sire lineage.

sensors

Column	Type	Description
sensor_id	INTEGER PRIMARY KEY	Auto-incrementing surrogate key
sensor_name	TEXT NOT NULL UNIQUE	LoRaWAN sensor name (e.g. "CU-1")

barns

Column	Type	Description
barn_id	INTEGER PRIMARY KEY	Auto-incrementing surrogate key
barn_name	TEXT NOT NULL UNIQUE	smaXtec barn sensor name (e.g. "NewBridge")

source_files

Column	Type	Description
file_id	INTEGER PRIMARY KEY	Auto-incrementing surrogate key
filename	TEXT NOT NULL	Basename of the original CSV file
folder	TEXT NOT NULL	Containing folder name, or "(standalone)"

Unique constraint on (filename, folder).

Fact tables

Every fact table includes a file_id INTEGER NOT NULL REFERENCES source_files(file_id) column for provenance tracing.

allocations

Records which barn group/pen an animal was assigned to and when.

Column	Type	FK →
animal_id	INTEGER	animals
datetime_enter	TEXT	—
datetime_exit	TEXT	—
group	INTEGER	—
file_id	INTEGER	source_files

Source: output_allocations/allocations.csv

diseases

Health events and diagnoses from HerdePlus.

Column	Type	FK →
animal_id	INTEGER	animals
disease_first_day	TEXT	—
disease_stop_day	TEXT	—
disease_description	TEXT	—
herdeplus_tier_id	INTEGER	—
herdeplus_gesund_id	INTEGER	—
disease_category	TEXT	—
file_id	INTEGER	source_files

Source: herdeplus_diseases.csv (standalone)

herdeplus

Milking events, MLP (Milchleistungsprüfung) test-day results, and calving records from the HerdePlus herd management system.

Column	Type	FK →
animal_id	INTEGER	animals
timestamp	TEXT	—
herdeplus_milked_duration_sec	REAL	—
herdeplus_milked_milk_flow	REAL	—
herdeplus_milked_mkg	REAL	—
herdeplus_mlp_mkg	REAL	—
herdeplus_mlp_fat_percent	REAL	—
herdeplus_mlp_fkg	REAL	—
herdeplus_mlp_protein_percent	REAL	—
herdeplus_mlp_ekg_percent	REAL	—
herdeplus_mlp_lactose	REAL	—
herdeplus_mlp_cell_count	REAL	—
herdeplus_mlp_urea	REAL	—
herdeplus_mlp_f_e	REAL	—
herdeplus_mlp_lkg	REAL	—
herdeplus_mlp_ecm	REAL	—
herdeplus_calving_lactation	TEXT	—
file_id	INTEGER	source_files

Source: outputs_herdeplus_mlp_gemelk_kalbung/{animal_id}_herdeplus_*.csv

bcs

Body Condition Score assessments.

Column	Type	FK →
animal_id	INTEGER	animals
timestamp	TEXT	—
bcs_wert	REAL	—
file_id	INTEGER	source_files

Source: outputs_bcs/{animal_id}_bcs_*.csv

gouna

Respiration frequency from Gouna sensors.

Column	Type	FK →
animal_id	INTEGER	animals
timestamp	TEXT	—
respirationfrequency	REAL	—
file_id	INTEGER	source_files

Source: outputs_gouna/{animal_id}_gouna_*.csv

smaxtec_derived

Derived metrics from the smaXtec rumen bolus. This is the largest table (~824 million rows at full ingestion) with 30+ columns of computed indices covering activity, estrus detection, calving prediction, rumination, rumen pH, temperature, and motility.

Column	Type	FK →
animal_id	INTEGER	animals
timestamp	TEXT	—
act	REAL	—
act_decrease_index	REAL	—
act_estrus_index	REAL	—
act_estrus_preprocess	REAL	—
act_group_heat_index	REAL	—
act_group_ratio	REAL	—
act_index	REAL	—
act_pasture_index	REAL	—
calving_index	REAL	—
drink_cycles_v2	REAL	—
heat_index	REAL	—
in_reticulum	REAL	—
mot_period	REAL	—
mot_pulse_width	REAL	—
mot_pulse_width_median	REAL	—
ph	REAL	—
ph_under_58	REAL	—
rum_classification	TEXT	—
rum_dec_index	REAL	—
rum_index	REAL	—
temp	REAL	—
temp_dec_index	REAL	—
temp_group_ratio_svm_inc_index	REAL	—
temp_height_index	REAL	—
temp_inc_index	REAL	—
temp_limit_crossing	REAL	—
temp_normal_index	REAL	—
temp_svm_inc_index	REAL	—
temp_without_drink_cycles	REAL	—
mot_period_rum_6h_we	REAL	—
mot_period_not_rum_6h_we	REAL	—
file_id	INTEGER	source_files

Source: outputs_smaxtec_derived/{animal_id}_smaxtec_derived_*.csv

Note: Due to sparse data in early rows, some columns may be typed as TEXT by the auto-inference engine. SQLite's dynamic typing means numeric values are still stored and compared correctly regardless of the declared affinity.

smaxtec_events

Discrete reproductive and health events from smaXtec.

Column	Type	FK →
animal_id	INTEGER	animals
timestamp	TEXT	—
cow	INTEGER	—
event_type	TEXT	—
value	REAL	—
file_id	INTEGER	source_files

Source: outputs_smaxtec_events/{animal_id}_events.csv

Note: The cow column contains the animal_id redundantly (the canonical animal_id is extracted from the filename). Both are kept for completeness.

smaxtec_water_intake

Daily water intake estimates derived from rumen temperature drops.

Column	Type	FK →
animal_id	INTEGER	animals
timestamp	TEXT	—
water_intake_liter	REAL	—
file_id	INTEGER	source_files

Source: outputs_smaxtec_water_intake/{animal_id}_smaxtec_derived_*.csv

smaxtec_barns

Barn climate measurements from smaXtec barn sensors.

Column	Type	FK →
barn_id	INTEGER	barns
timestamp	TEXT	—
rawtemp	REAL	—
rawhum	REAL	—
temp	REAL	—
hum	REAL	—
temp_hum_index	REAL	—
file_id	INTEGER	source_files

Source: outputs_smaxtec_barns/{barn_name}_smaxtec_raw_*.csv

lorawan

LoRaWAN environmental sensor readings (battery and current).

Column	Type	FK →
sensor_id	INTEGER	sensors
timestamp	TEXT	—
battery_level	REAL	—
current_ampere	REAL	—
file_id	INTEGER	source_files

Source: outputs_lorawan/{sensor_name}_LoRaWAN_raw_*.csv

dwd_weather

Daily weather summaries from the Deutscher Wetterdienst (DWD).

Column	Type	FK →
dt	TEXT	—
thi_max	REAL	—
qb_thi	TEXT	—
num_values_thi	INTEGER	—
enthalpy_max	REAL	—
qb_enthalpy	TEXT	—
num_values_enthalpy	INTEGER	—
file_id	INTEGER	source_files

Source: outputs_dwd.csv (standalone)

hobo_weather

Weather station readings from HOBO loggers. Column names include sensor serial numbers from the HOBO export format.

Column	Type	FK →
datetime	TEXT	—
21136553_b_battery_v	REAL	—
21141733_1_temperature	REAL	—
21141733_2_rh	REAL	—
21141733_3_dew_point	REAL	—
21141733_b_battery_level	REAL	—
21141735_1_solar_radiation	REAL	—
21141735_b_battery_level	REAL	—
21141737_1_wetness	REAL	—
21141737_b_battery_level	REAL	—
21141734_1_wind_speed	REAL	—
21141734_2_gust_speed	REAL	—
21141734_3_wind_direction	REAL	—
21141734_b_battery_level	REAL	—
file_id	INTEGER	source_files

Source: outputs_hobo/hobo_exports_*.csv

Indexes

All indexes are created after bulk insertion to avoid write overhead.

Index	Table	Columns
idx_bcs_animal_ts	bcs	animal_id, timestamp
idx_gouna_animal_ts	gouna	animal_id, timestamp
idx_herdeplus_animal_ts	herdeplus	animal_id, timestamp
idx_smaxtec_derived_animal_ts	smaxtec_derived	animal_id, timestamp
idx_smaxtec_events_animal_ts	smaxtec_events	animal_id, timestamp
idx_smaxtec_water_animal_ts	smaxtec_water_intake	animal_id, timestamp
idx_alloc_animal	allocations	animal_id
idx_alloc_enter	allocations	datetime_enter
idx_disease_animal	diseases	animal_id
idx_disease_start	diseases	disease_first_day
idx_lorawan_sensor_ts	lorawan	sensor_id, timestamp
idx_smaxtec_barns_barn_ts	smaxtec_barns	barn_id, timestamp
idx_dwd_dt	dwd_weather	dt
idx_hobo_ts	hobo_weather	datetime
idx_source_folder	source_files	folder

The composite (entity_id, timestamp) indexes accelerate the primary query pattern: "give me all measurements for entity X between dates A and B".

Planned additions

• Lactation view combining herdeplus milking + BCS + disease windows per lactation cycle
• Materialised summary tables for daily/weekly aggregates (if view performance is insufficient)

Analysis views

The file src/digimuh/create_views.sql defines a three-layer view hierarchy. Views are created automatically when any analysis script connects to the database.

Layer 0 — hourly aggregates

View	Purpose	Grouped by
v_smaxtec_hourly	Hourly means of temp, activity, rumination, motility, pH	animal × day × hour

Layer 1 — daily summaries

These views aggregate each data source into one row per animal (or sensor/barn) per day.

View	Source table(s)	Key columns
v_smaxtec_daily	smaxtec_derived	temp (mean/min/max/range), activity, rumination, motility, pH, drinking, estrus/calving indices
v_herdeplus_daily	herdeplus	total milk yield, mean duration/flow, MLP test-day values (fat, protein, FPR, SCC, urea, lactose, ECM)
v_gouna_daily	gouna	mean/min/max respiration frequency
v_water_daily	smaxtec_water_intake	total water intake (litres)
v_bcs_latest	bcs	BCS value and assessment date
v_barn_daily	smaxtec_barns	barn temp/humidity/THI (mean and max)

Layer 2 — analysis-specific joins

Each analysis view joins the daily summaries needed for a specific research question.

View	Joins	Purpose
v_analysis_ketosis	herdeplus + smaxtec + water + diseases	FPR, rumination, milk yield, rumen pH, with disease ground truth. Includes a fpr_flag column (+1 = energy deficit, −1 = acidosis, 0 = normal) and is_sick binary from disease records.
v_analysis_heat_stress	smaxtec + water + gouna + herdeplus + DWD weather	Rumen temp, respiration, activity, water intake, milk yield, all aligned with ambient THI and enthalpy.
v_analysis_digestive	smaxtec + herdeplus + water	Motility/pH daily profiles alongside MLP test-day composition for time-lagged cross-correlation analysis.
v_analysis_circadian	smaxtec_hourly + diseases	Hourly temp/activity/rumination profiles per animal-day with disease status for circadian rhythm analysis.
v_analysis_motility	smaxtec_derived (minimal aggregation)	Raw motility series (contraction intervals, pulse widths) with concurrent pH, rumination, and temperature for entropy computation.

Example queries using views

-- Daily milk yield vs. heat load for a specific cow
SELECT day, milk_yield_kg, rumen_temp_mean, dwd_thi_max, water_liter
FROM v_analysis_heat_stress
WHERE animal_id = 276001260919234
ORDER BY day;

-- MLP test days with FPR above ketosis threshold
SELECT animal_id, day, mlp_fpr, rum_index_mean, milk_yield_kg, disease_category
FROM v_analysis_ketosis
WHERE fpr_flag = 1
ORDER BY day;

-- Hourly temperature profile for circadian analysis
SELECT hour, AVG(temp_clean_mean) AS temp, AVG(act_index_mean) AS activity
FROM v_analysis_circadian
WHERE animal_id = 276001260919234
  AND day = '2023-07-15'
GROUP BY hour
ORDER BY hour;