Entity-Relationship (ER) modelling is the design process that translates real-world business concepts into a relational schema before a single line of SQL is written. An ER diagram defines entities (things the business cares about — players, matches, venues), attributes (properties of those entities), and relationships (how entities are connected to each other). Normalisation is the complementary process that restructures a schema to eliminate redundancy and dependency anomalies, ensuring that each piece of data is stored in exactly one place. Both processes are prerequisites for a schema that can grow without introducing data inconsistency.
The three normal forms — 1NF, 2NF, and 3NF — define progressively stricter rules for eliminating different classes of data anomalies. A database in 1NF has no repeating groups and atomic values in every cell. A database in 2NF additionally has no partial dependencies — every non-key attribute depends on the full primary key. A database in 3NF additionally has no transitive dependencies — non-key attributes depend only on the primary key, not on other non-key attributes. Violating these rules leads to update anomalies, insertion anomalies, and deletion anomalies that corrupt data over time.
For data engineers, ER modelling and normalisation are foundational skills for two reasons. First, understanding normalisation theory explains why source OLTP databases are designed the way they are — the many small tables with foreign key relationships are a direct product of normalisation. Second, data engineers often design staging schemas and intermediate tables during pipeline development, and a normalised design prevents the data quality bugs that plague unnormalised pipelines: update anomalies in dimension tables, insertion anomalies in staging tables, and deletion anomalies in slowly changing dimension implementations.