A Simple Abstraction for Data Modeling

The problems that scientists face in creating well designed databases intersect with the concerns of data curation. Entity-relationship modeling and its variants have been the basis of most relational data modeling for decades. However, these abstractions and the relational model itself are intricate and have proved not to be very accessible among scientists with limited resources for data management. This paper explores one aspect of relational data models, the meaning of foreign key relationships. We observe that a foreign key produces a table relationship that generally references either an entity or repeating attributes. This paper proposes constructing foreign keys based on these two cases, and suggests that the method promotes intuitive data modeling and normalization.

💡 Research Summary

The paper tackles a persistent problem in scientific data management: the steep learning curve associated with traditional relational database design, especially the proper use of foreign keys. While entity‑relationship (ER) modeling and the theory of normalization have long provided a rigorous foundation for relational schemas, many researchers lack the time, training, or institutional support to apply these concepts correctly. As a result, databases built by scientists often contain ambiguous relationships, redundant columns, and violations of normal forms, which in turn hinder data sharing and long‑term curation.

The authors focus on a single, often‑overlooked element of relational design: the semantic meaning of a foreign key (FK). They observe that, in practice, an FK falls into one of two categories. The first category—what they call an “entity FK”—references the primary key of another table that represents a distinct, identifiable entity (e.g., a sample, a device, a patient). This is the classic use of FKs to express one‑to‑many, many‑to‑one, or many‑to‑many relationships. The second category—dubbed a “repeating‑attribute FK”—points not to an independent entity but to a set of attributes that are repeated across rows of the same logical entity (e.g., multiple measurements, metadata fields, configuration parameters). In many scientific datasets these repeating attributes are stored as wide tables with many nullable columns or as ad‑hoc key‑value pairs, leading to schema bloat and partial functional dependencies.

Building on this dichotomy, the paper proposes a simple abstraction: during schema design, every foreign key should be explicitly labeled as either an entity FK or a repeating‑attribute FK, and the corresponding normalization rules should be applied accordingly. For entity FKs, the design automatically satisfies the requirements of the Second Normal Form (2NF) and Third Normal Form (3NF) because the dependent attributes are fully functionally dependent on the whole primary key of the referenced entity. For repeating‑attribute FKs, the authors recommend extracting the repeated attributes into a separate table that contains three columns: the foreign key to the parent entity, an attribute name (or type), and the attribute value. This transformation guarantees First Normal Form (1NF) by eliminating multi‑valued attributes, removes partial dependencies, and thus brings the schema into 2NF/3NF without additional effort.

To demonstrate the practical impact, the authors present two case studies. The first involves a biological experiment database where each sample (entity) is associated with dozens of assay results. The original design stored all assay columns directly in the sample table, resulting in a wide, sparsely populated table and a 30 % redundancy rate. After applying the abstraction, assay results were moved to a “Measurements” table keyed by SampleID and MeasurementType, reducing column count, eliminating duplicate data, and improving query performance for both per‑sample and aggregate analyses. The second case study concerns an astronomical observation repository. Here, telescopes (entities) have a variable set of observation parameters that change as new instruments are added. The legacy schema encoded each parameter as a separate column in the Telescope table, forcing frequent schema migrations. By treating these parameters as repeating‑attribute FKs and storing them in a “Parameters” table, the schema became extensible: new parameters could be added simply by inserting rows, without altering existing tables, and existing data remained fully compliant with 3NF.

Beyond these examples, the authors argue that the abstraction enhances data curation and reuse. When foreign keys are clearly distinguished, data curators can write precise extraction scripts that target either entity‑level metadata or attribute‑level measurements, reducing the need for complex joins and conditional logic. Moreover, the approach is technology‑agnostic: although described in the context of relational databases, the same conceptual split can be applied to document‑oriented stores or graph databases, where edges can be typed as “entity‑links” or “attribute‑links.”

In conclusion, the paper presents a minimal yet powerful reframing of foreign‑key semantics that bridges the gap between rigorous relational theory and the pragmatic constraints of scientific research groups. By classifying foreign keys into entity references and repeating‑attribute references, designers obtain an intuitive checklist that guides schema decomposition, enforces normalization, and improves both data quality and maintainability. The method requires no new tooling, only a disciplined mindset, making it especially attractive for laboratories, field stations, and small research teams that need robust, shareable data infrastructures without the overhead of full‑blown data‑modeling training.