Data Modelling Vocabulary: ER Diagrams, Normalisation, and Schema Design

Data engineers, data analysts, and backend developers who work with databases need precise vocabulary to discuss schema design, model structure, and data relationships. Whether you are drawing an entity-relationship diagram, reviewing a dbt project, or discussing normalisation trade-offs with a colleague, the right vocabulary makes the conversation faster and more accurate.

Entity-Relationship (ER) Diagram Vocabulary

Entity — a real-world object or concept represented as a table in a relational database. Entities are typically nouns: Customer, Order, Product, Invoice. “The schema has three core entities: Customer, Order, and OrderLine.”

Attribute — a property or characteristic of an entity, corresponding to a column in a table. “The Customer entity has attributes: customer_id, email, created_at, and country_code.”

Relationship — a connection between two entities. Relationships are described by their cardinality. “The relationship between Customer and Order is one-to-many: one customer can place many orders, but each order belongs to exactly one customer.”

Cardinality — describes the numerical nature of a relationship:

• One-to-one (1:1) — each record in Table A corresponds to exactly one record in Table B.
• One-to-many (1:N) — each record in Table A can correspond to many records in Table B.
• Many-to-many (M:N) — records in both tables can correspond to multiple records in the other, requiring a junction table.

Junction table / associative table — a table used to implement a many-to-many relationship. “The OrderProduct junction table stores the relationship between orders and products, along with the quantity of each product in a given order.”

Primary key — a column or combination of columns that uniquely identifies each row in a table. “We use a UUID as the primary key for the User table to avoid exposing sequential IDs in URLs.”

Foreign key — a column in one table that references the primary key in another table, enforcing referential integrity. “The order_id column in the OrderLine table is a foreign key referencing the id column in the Order table.”

Referential integrity — the guarantee that a foreign key value always corresponds to an existing primary key value. “Dropping that record without a cascade rule would violate referential integrity.”

Normalisation Vocabulary

Normalisation is the process of structuring a database to reduce redundancy and improve data integrity.

First Normal Form (1NF) — each column contains atomic (indivisible) values, and each row is unique. “The table was not in 1NF because the tags column stored comma-separated values; we split it into a separate Tags table.”

Second Normal Form (2NF) — the table is in 1NF and every non-key attribute is fully dependent on the entire primary key (relevant for composite keys). “Moving the product name out of the OrderLine table and into the Product table brought the schema to 2NF.”

Third Normal Form (3NF) — the table is in 2NF and no non-key attribute is transitively dependent on the primary key. “Separating the country-currency mapping into its own reference table eliminates the transitive dependency.”

Denormalisation — intentionally introducing redundancy into a schema to improve read performance. Common in analytical databases and data warehouses. “We denormalised the customer region into the events table to avoid expensive joins at query time.”

Schema Design Vocabulary

Index — a database structure that improves the speed of data retrieval at the cost of additional storage and write overhead. “We added a composite index on (user_id, created_at) to support the most common query pattern on the events table.”

Composite index — an index that covers more than one column. Column order in a composite index matters. “The composite index on (tenant_id, status, created_at) serves the most frequent queries in our multi-tenant architecture.”

Constraint — a rule enforced at the database level: primary key, foreign key, unique, not null, check. “Adding a check constraint on the amount column ensures we never store a negative transaction value.”

Partitioning — dividing a large table into smaller, more manageable pieces based on a column value (range, list, or hash). “We partition the events table by month on created_at; queries for recent events only scan the most recent partition.”

Schema migration — a script that changes the structure of a database schema in a controlled, versioned way. “Every schema change goes through a migration file; we never modify the database schema manually.”

dbt Model Types

dbt (data build tool) is widely used in data engineering. Its layered modelling approach has its own vocabulary.

Staging model — a thin transformation layer that cleans, renames, and casts raw source data. One staging model per source table, minimal business logic. “The stg_orders model casts the order_total field from a string to a decimal and renames columns to match our naming convention.”

Intermediate model — a model that combines or further transforms staging models, but is not yet a business-facing output. “The intermediate model joins orders with returns to calculate net order value, which is then referenced by multiple mart models.”

Mart model — a wide, business-facing table designed for reporting and analysis. Marts are often denormalised for query performance. “The fct_orders mart model contains one row per order with all the dimensions an analyst needs for revenue reporting.”

Snapshot model — a dbt model that captures the state of a slowly changing dimension at a point in time, enabling historical analysis. “We created a snapshot of the users table to track how customer subscription tiers change over time.”

Seed — a CSV file loaded into dbt as a static reference table. “Country codes and their ISO names are managed as a dbt seed.”

Example Sentences in Context

1. “The relationship between the Campaign and ConversionEvent entities is one-to-many; a campaign can generate thousands of conversion events, but each event is attributed to exactly one campaign.”

2. “We deliberately denormalised the pricing tier into the invoices table because joining back to the pricing configuration at query time was adding 200 ms to every report query.”

3. “The composite index on (customer_id, order_date DESC) was chosen specifically to support the most common access pattern: fetching a customer’s most recent orders.”

4. “All schema changes are versioned as migration files in the repository; no DBA has direct write access to the production schema outside of a reviewed and approved migration.”

5. “Our dbt project follows a strict three-layer architecture: staging models contain only source-faithful transformations, intermediate models contain join logic, and mart models are the only layer that analysts query directly.”

In Practice: Navigating the Nuances of Feedback

Let’s be honest – when you’re building a database schema, particularly in a team environment, communication can sometimes feel…complex. The terminology isn’t always intuitive, especially if your first language isn’t English. It’s easy to get bogged down in technical jargon and miss the core message. That’s where understanding how professionals use these terms becomes just as important as knowing what they mean.

Consider this scenario: You’ve submitted a Pull Request (PR) detailing changes to your customer order database schema. Your senior developer, Sarah, leaves a comment on the PR description: “This is good work, but I’m concerned about the normalization level here. Specifically, could you explain why we have a denormalized junction table for orders and `products’? It seems like it might lead to update anomalies in the future.” The phrasing isn’t aggressive; it’s constructive. Sarah isn’t simply saying “wrong!” She’s using precise vocabulary – “normalization level,” “denormalized junction table,” and crucially, “update anomalies” – each carrying a specific technical implication. Understanding that ‘update anomalies’ refers to the potential for data inconsistency if updates aren’t handled carefully is key. It’s about recognizing she’s raising a potential problem, not issuing an immediate criticism. Similarly, in Slack conversations discussing schema design, you might hear someone say, “Let’s focus on ensuring we have proper foreign keys to maintain referential integrity.” The emphasis here is on the principle of maintaining data relationships – something often lost when translating directly from native language concepts.

Another common situation arises during a code review. A reviewer might point out: “This index on customer_id in the orders table seems redundant given the primary key constraint.” The term “redundant” isn’t meant to diminish your effort; it’s highlighting a potential inefficiency – an index that doesn’t offer significant performance gains and could, over time, complicate maintenance. It’s about evaluating the trade-offs involved in schema design. Learning to interpret these nuances allows you to respond effectively, perhaps saying: “You are correct; I hadn’t considered the impact of the primary key constraint when adding this index. I will evaluate its necessity and potentially remove it.”

The goal isn’t simply to parrot back English definitions. It’s about understanding why particular terms are used in a professional context, recognizing the underlying concerns they represent, and responding with appropriate precision. It’s about demonstrating you grasp not just what is being asked but why it matters for the overall system’s stability and performance.

Here’s an example of how you might use dbt to create a staging model:

-- dbt Staging Model - Orders
source orders_table {
  sql = """
    SELECT
      order_id,
      customer_id,
      product_id,
      order_date,
      total_amount
    FROM {{ source('raw_orders') }}
  """
}

This simple example demonstrates the use of source and SQL to transform raw data – a key concept when discussing staging layers in dbt. The language here isn’t just about technical steps; it’s about describing how you’re preparing data for further analysis, emphasizing a deliberate and controlled process.