Data Model vs Data Dictionary vs Database Schema vs ERD

All those database design terms might be confusing. In this short article I will try explain what they are and what the differences are.

Data Model

Abstract model that organizes data elements and their relationships. It is not related to any implementation. Data model may be represented in many forms, such as Entity Relationship Diagram or UML Class Diagram.

Data Dictionary

Is a reference and description of each data element. It is a detailed definition and documentation of data model (learn more about data dictionary). It can have two levels of abstraction: physical and logical.

Database Schema

Database schema is a physical implementation of data model in a specific database management system. It includes all implementation details such as data types, constraints, foreign or primary keys.

Entity Relationship Diagram

ER diagrams are a graphical representation of data model/schema in relational databases. It is a modelling and a database documentation tool.

Comparison

Data Model Database Schema Data Dictionary (Logical) Data Dictionary (Physical) ERD
Stage Conceptual system modeling System implementation Detailed system design, documentation System implementation, documentation Conceptual system modeling, documentation
Purpose Data design Database implementation Define each data attribute in data model - data model supplement Design and documentation of each data attribute in database schema Communicate data model in relational databases
Level of detail Low or medium - key objects/entities and attributes High - every data and relation detail defined High - every key table and data attribute defined Very high - every table and column defined Low or medium - key entities and attributes
Authors Data/system architects, business analysts Data/system architects, DBAs Data/system architects, business analysts Data/system architects, DBAs Data architects, DBAs
Users Business analysts, business users, data/system architects Developers, DBAs Business analysts, business users, data/system architects Data/system architects, DBAs, developers, testers, system administrators Business analysts, business users, data/system architects, DBAs, developers
Tools CASE, diagramming tools Database development & management tools Word/Excel Word/Excel, extended properties/comments, data dictionary tools CASE, diagramming tools
Form Graphical - UML Class Diagram, ERD Structures in DBMS: tables, columns, foreign keys etc. Tables of metadata Tables of metadata Diagram

Learn more

  1. What is Data Dictionary
  2. Physical vs Logical Data Dictionary
  3. Create a Data Dictionary (tutorial)

or

Start creating Data Dictionaries