Types of Data Modelling
Data modelling is not a one-size-fits-all task. It is a progressive process that evolves from abstract business concepts to highly technical database schemas. The three primary types of data models are Conceptual, Logical, and Physical.
1. Conceptual Data Model
The Conceptual Data Model is the highest-level view of the data. It focuses entirely on what data is required by the business, without worrying about how it will be structured or stored.
- Audience: Business Stakeholders, Product Managers, Data Architects.
- Purpose: To define the scope of the system and establish common business vocabulary.
- Key Elements:
- Core Entities (e.g.,
Customer,Store,Product). - High-level relationships between entities.
- Core Entities (e.g.,
- What it excludes: Attributes, primary keys, data types, and database-specific logic.
2. Logical Data Model
The Logical Data Model takes the Conceptual Model and adds detail. It focuses on how the model should be structured based on business rules, independent of any specific database technology (like MySQL vs. Oracle).
- Audience: Data Modellers, Business Analysts, System Architects.
- Purpose: To develop a clear, comprehensive map of the data structures and relationships.
- Key Elements:
- All Entities and their complete set of Attributes.
- Primary Keys (PK) and Foreign Keys (FK).
- Data constraints and rules (e.g., "Email must be unique").
- Normalization (typically resolving Many-to-Many relationships into associative entities).
- What it excludes: Technical implementation details like indexes, exact field lengths (e.g.,
VARCHAR(255)), or table partitions.
3. Physical Data Model
The Physical Data Model represents the actual design of the database. It translates the Logical Model into specific syntax and structures required by the chosen Database Management System (DBMS).
- Audience: Database Administrators (DBAs), Data Engineers, Backend Developers.
- Purpose: To generate the SQL (or NoSQL commands) needed to physically create the database schema.
- Key Elements:
- Tables, Columns, and precise Data Types (e.g.,
INT8,TIMESTAMP,VARCHAR(50)). - Indexes (B-Tree, Hash) for performance tuning.
- Triggers, Views, and Stored Procedures.
- Storage configurations like partitioning and sharding rules.
- Tables, Columns, and precise Data Types (e.g.,
Quick Comparison
| Feature | Conceptual Model | Logical Model | Physical Model |
|---|---|---|---|
| Focus | Business concepts | Data structures & rules | Database implementation |
| Entities | Yes | Yes | Yes (as Tables) |
| Attributes | No | Yes | Yes (as Columns) |
| Primary/Foreign Keys | No | Yes | Yes |
| Specific Data Types | No | No | Yes (e.g., INT, JSONB) |
| Indexes & Partitions | No | No | Yes |
| Database Agnostic? | Yes | Yes | No (Tied to specific DBMS) |
Tip
Real-world Practice: In modern, fast-paced Agile development, teams often merge the Conceptual and Logical phases directly into whiteboard sessions, jumping straight into designing the Physical Model based on the immediate engineering requirements.