Database Design: Getting Relationships Right From the Start

by

 

Published: November 2025
Author: Chris Beaver
Category: Development, Database, Architecture

Bad database design is technical debt that compounds over time. I’ve inherited enough poorly designed databases to know: it’s worth getting this right from the beginning.

The cost of fixing a bad schema after you have production data and dependent code is significant. The cost of thinking through your data model upfront is relatively small.

Start With the Domain, Not the Database

Before you create any tables, understand the domain you’re modeling.

What entities exist in this system? What are their attributes? How do they relate to each other?

Sketch this on paper. Use a whiteboard. Talk through it. Get the business logic right before you think about SQL.

The Core Relationships

Most database design comes down to three relationship types:

One-to-Many
A user has many posts. A customer has many orders. This is the most common relationship and the easiest to implement—just add a foreign key.

CREATE TABLE users (
    id BIGINT PRIMARY KEY,
    name VARCHAR(255)
);

CREATE TABLE posts (
    id BIGINT PRIMARY KEY,
    user_id BIGINT REFERENCES users(id),
    title VARCHAR(255),
    content TEXT
);

Many-to-Many
A post can have many tags. A tag can be on many posts. Requires a junction table.

CREATE TABLE posts (
    id BIGINT PRIMARY KEY,
    title VARCHAR(255)
);

CREATE TABLE tags (
    id BIGINT PRIMARY KEY,
    name VARCHAR(100)
);

CREATE TABLE post_tag (
    post_id BIGINT REFERENCES posts(id),
    tag_id BIGINT REFERENCES tags(id),
    PRIMARY KEY (post_id, tag_id)
);

One-to-One
A user has one profile. Less common but useful when you want to split data for performance or organizational reasons.

Polymorphic Associations: Use With Caution

Polymorphic relationships let one table relate to multiple other tables. A “comment” might belong to a post, a photo, or a video.

Laravel makes this easy:

class Comment extends Model
{
    public function commentable()
    {
        return $this->morphTo();
    }
}

The database stores both an ID and a type:

CREATE TABLE comments (
    id BIGINT PRIMARY KEY,
    commentable_id BIGINT,
    commentable_type VARCHAR(255), -- 'App\Models\Post' or 'App\Models\Photo'
    content TEXT
);

This is flexible but has downsides:

  • Can’t use foreign key constraints to enforce referential integrity
  • Queries are more complex
  • Database can’t optimize as effectively

When to use polymorphic relationships:
When you truly need the same behavior across unrelated entities and the flexibility outweighs the complexity cost.

When to avoid them:
When separate tables would be clearer or when you need strong referential integrity.

Normalization: The Right Amount

Database normalization is about eliminating redundancy. The classic normal forms (1NF, 2NF, 3NF) teach important principles, but dogmatic adherence can lead to over-normalized schemas that are hard to query.

General Guideline:
Normalize to third normal form (3NF) by default. Denormalize deliberately when you have good reason.

Good reasons to denormalize:
– Query performance (avoiding expensive joins)
– Read-heavy tables where updates are rare
– Calculated values that are expensive to compute

Bad reasons to denormalize:
– It seemed easier at the time
– Avoiding writing joins

Indexes: Not Optional

Indexes make queries fast, but they slow down writes and consume space. The balance:

Always index:
– Foreign keys
– Columns used in WHERE clauses frequently
– Columns used in ORDER BY
– Unique constraints

Consider indexing:
– Columns used in JOIN conditions
– Columns used in GROUP BY
– Composite indexes for multi-column queries

Don’t over-index:
– Every column “just in case”
– Tables with heavy write traffic

Laravel migrations make this straightforward:

Schema::create('posts', function (Blueprint $table) {
    $table->id();
    $table->foreignId('user_id')->constrained()->onDelete('cascade');
    $table->string('title');
    $table->text('content');
    $table->timestamp('published_at')->nullable();
    $table->timestamps();

    // Indexes
    $table->index('published_at'); // Often queried
    $table->index(['user_id', 'published_at']); // Common query pattern
});

Null Values: Define Your Policy

NULL means “unknown” or “not applicable.” It’s not the same as an empty string or zero.

Decide your policy on nulls:

Allow nulls for:
– Optional fields (middle name, apartment number)
– Data that might not exist yet (end_date on ongoing projects)

Require values for:
– Fields essential to the entity’s meaning
– Fields where “not set” and “set to empty” are the same

Be consistent. If email can’t be null on users, it should never be null anywhere users have emails.

Timestamps: More Than You Think

Basic timestamps (created_at, updated_at) are standard. But consider:

Soft Deletes
Keep a deleted_at timestamp instead of actually deleting rows. This gives you an audit trail and makes it easy to restore data.

Published/Scheduled Content
If content can be scheduled, you need published_at, not just a boolean flag.

Versioning
For data that changes over time (prices, addresses), consider temporal tables with valid_from and valid_to timestamps.

Data Types Matter

Choosing the right data type saves space and enables database optimizations.

Strings
– Use VARCHAR(255) for most text fields
– TEXT for longer content
– CHAR for fixed-length strings (state codes, etc.)

Numbers
– BIGINT for IDs (you’ll run out of INTs eventually)
– DECIMAL for money (never use FLOAT for money)
– INTEGER for counts and quantities

Booleans
– Use actual BOOLEAN types when available
– Store as TINYINT(1) in MySQL

Dates/Times
– TIMESTAMP for points in time
– DATE for calendar dates (birthdays)
– TIME for time of day (opening hours)

Constraints: Your First Line of Defense

Database constraints enforce rules at the database level, where they can’t be bypassed.

Foreign Keys
Ensure referential integrity. Can’t insert an order with a non-existent customer_id.

FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE CASCADE

Unique Constraints
Prevent duplicate data at the database level.

UNIQUE(email)

Check Constraints
Enforce business rules.

CHECK (price > 0)
CHECK (end_date > start_date)

Laravel’s validation is great, but constraints ensure data integrity even if code has bugs.

JSON Columns: When and Why

Modern databases support JSON columns (JSONB in PostgreSQL). They’re useful for:

  • Configuration data
  • Flexible metadata
  • Semi-structured data that doesn’t fit neatly into columns

But don’t use JSON as a crutch for lazy schema design. If data is structured and you’ll query it regularly, use proper columns and tables.

Good use of JSON:

-- User preferences that vary and rarely need filtering
preferences JSONB

Bad use of JSON:

-- Order data that should be in a proper schema
order_details JSONB

Query Patterns Drive Schema Design

Design your schema based on how you’ll query it.

If you frequently need “all posts by a user, sorted by date,” make sure you have the right indexes:

CREATE INDEX idx_posts_user_date ON posts(user_id, created_at);

If you need counts, consider denormalization:

-- Instead of counting comments every time
ALTER TABLE posts ADD COLUMN comment_count INTEGER DEFAULT 0;

-- Update via triggers or application code

Migrations: The Right Way

Database changes should be versioned in migrations, never done manually in production.

Good migration practices:
– Make them reversible (implement down() methods)
– Test them on production-like data
– Consider data migration separately from schema migration
– Plan for zero-downtime deployments

Risky migrations:
– Adding NOT NULL columns to large tables
– Dropping columns (do a two-phase deploy)
– Changing column types
– Complex data transformations

The “When To Refactor” Decision

Even well-designed schemas need changes as requirements evolve. When do you refactor?

Refactor when:
– Queries are consistently slow despite indexes
– You’re duplicating data in ways that create inconsistency
– Business logic has changed and schema doesn’t match
– You’re working around the schema regularly

Don’t refactor when:
– The current design works adequately
– Changes would require extensive application updates
– You’re refactoring for theoretical future needs

Testing Your Schema

You should test your database design:

  • Can you write the queries you need efficiently?
  • Do your constraints actually prevent bad data?
  • Can you restore from backups?
  • Do migrations work on production-sized datasets?

I’ve caught many schema issues by loading production-scale test data and running realistic queries.

Wrapping Up

Good database design is about:

  1. Understanding the domain deeply
  2. Modeling relationships accurately
  3. Using appropriate constraints
  4. Indexing for your query patterns
  5. Planning for changes

Get it right early. Your future self (and your database performance) will thank you.

Database design and optimization is a core part of our development services. If you’re working with a problematic schema or planning a new system, let’s talk.

Leave a Comment