Data Model

What is Entity Design?

Entity design is the process of identifying the core objects in your system and defining their attributes. It's the foundation of your data model.

Step-by-Step Process

1. Read the problem statement and extract nouns

For a social media platform, the nouns are: Users, Posts, Comments, Likes, Followers, Messages, Groups.

2. For each entity, list its attributes

Entity	Key Attributes
User	id, username, email, password_hash, display_name, avatar_url, bio, created_at, updated_at
Post	id, user_id, content, image_url, visibility (public/private), created_at, updated_at
Comment	id, post_id, user_id, content, parent_comment_id (for replies), created_at
Like	id, user_id, post_id, created_at
Follow	id, follower_id, followee_id, created_at
Message	id, sender_id, receiver_id, content, read_at, created_at

3. Define primary keys

Every entity needs a unique identifier:

-- Auto-increment (simple, sequential)
id BIGINT PRIMARY KEY AUTO_INCREMENT

-- UUID (globally unique, good for distributed systems)
id CHAR(36) PRIMARY KEY  -- or BINARY(16) for space efficiency

-- Snowflake ID (time-ordered, distributed-friendly)
id BIGINT PRIMARY KEY  -- encodes timestamp + machine + sequence

ID Type	Pros	Cons
Auto-increment	Simple, sequential, small	Not distributed-friendly, leaks data
UUID	Globally unique, no coordination	36 chars, not sortable, larger index
Snowflake	Time-ordered, distributed, compact	Requires coordination, more complex

4. Add timestamps

Always include created_at and updated_at for auditing and debugging.

created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP

5. Consider soft deletes

Instead of deleting rows, mark them as deleted:

deleted_at TIMESTAMP NULL DEFAULT NULL
-- Query: WHERE deleted_at IS NULL

This preserves data for audit trails and allows recovery.

Entity Design Principles

• Single source of truth: Each piece of data should live in exactly one place
• Meaningful names: Table names should be plural (users, posts), columns should be snake_case
• Appropriate data types: Use the smallest type that fits (TINYINT for boolean, INT for IDs, TEXT for long content)
• Constraints: Use NOT NULL, UNIQUE, CHECK constraints to enforce data integrity
• Index foreign keys: Every foreign key column should be indexed for JOIN performance

SQL Schema Example: Users Table

CREATE TABLE users (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(30) NOT NULL UNIQUE,
    email VARCHAR(255) NOT NULL UNIQUE,
    password_hash VARCHAR(255) NOT NULL,
    display_name VARCHAR(100),
    avatar_url VARCHAR(500),
    bio TEXT,
    is_verified BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    deleted_at TIMESTAMP NULL DEFAULT NULL,
    INDEX idx_email (email),
    INDEX idx_username (username)
);

NoSQL Document Design: Users Collection

{
    "_id": "user_abc123",
    "username": "johndoe",
    "email": "john@example.com",
    "displayName": "John Doe",
    "avatarUrl": "https://cdn.example.com/avatars/john.jpg",
    "bio": "Software engineer and coffee enthusiast",
    "isVerified": false,
    "createdAt": "2024-01-15T10:30:00Z",
    "updatedAt": "2024-03-20T14:22:00Z"
}

Types of Relationships

Every relationship between two entities has a cardinality: how many instances of each entity are related.

One-to-One (1:1)

One record in Table A relates to exactly one record in Table B.

┌──────────┐       ┌──────────────┐
│  users   │       │ user_profiles │
│──────────│       │──────────────│
│ id (PK)  │──1:1──│ user_id (FK) │
│ username │       │ bio          │
│ email    │       │ website      │
└──────────┘       └──────────────┘

SQL:

CREATE TABLE user_profiles (
    user_id BIGINT PRIMARY KEY,
    bio TEXT,
    website VARCHAR(255),
    location VARCHAR(100),
    FOREIGN KEY (user_id) REFERENCES users(id)
);

When to use: Profile data that not all users have, or data that's large and rarely accessed (separate table to keep users table lean).

One-to-Many (1:N)

One record in Table A relates to many records in Table B. The most common relationship.

┌──────────┐       ┌──────────┐
│  users   │       │  posts   │
│──────────│       │──────────│
│ id (PK)  │──1:N──│ user_id  │
│ username │       │ id (PK)  │
│ email    │       │ content  │
└──────────┘       └──────────┘

SQL:

CREATE TABLE posts (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    user_id BIGINT NOT NULL,
    content TEXT NOT NULL,
    image_url VARCHAR(500),
    visibility ENUM('public', 'private') DEFAULT 'public',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id),
    INDEX idx_user_id (user_id)
);

When to use: User → Posts, Post → Comments, Order → OrderItems, Category → Products.

Many-to-Many (M:N)

Many records in Table A relate to many records in Table B. Requires a junction table.

┌──────────┐       ┌──────────┐       ┌──────────┐
│  users   │       │ follows  │       │  users   │
│──────────│       │──────────│       │──────────│
│ id (PK)  │──M:N──│ follower │──M:N──│ id (PK)  │
│ username │       │ followee │       │ username │
│ email    │       │ id (PK)  │       │ email    │
└──────────┘       │ created  │       └──────────┘
                   └──────────┘

SQL:

CREATE TABLE follows (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    follower_id BIGINT NOT NULL,
    followee_id BIGINT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (follower_id) REFERENCES users(id),
    FOREIGN KEY (followee_id) REFERENCES users(id),
    UNIQUE KEY unique_follow (follower_id, followee_id),
    INDEX idx_followee (followee_id)
);

When to use: User ↔ User (followers), User ↔ Post (likes), Post ↔ Tag, User ↔ Group.

Self-Referencing Relationship

An entity relates to itself. Common for comments with replies.

┌────────────────┐
│    comments    │
│────────────────│
│ id (PK)        │
│ post_id (FK)   │
│ user_id (FK)   │
│ content        │
│ parent_id (FK) │──────┐
│ created_at     │      │
└────────────────┘      │
       ▲                │
       └────────────────┘
       (self-reference)

SQL:

CREATE TABLE comments (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    post_id BIGINT NOT NULL,
    user_id BIGINT NOT NULL,
    content TEXT NOT NULL,
    parent_comment_id BIGINT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (post_id) REFERENCES posts(id),
    FOREIGN KEY (user_id) REFERENCES users(id),
    FOREIGN KEY (parent_comment_id) REFERENCES comments(id),
    INDEX idx_post_id (post_id),
    INDEX idx_parent (parent_comment_id)
);

Relationship Summary

Relationship	Example	Junction Table?	SQL Pattern
One-to-One	User ↔ Profile	No	FK in either table
One-to-Many	User → Posts	No	FK in the "many" table
Many-to-Many	User ↔ Tags	Yes	Junction table with two FKs
Self-Referencing	Comment → Reply	No	FK references same table

Normalization vs Denormalization

This is the single most important tradeoff in data modeling.

Normalization (3NF)

Organize data to eliminate redundancy. Each piece of data stored in exactly one place.

Example: Normalized Schema

-- Users table (no post data)
CREATE TABLE users (
    id BIGINT PRIMARY KEY,
    username VARCHAR(30) NOT NULL,
    email VARCHAR(255) NOT NULL
);

-- Posts table (references user)
CREATE TABLE posts (
    id BIGINT PRIMARY KEY,
    user_id BIGINT NOT NULL,
    content TEXT NOT NULL,
    FOREIGN KEY (user_id) REFERENCES users(id)
);

To get a user's posts with their name:

SELECT u.username, p.content
FROM posts p
JOIN users u ON p.user_id = u.id
WHERE p.user_id = 123;

Pros:

• No data inconsistency (update one place, reflected everywhere)
• Smaller storage
• Easier to maintain data integrity

Cons:

• Requires JOINs (slow at scale)
• Complex queries
• Doesn't scale well for read-heavy systems

Denormalization

Duplicate data to avoid JOINs. Optimize for read performance.

Example: Denormalized Posts Table

CREATE TABLE posts (
    id BIGINT PRIMARY KEY,
    user_id BIGINT NOT NULL,
    username VARCHAR(30) NOT NULL,      -- Duplicated from users
    user_avatar_url VARCHAR(500),        -- Duplicated from users
    content TEXT NOT NULL,
    likes_count INT DEFAULT 0,          -- Pre-computed counter
    comments_count INT DEFAULT 0,       -- Pre-computed counter
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Query becomes simple:

SELECT username, user_avatar_url, content, likes_count
FROM posts
WHERE user_id = 123;
-- No JOIN needed!

Pros:

• Fast reads (no JOINs)
• Simple queries
• Scales well for read-heavy workloads

Cons:

• Data inconsistency risk (username changes → must update all posts)
• Larger storage
• More complex writes (update multiple places)

When to Denormalize

Scenario	Recommendation
Read-heavy (100:1 read:write ratio)	Denormalize
Write-heavy (social feeds, logging)	Normalize
Need strong consistency (financial)	Normalize
Need low latency reads (user profiles)	Denormalize
Can tolerate stale data (analytics)	Denormalize
Data rarely changes (product catalog)	Denormalize

SQL Schema Design Patterns

1. Star Schema (Analytics)

-- Fact table
CREATE TABLE order_items (
    id BIGINT PRIMARY KEY,
    order_id BIGINT NOT NULL,
    product_id BIGINT NOT NULL,
    quantity INT,
    price DECIMAL(10,2)
);

-- Dimension tables
CREATE TABLE products (id BIGINT PRIMARY KEY, name VARCHAR(255), category VARCHAR(100));
CREATE TABLE orders (id BIGINT PRIMARY KEY, user_id BIGINT, created_at TIMESTAMP);

2. Soft Deletes

ALTER TABLE users ADD COLUMN deleted_at TIMESTAMP NULL;
-- Query active users: WHERE deleted_at IS NULL
-- Restore: UPDATE users SET deleted_at = NULL WHERE id = 123;

3. Optimistic Locking

ALTER TABLE posts ADD COLUMN version INT DEFAULT 0;
-- Update with version check:
UPDATE posts SET content = 'new', version = version + 1
WHERE id = 123 AND version = 5;
-- If affected_rows = 0, someone else modified it

NoSQL Schema Design Patterns

1. Single Table Design (DynamoDB)

Store all related data in one table, use sort keys for access patterns.

// DynamoDB Items in a single table
// PK: USER#123, SK: PROFILE
{"PK": "USER#123", "SK": "PROFILE", "name": "Alice", "email": "alice@example.com"}
// PK: USER#123, SK: POST#2024-03-20
{"PK": "USER#123", "SK": "POST#2024-03-20", "content": "Hello world"}
// PK: USER#123, SK: FOLLOWER#456
{"PK": "USER#123", "SK": "FOLLOWER#456", "followedAt": "2024-01-01"}

2. Embedding (MongoDB)

Embed related data inside the parent document.

{
    "_id": "user_123",
    "username": "alice",
    "posts": [
        {
            "postId": "post_1",
            "content": "Hello world",
            "createdAt": "2024-03-20",
            "comments": [
                {"userId": "user_456", "text": "Great post!", "createdAt": "2024-03-21"}
            ]
        }
    ]
}

Pros: Single query gets everything, atomic updates.
Cons: 16MB document limit, data duplication, harder to query partial data.

3. Referencing (MongoDB)

Store references (like foreign keys) instead of embedding.

// users collection
{"_id": "user_123", "username": "alice"}

// posts collection
{"_id": "post_1", "userId": "user_123", "content": "Hello world"}

// comments collection  
{"_id": "comment_1", "postId": "post_1", "userId": "user_456", "text": "Great post!"}

Pros: No duplication, flexible queries, smaller documents.
Cons: Requires multiple queries (or $lookup aggregation).

NoSQL Decision Matrix

Pattern	When to Use	Example
Embedding	Data is read together, 1:1 or 1:N, small subdocuments	User + Profile, Post + Comments
Referencing	M:N relationships, large subdocuments, data shared across entities	Users ↔ Groups, Posts ↔ Tags
Single Table	DynamoDB, known access patterns, minimize queries	All user data in one table
Bucket Pattern	Time-series data, fixed-size buckets	IoT sensor data, log storage
Schema Per Tenant	Multi-tenant SaaS	Each tenant gets its own collection

Real-World Example: Social Media Data Model

Full SQL Schema:

-- Users
CREATE TABLE users (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(30) NOT NULL UNIQUE,
    email VARCHAR(255) NOT NULL UNIQUE,
    password_hash VARCHAR(255) NOT NULL,
    display_name VARCHAR(100),
    avatar_url VARCHAR(500),
    bio TEXT,
    follower_count INT DEFAULT 0,
    following_count INT DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Posts (denormalized with username for fast reads)
CREATE TABLE posts (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    user_id BIGINT NOT NULL,
    username VARCHAR(30) NOT NULL,
    user_avatar_url VARCHAR(500),
    content TEXT NOT NULL,
    image_url VARCHAR(500),
    likes_count INT DEFAULT 0,
    comments_count INT DEFAULT 0,
    visibility ENUM('public','private') DEFAULT 'public',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id),
    INDEX idx_user_posts (user_id, created_at DESC),
    INDEX idx_feed (visibility, created_at DESC)
);

-- Comments (self-referencing for replies)
CREATE TABLE comments (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    post_id BIGINT NOT NULL,
    user_id BIGINT NOT NULL,
    username VARCHAR(30) NOT NULL,
    content TEXT NOT NULL,
    parent_comment_id BIGINT NULL,
    likes_count INT DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (post_id) REFERENCES posts(id),
    FOREIGN KEY (user_id) REFERENCES users(id),
    FOREIGN KEY (parent_comment_id) REFERENCES comments(id),
    INDEX idx_post_comments (post_id, created_at)
);

-- Follows (many-to-many self-referencing)
CREATE TABLE follows (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    follower_id BIGINT NOT NULL,
    followee_id BIGINT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (follower_id) REFERENCES users(id),
    FOREIGN KEY (followee_id) REFERENCES users(id),
    UNIQUE KEY unique_follow (follower_id, followee_id),
    INDEX idx_followee (followee_id)
);

-- Likes (many-to-many)
CREATE TABLE likes (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    user_id BIGINT NOT NULL,
    post_id BIGINT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id),
    FOREIGN KEY (post_id) REFERENCES posts(id),
    UNIQUE KEY unique_like (user_id, post_id),
    INDEX idx_post_likes (post_id)
);

ER Diagram (Text):

┌─────────┐     ┌──────────┐     ┌──────────┐
│  users  │     │  posts   │     │ comments │
│─────────│     │──────────│     │──────────│
│ id  (PK)│──┐  │ id  (PK) │──┐  │ id  (PK) │
│ username│  │  │ user_id  │  │  │ post_id  │──┘
│ email   │  ├──│ username │  ├──│ user_id  │
│ ...     │  │  │ content  │  │  │ parent_id│──┐
└─────────┘  │  │ likes_n  │  │  │ ...      │  │
             │  └──────────┘  │  └──────────┘  │
             │                │                 │
             │  ┌──────────┐  │  (self-ref)     │
             │  │ follows  │  │  ◀──────────────┘
             │  │──────────│  │
             ├──│ follower │  │
             ├──│ followee │──┘
             │  │ ...      │
             │  └──────────┘
             │
             │  ┌──────────┐
             │  │  likes   │
             │  │──────────│
             ├──│ user_id  │
             │  │ post_id  │
             │  │ ...      │
             │  └──────────┘
             │
             └──── (users referenced by username in posts/comments)

Key Takeaways for Data Modeling

1. Start with entities and relationships before writing SQL
2. Normalize by default, denormalize only when you have a proven read performance need
3. Index foreign keys and frequently queried columns
4. Choose the right ID strategy for your scale (UUID for distributed, Snowflake for time-ordered)
5. Soft deletes preserve data and allow recovery
6. Pre-compute counters (likes_count, comments_count) to avoid COUNT queries
7. For NoSQL, design around access patterns, not data structure