Understanding Data Model, Data Dictionary, Database Schema, and ER Diagram: Definitions and Comparisons

All these database design terms can be confusing. In this short article I will try to explain what they are and how they differ.

Data Model : An abstract model that organizes data elements and their relationships, independent of any implementation. It can be represented by entity‑relationship diagrams, UML class diagrams, etc.

Data Dictionary : A reference and description for each data element, providing a detailed definition and documentation of the data model. It has two abstraction levels: logical and physical.

Database Schema : The physical implementation of a data model in a specific DBMS, including all implementation details such as data types, constraints, foreign keys, and primary keys.

Entity‑Relationship Diagram (ERD) : A graphical representation of the data model/schema in a relational database, used as a modeling and documentation tool.

Comparison

Stage

Data Model: Conceptual system modeling

Database Schema: System implementation

Data Dictionary (Logical): Detailed system design documentation

Data Dictionary (Physical): System implementation documentation

Conceptual system modeling, documentation

Purpose

Data Model: Data design

Database Schema: Database implementation

Data Dictionary (Logical): Define each data attribute in the model – supplement to the data model

Data Dictionary (Physical): Design and document each data attribute in the database schema

Communication data model in relational databases

Detail Level

Data Model: Mid‑level objects/entities and attributes

Database Schema: Highly detailed definition of each data item and relationship

Data Dictionary (Logical): Highly defined each key table and data attribute

Data Dictionary (Physical): Very high‑level definition of each table and column

Low or medium‑level entities and attributes

Author

Data Model: Data/system architects, business analysts

Database Schema: Data/system architects, DBAs

Data Dictionary (Logical): Data/system architects, business analysts

Data Dictionary (Physical): Data/system architects, DBAs

ERD: Data architects, DBAs

User

Data Model: Business analysts, business users, data/system architects

Database Schema: Developers, DBAs

Data Dictionary (Logical): Business analysts, business users, data/system architects

Data Dictionary (Physical): Data/system architects, DBAs, developers, testers, system administrators

ERD: Business analysts, business users, data/system architects, DBAs, developers

Tool

Data Model: Case studies, diagram tools

Database Schema: Database development and management tools

Data Dictionary (Logical): Word/Excel

Data Dictionary (Physical): Word/Excel, extended attributes/comments, data‑dictionary tools

ERD: Case studies, diagram tools

Form

Data Model: Graphical UML class diagram

Database Schema: Structure in a DBMS – tables, columns, foreign keys, etc.

Data Dictionary (Logical): Metadata tables

Data Dictionary (Physical): Metadata tables

ERD: Charts