Minimal Modeling Database Design

Overview

Minimal Modeling is a database design methodology that bridges the gap between vague business requirements and concrete SQL schemas through systematic decoupling of logical and physical concerns.

Core Principle: Separate the "what exists" (business logic) from "how to store it" (technical implementation) to reduce cognitive load and enable validation by non-technical stakeholders before writing any SQL.

When to Use

Use this skill when:

• Translating business requirements ("I need a website for...") into database schemas
• Starting a new project with unclear data requirements
• Validating database design with non-technical stakeholders
• Experiencing analysis paralysis from too many implementation details early
• Need to explain database structure to business users

Do NOT use when:

• Database schema already exists and you're just querying it
• Working with predefined ORM models that can't be changed
• Technical constraints (like existing tables) must drive the design

The Two-Phase Process

Phase A: Logical Model (Business Domain)

Focus entirely on business concepts without technical implementation.

Phase B: Physical Schema (Implementation)

Translate business concepts into tables, columns, and data types.

Phase A: Logical Model

Step 1: Gather Requirements

Start with informal text or transcript describing the system.

Step 2: Find Anchors (The "Nouns")

Definition: Entities that exist independently with unique IDs. They represent things you count, not data itself.

Validation: Use two sentences to test if a noun is an Anchor:

Counting Sentence: "We have 1,000 [Nouns] in our database."
Adding Sentence: "The system inserts another [Noun] into the database."

If both sentences sound natural, it's an Anchor.

Examples:

• ✅ User, Post, Order, Product (you count these)
• ❌ Email, Title, Price (these are data about things)

Step 3: Find Attributes (The "Data")

Definition: Information that describes an Anchor.

Validation: Define each attribute with a Human-Readable Question:

"What is the [attribute name]?"
"Is this [condition]?"

Examples:

• User → "What is the email address?" (String)
• Product → "What is the price?" (Decimal)
• Room → "Is this wheelchair accessible?" (Boolean)

Common Attribute Types:

• Strings: Names, descriptions, addresses
• Numbers: Counts, IDs, quantities
• Money: Prices, balances (always use DECIMAL, never Float)
• Booleans: Flags, yes/no questions
• Dates/Times: Created dates, scheduled events
• Binary: Files, images (or S3 URLs)

Step 4: Find Links (The "Relationships")

Definition: Connections between two Anchors (or an Anchor to itself).

Validation: Use the Two-Sentence Rule to determine cardinality:

Sentence 1: "An [Anchor A] [verb] [one/several] [Anchor B]."
Sentence 2: "An [Anchor B] [verb] [one/several] [Anchor A]."

Cardinality Patterns:

Sentence Pattern	Cardinality	Implementation
"A has several B" + "B belongs to one A"	1:N (One-to-Many)	Foreign key in B table
"A has one B" + "B belongs to one A"	1:1 (One-to-One)	Foreign key in either table
"A has several B" + "B has several A"	N:M (Many-to-Many)	Junction table

Examples:

User ↔ Order
"A User can place several Orders."
"An Order is placed by one User."
Result: 1:N → Add user_id to orders table

User ↔ Post (likes)
"A User can like several Posts."
"A Post can be liked by several Users."
Result: N:M → Create users_liked_posts junction table

Step 5: Verify Completeness

Go back to original requirements. Highlight every word covered by your model.

Missing highlights indicate:

• Missed Anchors
• Missed Attributes
• Missed Links

Continue iterating until all business concepts are captured.

Phase B: Physical Schema

Step 6: Choose Table Names

Convention: Pluralize the Anchor name

• User → users
• Post → posts
• Order → orders

Step 7: Choose Column Types

Critical Type Decisions:

Logical Type	SQL Type	Notes
ID	INTEGER or BIGINT	Use BIGINT for massive scale (billions of rows)
String	VARCHAR(N)	Default to empty string, not NULL
Money	DECIMAL(M,D)	NEVER use Float for money
Boolean	BOOLEAN or TINYINT	Depends on database
Timestamp (past)	TIMESTAMP in UTC	Store server events in UTC
Timestamp (future)	Store local time + timezone	For user-scheduled events
Binary	BLOB or S3 URL	Consider external storage for large files

NULL vs Default Values:

• Prefer meaningful defaults over NULL when possible
• Strings: Default to '' (empty string)
• Numbers: Consider if 0 makes sense or if NULL is semantic

Step 8: Implement Links

One-to-Many (1:N): Add foreign key column to the "Many" side table.

-- User has many Orders
CREATE TABLE orders (
  id INTEGER PRIMARY KEY,
  user_id INTEGER NOT NULL,  -- Foreign key to users
  total_amount DECIMAL(10,2),
  FOREIGN KEY (user_id) REFERENCES users(id)
);

Many-to-Many (N:M): Create a junction table with foreign keys to both sides.

-- Users can like many Posts, Posts can be liked by many Users
CREATE TABLE users_liked_posts (
  user_id INTEGER NOT NULL,
  post_id INTEGER NOT NULL,
  liked_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (user_id, post_id),
  FOREIGN KEY (user_id) REFERENCES users(id),
  FOREIGN KEY (post_id) REFERENCES posts(id)
);

One-to-One (1:1): Add foreign key to either table (choose based on query patterns).

-- User has one Profile
CREATE TABLE profiles (
  id INTEGER PRIMARY KEY,
  user_id INTEGER UNIQUE NOT NULL,  -- UNIQUE enforces 1:1
  bio TEXT,
  FOREIGN KEY (user_id) REFERENCES users(id)
);

Quick Reference: Validation Checklist

Anchors:

• Pass counting sentence test
• Pass adding sentence test
• Have unique IDs
• Exist independently

Attributes:

• Have human-readable question
• Belong to a specific Anchor
• Are single-valued (not lists)
• Appropriate SQL type chosen

Links:

• Two-sentence validation complete
• Cardinality determined (1:1, 1:N, N:M)
• Implementation strategy chosen
• Foreign keys properly constrained

Physical Schema:

• Table names pluralized
• Primary keys defined
• Foreign keys with constraints
• Column types match logical types
• Defaults specified where appropriate
• Indexes planned for common queries

Common Mistakes

Mistake 1: Mixing Logical and Physical Too Early

❌ BAD: "Should User have a VARCHAR(255) email?"
✅ GOOD: "Does User have an email address? [Yes] → Later: VARCHAR(255)"

Mistake 2: Attributes as Anchors

❌ BAD: EmailAddress as an Anchor
✅ GOOD: Email as an Attribute of User
Ask: "We have 1,000 emails in our database?" (sounds weird → not an Anchor)

Mistake 3: Using Float for Money

❌ BAD: price FLOAT
✅ GOOD: price DECIMAL(10,2)
Reason: Float has rounding errors; DECIMAL is exact

Mistake 4: Wrong Cardinality Side

❌ BAD: Adding order_id to users table (1:N wrong side)
✅ GOOD: Adding user_id to orders table (1:N correct side)
Rule: Foreign key goes on the "Many" side

Mistake 5: Missing Junction Tables

❌ BAD: Adding post_ids array column to users (N:M)
✅ GOOD: Create users_liked_posts junction table
Reason: Relational databases don't handle arrays well

Mistake 6: Storing Timezone as String

❌ BAD: "America/New_York" string for all times
✅ GOOD:
  - Past events: UTC timestamp
  - Future events: Local time + timezone
Reason: Different semantic needs

Secondary Data

Definition: Data that is duplicated, cached, or aggregated for performance. Not the source of truth.

Examples:

• total_posts count on User (redundant with COUNT of posts)
• last_login_at on User (duplicated from sessions table)

When to Use:

• Performance optimization after proving it's needed
• Denormalization for read-heavy workloads

Always:

• Document as secondary data
• Have mechanism to rebuild from source of truth
• Consider if query optimization or indexing is better solution

Working with Query Builders and ORMs

When using query builders like Kysely or ORMs, the process remains the same:

1. Logical Model first (Anchors, Attributes, Links)
2. Define schema types instead of raw SQL
3. Generate migrations from type definitions

// After logical modeling, translate to Kysely schema:

// Define database schema interface
interface Database {
  users: UserTable; // Anchor: User
  orders: OrderTable; // Anchor: Order
}

// Anchor: User
interface UserTable {
  id: Generated<number>;
  email: string; // Attribute
  created_at: Generated<Timestamp>;
}

// Anchor: Order
interface OrderTable {
  id: Generated<number>;
  user_id: number; // Link (1:N) - Foreign key to users
  total: string; // Decimal stored as string for precision
  created_at: Generated<Timestamp>;
}

// Create Kysely instance
const db = new Kysely<Database>({
  dialect: new PostgresDialect({ pool }),
});

// Queries respect the logical model:
const userOrders = await db
  .selectFrom('orders')
  .where('user_id', '=', userId) // Following 1:N relationship
  .selectAll()
  .execute();

Time and Timezone Best Practices

Past Events (already happened):

-- Server events, log entries, message timestamps
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP  -- Store in UTC

Future Events (scheduled by user):

-- Concert times, meeting schedules, alarm times
event_time TIMESTAMP,
event_timezone VARCHAR(50)  -- e.g., "America/New_York"

Reason: Past events have definite UTC times. Future events need local context because timezone rules may change (DST, political changes).

Iterative Refinement

Database design is iterative. After creating initial schema:

1. Test with sample data - Does it feel natural?
2. Write common queries - Are they simple or complex?
3. Show to stakeholders - Do they understand the model?
4. Adjust and repeat - Refine based on feedback

Signs you need to revisit:

• Queries require 5+ JOINs regularly
• Stakeholders confused by table names
• Constant use of NULL checks in queries
• Performance issues with simple queries
• Adding "workaround" columns frequently

Real-World Impact

Benefits:

• Non-technical stakeholders can validate the logical model
• Catch misunderstandings before writing SQL
• Faster iterations (change model, not migrations)
• Clear documentation of business logic
• Reduced cognitive load during design

When you skip this:

• Premature optimization (wrong indexes too early)
• Schema doesn't match business needs
• Expensive refactoring later
• Technical debt from implementation-driven design