DEV Community: Hugo do Carmo

Where Should the Foreign Key Go?

Hugo do Carmo — Thu, 23 Jul 2026 02:24:51 +0000

This is Part II of a series about vertical partitioning for large text columns. Part I explains why we separated the large text from the main table.

In Part I, we split one books table into two tables:

CREATE TABLE books (
    id               BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    title            VARCHAR(255) NOT NULL,
    author           VARCHAR(255) NOT NULL,
    isbn             VARCHAR(20) NOT NULL,
    published_year   SMALLINT UNSIGNED NOT NULL
);

CREATE TABLE book_contents (
    book_id     BIGINT UNSIGNED PRIMARY KEY,
    resume      TEXT NOT NULL,
    FOREIGN KEY (book_id) REFERENCES books(id) ON DELETE CASCADE
);

The foreign key is in book_contents, and it points to books. Part I used this design without explaining it in detail.

However, we could put the foreign key in the other table. We could add a content_id column to books instead. Both designs are valid SQL, but they describe different relationships and create different responsibilities in the application.

There is also a third design for systems that need to keep several versions of a book's content. We will look at all three options.

Option A: Put the foreign key in the content table

This is the design used in Part I:

CREATE TABLE books (
    id      BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    title   VARCHAR(255) NOT NULL
);

CREATE TABLE book_contents (
    book_id  BIGINT UNSIGNED PRIMARY KEY,
    resume   TEXT NOT NULL,
    FOREIGN KEY (book_id) REFERENCES books(id) ON DELETE CASCADE
);

Here, book_contents.book_id is both a primary key and a foreign key.

The foreign key connects the content to a book. The primary key guarantees that each book can have no more than one content row. The content row uses the same identity as the book because it is an extension of that book.

Creating a book

The books table does not depend on book_contents. We can create a book first and add its content later:

$book = Book::create([
    'title' => 'Dune',
    'author' => 'Frank Herbert',
    // ...
]);

// We can create the content now, later, or not at all.

This is useful when the content is optional or is not ready when the book is created. A book without content is simply a row in books with no related row in book_contents.

With Eloquent, the relation returns null when the content does not exist:

$book->content; // null when the book has no content yet

Deleting a book

The foreign key uses ON DELETE CASCADE. When we delete a book, the database also deletes its content.

This matches the domain: the content belongs to the book and has no purpose without it. The database prevents a book_contents row from existing without a valid book.

Limitation

This design is a strict one-to-one relationship. It does not support several content rows for one book. It also does not support sharing one content row between several books.

For the example in Part I, these limits are useful because each content row belongs to exactly one book. If the requirements change, however, we may need a different structure.

Option B: Put the foreign key in the books table

We can reverse the relationship and let books reference book_contents:

CREATE TABLE book_contents (
    id      BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    resume  TEXT NOT NULL
);

CREATE TABLE books (
    id          BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    title       VARCHAR(255) NOT NULL,
    content_id  BIGINT UNSIGNED NOT NULL UNIQUE,
    FOREIGN KEY (content_id) REFERENCES book_contents(id)
);

The UNIQUE constraint is important if this must be a one-to-one relationship. Without it, several books could reference the same content row.

In this design, a book depends on a content row. Because content_id is NOT NULL, we must create the content first and then create the book:

$content = BookContent::create([
    'resume' => $resumeText,
]);

$book = Book::create([
    'title' => 'Dune',
    'content_id' => $content->id,
    // ...
]);

Every place that creates a book must follow this order. This includes API endpoints, importers, admin pages, tests, and database seeders.

Optional content

If a book can exist without content, content_id must be nullable:

content_id BIGINT UNSIGNED NULL UNIQUE

Queries must then allow for books with no content, usually with a LEFT JOIN:

SELECT b.title, c.resume
FROM books b
LEFT JOIN book_contents c ON c.id = b.content_id;

This works, but the main table now contains a reference to an optional detail row. The book must know about the storage structure of one of its attributes.

Deleting a book

When we delete a book, the content row is not deleted automatically. The foreign key points from books to book_contents, so a cascade in this direction cannot clean up the content after its book is removed.

The application needs separate cleanup logic. If one deletion path forgets this step, unused content rows can remain in the database.

This is not always a problem. Sometimes the referenced row is independent and should remain after the book is deleted. For example, a cover image may be reused by several book editions. In that case, a books.cover_image_id column can be a good design.

Option B makes sense when the referenced record has its own identity, can exist independently, or can be shared. It is usually less natural when the referenced row is only a private detail of one book.

Option C: Use a separate primary key for versioned content

The shared primary key from Option A allows only one content row per book. That is exactly what we want when the system only stores the current content.

But imagine that we need to keep every version. A book may have version 1, version 2, and version 3 of its content. In this case, book_id cannot be the primary key because the same book ID must appear in several rows.

We can give each content version its own primary key and keep book_id as a separate foreign key:

CREATE TABLE book_contents (
    id          BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    book_id     BIGINT UNSIGNED NOT NULL,
    version     INT UNSIGNED NOT NULL,
    resume      TEXT NOT NULL,
    created_at  TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,

    FOREIGN KEY (book_id) REFERENCES books(id) ON DELETE CASCADE,
    UNIQUE (book_id, version)
);

The two identifiers now have different purposes:

id identifies one specific content version.
book_id identifies the book that owns that version.

The UNIQUE (book_id, version) constraint prevents two rows from using the same version number for the same book. Different books can still have the same version numbers.

This is no longer a one-to-one relationship. It is a one-to-many relationship: one book has many content versions. However, the ownership direction stays the same as in Option A. The content versions depend on the book, and deleting the book can delete all of them through ON DELETE CASCADE.

To load the newest version, we can order the versions from highest to lowest:

SELECT bc.*
FROM book_contents bc
WHERE bc.book_id = ?
ORDER BY bc.version DESC
LIMIT 1;

Another design could store a reference to the current version, but that adds more rules to keep the reference correct. If reading the newest version by its number is fast enough, the simpler design is often easier to maintain.

Use this option only when version history is a real requirement. If the system needs only one current content row, Option A gives stronger one-to-one rules with a simpler schema.

How to choose

The main question is: which record can exist on its own, and which record depends on the other?

A book is still a book before its summary is written. The summary has no meaning without the book it describes. For that reason, the dependent table should normally contain the foreign key that points to the independent table.

For this example, the choices are:

Use Option A when each book has zero or one content row and the content belongs only to that book.
Use Option B when the referenced content has its own identity, can exist independently, or may be shared.
Use Option C when each book needs several historical versions of its content.

The shared primary key in Option A expresses an important rule: the content row is an extension of the book. A separate primary key in Option C expresses a different rule: every version is a separate row, but all versions still belong to one book.

The takeaway

Vertical partitioning keeps the large text column out of common book queries in all three designs. The position of the foreign key does not change that performance benefit, but it changes the meaning of the relationship.

When the content is a private detail of a book, put the foreign key in book_contents. This allows the book to exist before its content, supports natural cleanup with ON DELETE CASCADE, and prevents content from existing without an owner.

If only one content row is allowed, make book_id both the primary key and the foreign key. If the content must be versioned, give each version its own primary key and use book_id as a separate foreign key.

The best schema is not only the one that joins correctly. It is the one that expresses the real rules of the domain and lets the database help enforce them.

When Large Text Belongs in a Separate Table

Hugo do Carmo — Thu, 16 Jul 2026 00:02:02 +0000

The Hidden Cost of "Just One More Column": When Large Text Belongs in a Separate Table

This is Part I of a series about vertical partitioning and one-to-one table design.

Picture a simple books table. It contains the fields you would expect: title, author, ISBN, publication year, and a summary column with a few paragraphs about the book.

CREATE TABLE books (
    id               BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    title            VARCHAR(255) NOT NULL,
    author           VARCHAR(255) NOT NULL,
    isbn             VARCHAR(20) NOT NULL,
    published_year   SMALLINT UNSIGNED NOT NULL,
    summary          TEXT NOT NULL,
    created_at       TIMESTAMP NULL DEFAULT NULL,
    updated_at       TIMESTAMP NULL DEFAULT NULL
);

There is nothing inherently wrong with this design. The summary belongs to the book, the table is easy to understand, and keeping the data together may be the best choice for many applications.

A large text field does not automatically need its own table.

The problem appears when the application's access pattern changes. The summary may be required on a book details page, but not in search results, dropdowns, reports, or administrative listings. If the data-access technology loads every mapped column by default, a reasonable schema can begin producing unnecessary work.

The query that quietly becomes expensive

Imagine a page that lists every book published after 2010. It only needs three fields:

SELECT id, title, published_year
FROM books
WHERE published_year > 2010;

That query is precise. The database returns only what the page needs.

However, many ORM calls that load complete entities generate something closer to this:

SELECT *
FROM books
WHERE published_year > 2010;

Now every summary is returned as well. If the result contains thousands of books, several kilobytes of unused text per row can become tens of megabytes transferred from the database and allocated inside the application.

The application never reads the summaries, but it still pays the cost of fetching and materializing them.

This behavior is not limited to one language or framework. Full-entity hydration is a common ORM default. Some persistence tools provide deferred or lazy scalar fields, while others make column selection a decision that must be repeated in each query.

That difference in framework design can influence the database design.

Does the database already handle large text efficiently?

Usually, yes.

Modern relational databases have mechanisms for handling large text and binary values without treating every byte as part of a small, fixed-width row. The exact strategy depends on the database engine, value size, and storage configuration, see MySQL InnoDB Row Formats and PostgreSQL TOAST.

More importantly, when a query requests only id and title, the database does not need to return the summary column to the application.

So the main issue is not simply that the table contains text. The issue is that the application layer may continue asking for that text when it is not needed.

This distinction matters. Moving every TEXT field into another table would add joins, relationships, migrations, and lifecycle rules without necessarily improving anything. Vertical partitioning becomes useful only when the access pattern and the framework behavior make the original layout costly or difficult to control.

Why explicit column selection may not be enough

The direct solution is to select only the required columns:

SELECT id, title, author, published_year
FROM books;

Most ORMs also provide a way to express this. Technically, that may solve the problem.

The difficulty is consistency. Every new list page, API endpoint, export, scheduled job, or relationship query becomes another place where someone must remember to exclude the large field.

That approach can work well when:

the project has clear query conventions;
the data-access layer centralizes projections;
performance tests detect accidental full-row loading; or
the ORM supports deferred fields as part of the model mapping.

It becomes less reliable when column selection is repeated throughout a large codebase and the framework provides no model-level way to declare that a field is rarely needed.

In that situation, the risk is not that developers are careless. The risk is that the safer behavior is optional, while the expensive behavior is the default.

When a separate table becomes the safer option

Suppose the summary has the following characteristics:

it is significantly larger than the other book attributes;
it is needed only on a small number of screens;
books are frequently loaded in lists or relationships;
the ORM normally hydrates every mapped column; and
there is no reliable project-wide mechanism for deferring the field.

These conditions make vertical partitioning a reasonable option.

-- Frequently accessed book data
CREATE TABLE books (
    id               BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    title            VARCHAR(255) NOT NULL,
    author           VARCHAR(255) NOT NULL,
    isbn             VARCHAR(20) NOT NULL,
    published_year   SMALLINT UNSIGNED NOT NULL,
    created_at       TIMESTAMP NULL DEFAULT NULL,
    updated_at       TIMESTAMP NULL DEFAULT NULL,

    INDEX idx_books_published_year (published_year)
);

-- Less frequently accessed large content
CREATE TABLE book_contents (
    book_id     BIGINT UNSIGNED PRIMARY KEY,
    summary     TEXT NOT NULL,
    created_at  TIMESTAMP NULL DEFAULT NULL,
    updated_at  TIMESTAMP NULL DEFAULT NULL,

    CONSTRAINT fk_book_contents_book
        FOREIGN KEY (book_id) REFERENCES books(id)
        ON DELETE CASCADE
);

The table split does not make the model more normalized. The original table was already valid from a normalization perspective.

Instead, the split organizes the data by access pattern:

books contains the attributes that are loaded frequently;
book_contents contains the heavier content that is requested less often.

This makes accidental retrieval harder. A query against books cannot return the summary because the column is no longer there.

A Laravel example, but not a Laravel-only problem

In Eloquent, the relationship could be represented like this:

class Book extends Model
{
    public function content()
    {
        return $this->hasOne(BookContent::class, 'book_id');
    }
}

class BookContent extends Model
{
    protected $table = 'book_contents';
    protected $primaryKey = 'book_id';
    public $incrementing = false;

    public function book()
    {
        return $this->belongsTo(Book::class, 'book_id');
    }
}

The common listing query loads only the smaller table:

$books = Book::where('published_year', '>', 2010)->get();

The details page requests the content explicitly:

$book = Book::with('content')->findOrFail($id);

Laravel is only the implementation used in this example. The same architectural pressure can appear in any stack where complete entities are loaded by default and individual fields cannot be deferred clearly at the mapping level.

If another ORM already provides reliable lazy or deferred scalar fields, keeping the summary in books may remain the simpler and better design.

Why the shared primary key matters

In book_contents, book_id is both the primary key and the foreign key.

book_id BIGINT UNSIGNED PRIMARY KEY

This guarantees that each book can have at most one matching content row. It also avoids adding an unrelated surrogate identifier to a table whose identity is entirely dependent on the book.

The relationship is one-to-one because the database enforces it, not only because the application expects it.

However, another design decision remains: why does book_contents reference books? Why not store a content_id inside books and reverse the foreign-key direction?

Both layouts can represent a one-to-one relationship, but they differ in insert order, optionality, deletion behavior, and orphan management. That choice deserves a separate discussion.

The takeaway

Keeping a large text field in its original table is often completely reasonable. It is simple, cohesive, and supported efficiently by modern databases.

The design becomes questionable when the field is rarely needed, frequently fetched by accident, and difficult to defer because of the framework or ORM being used.

At that point, the technology has created a constraint that the schema may need to address. Moving the field into a one-to-one companion table can turn an optional query convention into a structural guarantee.

That is the real lesson: do not split a table merely because a column contains text. Split it when data size, access frequency, and framework behavior consistently work against the original design.

Part II examines the next decision: where the foreign key should live in a one-to-one vertical partition, what each direction communicates, and which failure modes appear when the relationship is modeled incorrectly.