Where Should the Foreign Key Go?

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
);

Enter fullscreen mode Exit fullscreen mode

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
);

Enter fullscreen mode Exit fullscreen mode

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.

Enter fullscreen mode Exit fullscreen mode

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

Enter fullscreen mode Exit fullscreen mode

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)
);

Enter fullscreen mode Exit fullscreen mode

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,
    // ...
]);

Enter fullscreen mode Exit fullscreen mode

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

Enter fullscreen mode Exit fullscreen mode

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;

Enter fullscreen mode Exit fullscreen mode

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)
);

Enter fullscreen mode Exit fullscreen mode

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;

Enter fullscreen mode Exit fullscreen mode

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.