Introduction

The goal of this article is to set the stage for data modeling. It is part of a broader series where we will progressively explore the world of data modeling, diving deeper into technical and expert-level topics such as normalization, dimensional modeling, Data Vault, and their implications on system architecture.

If you’re already familiar with the fundamental concepts below, feel free to skip ahead to the more technical articles in the series.

First of all, let’s talk ER Diagrams?

ER Diagram

Entity-Relationship (ER) Diagrams are visual representations of a database structure. They help design and understand relationships between entities (tables) before actual implementation.

Key Components of ER Diagrams

  1. Entities (Tables) – Represented as rectangles (e.g., Customer, Order).
  2. Attributes (Columns) – Represented as ovals (e.g., CustomerName, OrderDate).
  3. Relationships (Foreign Keys) – Represented as diamonds connecting entities (e.g., Customer places Order). It can have a cardinality, defining the type of the relationships (1:1, 1:M, M:M).

Note: Enhanced Entity-Relationship diagrams extend standard ER diagrams by adding concepts like generalization/specialization. These enhancements provide a richer semantic representation of complex data models.

Data Model Layers

Now, let’s dive into the core of the topic: exploring the different types of data models, their key characteristics, their differences, and the main stakeholders involved.

Data Model Layers

Data modeling consists of three key layers, each playing a unique role in the lifecycle of data architecture:

  • Conceptual Model: Defines business requirements and processes, ensuring alignment with stakeholders’ needs.
  • Logical Model – Focuses on data structure and normalization, establishing relationships and attributes without considering technical constraints.
  • Physical Model: Implements the logical model into actual database structures, incorporating technical specifications tailored to the chosen database technology. You can have multiple physical models for the same logical model because it depends on database technology.

Breakdown of each layer

Each layer in data modeling serves a specific purpose, progressively refining data representation from abstract business concepts to concrete database implementation. Below is a breakdown of these layers and their significance:

Conceptual Model

Conceptual Model
  • What it is: A high-level representation of the data, focusing on business concepts and relationships (usually using ER diagrams).
  • Purpose: Helps business stakeholders understand the data without technical details.
  • Key elements: Entities (major business objects), high-level relationships between entities, and key business rules.

Logical Model

Logical Model
  • What it is: A more detailed model that defines the data structure without considering physical implementation.
  • Purpose: Provides a clear view of the data structure for analysts and developers, independent of any database technology.
  • Key elements: Entities, attributes (with data types), primary keys, foreign keys, and normalized relationships with no physical storage details.

Physical Model

Physical Model
  • What it is: A model that defines how data will be stored in a specific database system (e.g., PostgreSQL, SQL Server, etc.).
  • Purpose: Used by DBAs and developers to implement the actual database.
  • Key elements: Tables, columns, data types, constraints (e.g., indexes, partitions, clustering details), storage details, and performance tuning considerations.
⚠️
This example focuses on an relational databases (OLTP) that use strictly structured and normalized data, ensuring data integrity through ACID compliance. NoSQL databases and physical models in OLAP world are typically more denormalized to optimize for their specific use cases.

🛑 Technical Notes - Transitioning from Logical to Physical Models

Too technical, so I put it in separate toggle 😉, when moving from a logical model to a physical model (relational in this case), don’t forget to consider the following:

  1. Ensure necessary metadata is captured: Include creation date, update date, and status columns for tracking changes.
  2. Optimize attribute definitions: Choose appropriate data types and define length limits based on actual use cases.
  3. Handle one-to-one relationships: You can place the foreign key (FK) in one of the tables (depending on where you want to access the information) or in both tables (which introduces inconsistency risks). A bridge table is typically unnecessary unless there are additional attributes related to the relationship.
  4. Handle one-to-many relationships: The standard approach is to place the foreign key on the “many” side of the relationship. A bridge table is usually not needed unless the relationship has additional attributes.
  5. Handle many-to-many relationships: In logical models, M:M relationships are represented, but in physical models, they must be resolved by creating a bridge table with foreign keys pointing to both related tables.
  6. Manage generalization/specialization: Generalization (inheritance) exists in logical models but must be implemented physically using one of these approaches:
    • Single Table : A single table is used for all entities in the hierarchy.
      • A type discriminator column (e.g., entity_type) is added to differentiate records.
      • Simple, efficient queries, good performance.
      • BUT lead to many NULL values if child entities have different attribute
    • Table per Class : Each subclass gets its own table, without a shared table for common attributes .
      • Each subclass table contains all necessary attributes, including inherited ones.
      • No NULL values, clear separation of entities.
      • BUT redundant storage of common attributes, complex queries when retrieving data across all subclasses.
    • Joined Inheritance : Common attributes are stored in a base table, while specific attributes are in separate child tables.
      • Eliminates redundancy, follows normalization principles.
      • BUT more complex queries due to joins, potential performance overhead.
    • There is no ultimate technique. I personally prefer joined inheritance because it follows normalization principles.
  7. Indexing and performance tuning: Ensure proper indexing strategies, partitioning (if required), and clustering optimizations are applied.
✔️
A well-structured data model reduces redundancy, improves performance, and ensures data integrity across systems !

Key Takeaways

❗In today’s article, we’ve explored what a data model is and why it matters ! You’ve learned how ER diagrams help visualize relationships before building a database. We also broke down the conceptual, logical, and physical layers, transforming data from business concepts into structured database models.

In the next article, we’ll dive into normalization levels and their impact on data integrity and performance : Data Modeling: Dive into data normalization levels.