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

Stage

• Data Model: Conceptual system modeling
• Database Schema: System implementation
• Data Dictionary (Logical): Detailed system design, documentation
• Data Dictionary (Physical): System implementation, documentation
• ERD: Conceptual system modeling, documentation

Purpose

• Data Model: Data design
• Database Schema: Database implementation
• Data Dictionary (Logical): Define each data attribute in data model - data model supplement
• Data Dictionary (Physical): Design and documentation of each data attribute in database schema
• ERD: Communicate data model in relational databases

Level of detail

• Data Model: Low or medium - key objects/entities and attributes
• Database Schema: High - every data and relation detail defined
• Data Dictionary (Logical): High - every key table and data attribute defined
• Data Dictionary (Physical): Very high - every table and column defined
• ERD: Low or medium - key entities and attributes

Authors

• 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

Users

• 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

Tools

• Data Model: CASE, diagramming tools
• Database Schema: Database development & management tools
• Data Dictionary (Logical): Word/Excel
• Data Dictionary (Physical): Word/Excel, extended properties/comments, data dictionary tools
• ERD: CASE, diagramming tools

Form

• Data Model: Graphical - UML Class Diagram, ERD
• Database Schema: Structures in DBMS: tables, columns, foreign keys etc.
• Data Dictionary (Logical): Tables of metadata
• Data Dictionary (Physical): Tables of metadata
• ERD: Diagram