Data-type composition varies across the tables of Dr. Duke's database schema

Summary

Working only from the published data dictionary of Dr. Duke's Phytochemical and Ethnobotanical Databases, I asked one pre-registered question: within this relational schema, is a column's declared data type associated with which table the column belongs to? Of the dictionary's descriptive fields, type is the only one with genuine cross-table variation — required is FALSE for every column, and size is a deterministic function of type, so both are untestable. Across the 15 tables and 199 column definitions, tables differ in their data-type mix (Monte-Carlo χ² = 62.47, p = 0.032, Cramér's V = 0.32, n = 199). The association is a moderate one that sits at the significance boundary once corrected for the three-test family (Holm p = 0.065), and it strengthens when the structurally constant integer key is removed (Holm p = 0.033). The effect is driven by a few tables: the numeric-heavy YIELDS and CHEM_MEANS (rich in Double) versus the text-heavy ETHNOBOT, FARMACY_NEW and FNFTAX. This is a descriptive finding about schema design, not a causal claim.

Provenance and methods

Source. Dr. Duke's Phytochemical and Ethnobotanical Databases — data dictionary, DOI [10.15482/USDA.ADC/1239279](https://doi.org/10.15482/USDA.ADC/1239279), released under CC0. Single file DrDukesDatabaseDataDictionary-prelim.csv (20 719 bytes, md5 ee486f85c5aecd88c1c27d08c47b88be).

Acquisition. analysis.py first attempts the canonical figshare download and verifies the md5; on any failure it falls back to a local copy, which it also md5-verifies and hard-fails on mismatch. Both paths therefore guarantee byte-identical input. (In the environment where these results were produced the canonical host was unreachable and the md5-verified local fallback was used; the download path reproduces on any machine where figshare is reachable.)

Parsing. The dictionary interleaves table-header rows (table name + RecordCount, no column) with field rows (one per column). I forward-filled the table name onto its field rows and kept the 199 field rows, mapping each table's RecordCount onto its columns. This yields 15 tables and 199 column definitions.

Fields examined and excluded. Three descriptive fields were candidates: required, size, type. required is FALSE for all 199 columns (zero variance) and size takes exactly one value per type (Short Text = 255, Double = 8, Date With Time = 8, Long Integer = 4; within-type variance = 0), so neither can serve as an independent variable. Only type (4 levels: Short Text, Double, Long Integer, Date With Time) varies non-degenerately across tables. These exclusions were established during profiling and fixed before testing.

Pre-registered tests. Primary (H1): independence of table (15 levels) and declared type (4 levels), tested with a Monte-Carlo χ² test. Monte Carlo was mandated a priori because the 15×4 table is sparse — 52 of 60 expected counts are below 5 — so the asymptotic χ² reference distribution is invalid. The null was built by permuting the type labels across columns (B = 20 000 replicates, seed 20240607) and the p-value uses the add-one estimator. Effect size: Cramér's V, reported alongside the bias-corrected V (Bergsma 2013). Two pre-registered robustness checks: R1 repeats the test after removing the structurally constant Long Integer ID key present in every table; R2 repeats it after collapsing type to a binary Text vs. Non-text distinction. The three p-values form one family, corrected together with Holm–Bonferroni. All analysis is nonparametric; every seed is set and recorded in results.json.

Data records

The parsed dictionary comprises 15 tables and 199 column definitions. Column counts per table range from 3 (SUPERACT) to 56 (YIELDS); the largest tables by declared record count are FARMACY_NEW (104 388), ETHNOBOT (82 873) and CHEMICALS (29 585). Every table contains exactly one Long Integer auto-increment ID key. The declared-type totals across all columns are: Short Text 138, Double 37, Long Integer 15, Date With Time 9. Per-table detail is in tables/tbl-2-per-table-summary.csv; the full table×type contingency is in tables/tbl-1-type-by-table-contingency.csv.

Technical validation

The primary Monte-Carlo χ² test rejects independence at the conventional 5 % threshold before correction: χ² = 62.47 (df = 42), p = 0.032, with a moderate effect size (Cramér's V = 0.32; bias-corrected V = 0.19, n = 199). The observed statistic sits in the upper tail of the permutation null (fig-3). After Holm–Bonferroni correction across the three-test family the primary result is at the boundary (p = 0.065), i.e. it does not survive multiple-comparison control on its own.

The robustness checks clarify the picture rather than merely confirm it. Removing the constant integer key (R1) strengthens the association — χ² = 52.48 (df = 28), p = 0.011, V = 0.38, n = 184, Holm p = 0.033 — showing the effect is not an artefact of the shared key column and is in fact diluted by it. Collapsing to binary Text vs. Non-text (R2) preserves a moderate effect — χ² = 23.60 (df = 14), p = 0.043, V = 0.34, n = 199 — but like the primary test lands at the corrected boundary (Holm p = 0.065). The standardized-residual map (fig-2) localizes the signal: YIELDS and CHEM_MEANS carry an excess of Double columns (adjusted residuals +3.5 and +4.1), while several small reference-style tables (REFERENCES, COMMON_NAMES, PARTS) carry an excess of date columns.

Honest reading. The evidence for a table–type association is real but modest. One of three pre-registered tests survives Holm correction (R1); the primary and binary tests sit at p ≈ 0.065 after correction. The bias-corrected effect sizes (V ≈ 0.19–0.26) place this in the small-to-moderate range. The finding should be read as "tables plausibly differ in type composition, driven by a handful of numeric-heavy measurement tables," not as a strong or universal rule.

Usage notes

These conclusions describe the schema as documented, not the database contents. The unit of analysis is a column definition (n = 199), not a data record. Because required and size are degenerate in this dictionary, no inference about field requiredness or storage sizing is possible from this file alone. The sparse contingency table is the reason Monte-Carlo inference is used throughout; anyone re-analysing should retain a resampling approach rather than the asymptotic χ². No causal mechanism is claimed — differences in type composition reflect what each table stores (measurements vs. names/text), a design choice, not an effect.

Code availability

analysis.py is self-contained and reproduces every number, figure, and table from the md5-verified input. It sets all seeds (base seed 20240607; B = 20 000 Monte-Carlo replicates), attempts the canonical figshare download with an md5-gated local fallback, runs the three pre-registered tests plus the Holm correction, and writes figures/, tables/ (with a Frictionless datapackage.json), and results.json. The exact interpreter and package versions are recorded in environment.txt.

Claims

1. Across the 15 tables and 199 column definitions of the schema, declared data type is associated with table membership (Monte-Carlo χ² = 62.47, df = 42, p = 0.032, Cramér's V = 0.32, n = 199); after Holm correction across the three-test family the primary test is at the significance boundary (p = 0.065). 2. The association is not an artefact of the shared integer key: removing the constant ID column strengthens it (χ² = 52.48, df = 28, p = 0.011, V = 0.38, n = 184; Holm p = 0.033). 3. A minimal Text vs. Non-text contrast retains a moderate association (χ² = 23.60, df = 14, p = 0.043, V = 0.34, n = 199; Holm p = 0.065). 4. The required and size fields are degenerate in this dictionary (required = FALSE for all 199 columns; size takes one value per type), so they cannot be tested and no inference about requiredness or sizing is drawn.