An ER diagram shows what exists in a data model—entities, attributes, relationships, constraints. But it cannot show why design decisions were made, what business rules govern data values, when elements were added or modified, or who is responsible for what.

This contextual knowledge lives in documentation.

Every organization has experienced the pain of undocumented systems:

• 'Why is this nullable?'
• 'What does status code 7 mean?'
• 'Why are there two Customer tables?'
• 'Who decided to denormalize this?'
• 'Is this column still used?'

Without documentation, every engineer who touches the system must rediscover this knowledge through archaeology—examining code, querying data, interviewing colleagues (if they're still around), or simply guessing. This process wastes time, introduces errors, and frustrates everyone involved.

Documentation is the difference between a data model that remains maintainable for decades and one that becomes a legacy burden within years.

Documentation is often treated as an afterthought—something done (if at all) after the 'real work' of modeling and implementation. This mindset leads to sparse, outdated, or nonexistent documentation.

The True Cost of Undocumented Models

Consider the lifecycle of a data model:

1. Initial Development (Months 1-6): The design team knows everything implicitly. Documentation seems redundant.

2. Early Production (Months 6-18): Original team members leave or transition. New developers ask questions that 'everyone knew' the answers to.

3. Maturity (Years 2-5): The model is modified repeatedly. Nobody remembers original design rationale. Modifications introduce inconsistencies because engineers don't understand the implications.

4. Legacy Phase (Years 5+): The model is critical but feared. Nobody wants to touch it because understanding it requires too much effort. Technical debt accumulates.

Proper documentation prevents this decay. It preserves institutional knowledge across personnel changes and time.

The data dictionary is the central repository of metadata about a data model. It defines every entity, attribute, relationship, and constraint with sufficient detail for unambiguous interpretation.

Components of a Comprehensive Data Dictionary

1. Entity Definitions: For each entity, document:

   • Name: The official name of the entity
   • Definition: A clear, business-oriented description of what the entity represents
   • Aliases: Other names this entity might be known by in the business
   • Examples: Real-world examples of instances
   • Owner: Who is responsible for the entity's definition and data quality
   • Source: Where the data comes from (user input, integration, derived)

2. Attribute Definitions: For each attribute, document:

   • Name: Official attribute name
   • Definition: What the attribute represents
   • Data type: Logical and physical data type
   • Domain/Values: Allowed values, constraints, enumerations
   • Nullability: Whether null is permitted and what null means
   • Default: Default value, if any
   • Example values: Representative data
   • Business rules: Rules governing the attribute's value

Relationship Definitions

For each relationship, document:

• Name: The relationship name
• Definition: What the relationship represents in business terms
• Participating entities: With role names if applicable
• Cardinality: With business justification
• Participation: Total or partial, and why
• Referential action: What happens on delete/update
• Examples: Real-world instances of the relationship

Storage and Access

Data dictionaries can be stored in various formats:

Business rules govern how data is created, modified, and related. While some rules are expressed in the schema (constraints, relationships), many cannot be captured declaratively and must be documented.

Categories of Business Rules

1. Structural Rules: Expressed in the schema

   • Entity existence: 'Every Employee belongs to exactly one Department'
   • Attribute constraints: 'Price must be positive'
   • Referential integrity: 'Order must reference a valid Customer'

2. Derivation Rules: How values are calculated

   • 'Total Order Amount = Sum of Line Item Amounts + Tax'
   • 'Customer Age = Current Date - Birth Date'

3. Action Rules: What happens on events

   • 'When an Order is cancelled, all Line Items are cancelled'
   • 'Customer deletion requires anonymizing historical orders'

4. Process Rules: Sequencing and workflow

   • 'Order status must follow: Pending → Approved → Shipped → Delivered'
   • 'Credit limit can only be increased after manager approval'

5. Validation Rules: Complex conditionals

   • 'If customer_type = 'B' then tax_exempt_id is required'
   • 'Discount percentage cannot exceed tier maximum'

Rule-Entity Matrix

For complex models, maintain a matrix showing which rules affect which entities:

This matrix helps impact analysis: when modifying an entity, you can quickly identify all rules that might be affected.

While data dictionaries provide comprehensive documentation, some information is best placed on the diagram itself as annotations. Annotations provide context without requiring separate document lookup.

Types of Diagram Annotations

1. Entity Notes: Inline explanations attached to entities

   • Purpose clarification: 'This entity captures both shipped and planned orders'
   • Special handling: 'GDPR-sensitive; requires encryption at rest'
   • Integration notes: 'Synchronized nightly from ERP system'

2. Relationship Notes: Clarifications on relationships

   • Cardinality justification: 'One-to-many because customers can have multiple shipping addresses'
   • Constraint explanation: 'Total participation because all orders must have at least one line item'
   • Historical context: 'Changed from M:N to 1:N in v2.0; junction table preserved for audit'

3. Attribute Notes: For attributes that need clarification

   • Calculation: 'Derived from sum of line_item_amounts'
   • Valid values: 'See lookup table STATUS_CODES'
   • Deprecation: 'DEPRECATED: Use new_status instead; removed in v4.0'

Annotation Callouts and Symbols

Use visual conventions to distinguish annotation types:

Legend for Annotations

If your diagram uses annotation styles for semantic meaning, include a legend explaining them. Don't assume viewers know your conventions.

Data models evolve over time. Capturing what changed, when, why, and who made changes is essential for understanding the current model and managing future evolution.

Version Identification

Every diagram and documentation artifact should carry version information:

• Version number: Major.Minor.Patch (e.g., 3.2.1)
• Version date: When this version was created
• Author: Who made the changes
• Status: Draft, Under Review, Approved, Deprecated

Versioning Scheme

Change Log

Maintain a structured change log documenting every modification:

# Change Log: Sales Domain Model

## Version 3.1.0 (2024-01-15)
- Added: Customer.preferred_language attribute (REQ-2024-023)
- Added: Relationship Customer-ShippingPreference (REQ-2024-023)
- Modified: Order.status_code expanded to support 'ON_HOLD' (BUG-2023-445)
- Author: J. Smith
- Reviewed by: A. Johnson

## Version 3.0.0 (2023-09-01)
- Added: PaymentMethod entity (REQ-2023-089)
- Added: Subscription entity (REQ-2023-102)
- Removed: LegacyCustomer entity (deprecated in v2.5)
- Breaking: Customer.payment_type moved to PaymentMethod relationship
- Author: M. Garcia
- Reviewed by: K. Williams

Preserving Historical Versions

Don't overwrite previous versions—preserve them:

1. File naming: sales-model-v3.1.0.erdm, sales-model-v3.0.0.erdm
2. Version control: Store diagrams in Git or similar VCS
3. Archive folders: /models/current/, /models/archive/
4. Modeling tool history: Use built-in versioning features if available

Why This Matters:

• Rollback capability when changes cause issues
• Audit trail for compliance requirements
• Historical reference for impact analysis
• Understanding the evolution of complex structures

Deprecated Element Handling

When elements are scheduled for removal:

1. Mark as deprecated in current version (don't remove yet)
2. Document the deprecated status, reason, and planned removal version
3. Provide migration guidance (what replaces it)
4. Remove in a future major version after transition period

@deprecated Since version 2.5.0
Scheduled for removal in version 3.0.0
Replacement: Use Customer.email_primary instead
Migration: Run script migrate_email_fields.sql

Metadata is data about data—the descriptions, definitions, and contextual information that give meaning to your data model. Effective metadata management integrates documentation with tools and processes.

Metadata Categories

1. Technical Metadata: Schema structure, data types, constraints, indexes
2. Business Metadata: Definitions, ownership, stewardship, glossary terms
3. Operational Metadata: Source systems, ETL processes, refresh schedules
4. Usage Metadata: Query patterns, access frequency, consumer applications
5. Governance Metadata: Classification, sensitivity, retention, compliance

Metadata Standards

Organizations benefit from adopting or creating metadata standards:

Metadata Integration Points

Metadata should flow between systems, not exist in silos:

1. Modeling Tool → Data Dictionary: Export entity/attribute definitions
2. Data Dictionary → Database Catalog: Push descriptions to database COMMENT fields
3. Database Catalog → Business Glossary: Link technical names to business terms
4. Business Glossary → Reporting Tools: Enable self-service discovery
5. All Systems → Data Catalog: Centralized metadata repository

Data Catalog Integration

Modern data catalogs (Alation, Collibra, Apache Atlas, AWS Glue Data Catalog) provide:

• Centralized metadata storage
• Search and discovery
• Lineage visualization
• Collaborative annotation
• Governance enforcement

If your organization uses a data catalog, integrate your ER model documentation:

# Example: Push metadata to data catalog API
for entity in er_model.entities:
    catalog_api.update_table(
        name=entity.name,
        description=entity.definition,
        owner=entity.owner,
        tags=entity.domain_tags
    )
    for attr in entity.attributes:
        catalog_api.update_column(
            table=entity.name,
            column=attr.name,
            description=attr.definition,
            data_type=attr.data_type
        )

Documentation is only valuable if it's accurate and current. Documentation governance establishes processes to ensure quality.

Roles and Responsibilities

Review Processes

1. On Creation: All new entities, attributes, and relationships require documentation as part of the design review. No undocumented elements approved.

2. On Modification: Changes require updated documentation. Pull request/change request includes documentation diff.

3. Periodic Review: Schedule regular audits (quarterly, semi-annually) to:

   • Verify documentation matches current schema
   • Identify undocumented or poorly documented elements
   • Update ownership for organizational changes
   • Retire documentation for removed elements

4. Usage-Triggered Review: When questions arise that documentation should answer but doesn't, flag for documentation improvement.

Documentation transforms an ER diagram from a snapshot of structure into a living resource that supports understanding, maintenance, and evolution over time. The investment in comprehensive documentation pays dividends across the entire system lifecycle.

What's Next

Even experienced modelers make mistakes. The next page examines common mistakes in ER diagram creation—patterns of error that lead to flawed models, miscommunication, and costly rework. Recognizing these patterns helps you avoid them.