Data Modeling Foundations

Data Modeling Foundations

Designing data models for relational and non-relational systems — from entities and relationships through normalization, denormalization, and data model patterns.

When to Use

• Designing database schemas for new applications
• Modeling business entities and their relationships
• Normalizing data to reduce redundancy
• Optimizing data models for query performance
• Building conceptual, logical, and physical data models

Modeling Levels

MODELING_LEVELS = {
    'conceptual': 'Business entities and relationships (no technical details)',
    'logical': 'Attributes, data types, keys, relationships (DB-agnostic)',
    'physical': 'Tables, columns, indexes, partitions (DB-specific)',
}

# Entity-Relationship modeling
class DataModel:
    def __init__(self, name: str):
        self.name = name
        self.entities = {}  # entity_name -> attributes
    
    def add_entity(self, name: str, attributes: Dict, 
                   primary_key: str = 'id'):
        self.entities[name] = {
            'attributes': attributes, 'primary_key': primary_key,
            'relationships': []
        }
    
    def add_relationship(self, source: str, target: str, 
                         rel_type: str = 'one_to_many'):
        """one_to_one, one_to_many, many_to_many"""
        if source in self.entities and target in self.entities:
            self.entities[source]['relationships'].append({
                'target': target, 'type': rel_type
            })

Common Pitfalls

1. Over-normalization — 6th normal form hurts query performance; stop at 3NF for OLTP
2. Under-normalization — duplicate data everywhere leads to update anomalies
3. Ignoring access patterns — model for how data is READ, not just stored
4. No naming conventions — inconsistent names create confusion; use conventions
5. Mixed OLTP and OLAP — transactional and analytical models have different needs; separate them

Verification Checklist

• Entities identified and named consistently
• Relationships correctly typed (1:1, 1:M, M:M)
• Normalized to at least 3NF
• Access patterns considered in model design
• Primary and foreign keys defined
• Indexes on foreign keys and frequent query columns
• Denormalization justified (only for performance)