Designing a Normalized Schema for Narrative Text

1. The Rationale for Normalization: Avoiding Anomalies

Before diving into the "how" of schema design, we must first understand the "why." What happens when a database schema is poorly designed? Imagine we tried to store all our project's data in one giant, flat table:

This design is rife with problems stemming from data redundancy. The author's name and date of birth are repeated for every single chunk of the book. This leads to three classes of problems, known as anomalies:

• Update Anomaly: If we discover Frank Herbert's date of birth was recorded incorrectly, we would have to update thousands of rows. If we miss even one, the database becomes internally inconsistent, violating data integrity.
• Insertion Anomaly: How do we add a new author to our database before we have any of their books? In this schema, we can't. We need a book, chapter, and chunk to create a row, so we cannot store information about an author independently.
• Deletion Anomaly: If we delete the last remaining chunk of a particular book, we might inadvertently delete the only record of that book's existence or its author's details from our database.

Database normalization is the formal process of organizing columns and tables to minimize data redundancy and eliminate these anomalies. The following video provides an excellent, practical introduction to why normalization is essential and the problems it solves.

2. The Normal Forms: A Step-by-Step Guide

Normalization is achieved by following a set of rules called Normal Forms. Each normal form represents a progressively stricter set of conditions. For most practical purposes, achieving the Third Normal Form (3NF) is the goal.

A helpful mnemonic for 3NF, which we'll unpack, is that every non-key attribute "must depend on the key, the whole key, and nothing but the key."

Let's continue with the "Learn Database Normalization" video, which brilliantly explains these forms.

For a more formal, academic perspective that aligns with your preference for university materials, you can review the following lecture notes from CMU. They formally define the properties of a normalized schema, such as lossless joins and dependency preservation.

3. Designing the Schema for the Narrative Intelligence Engine

Now, let's apply these principles to your project. We need to store books, which are composed of chapters, which are in turn composed of chunks of text. This is a natural hierarchy.

An Entity-Relationship Diagram (ERD) helps visualize the tables and their relationships.

Our goal is to create a similar structure for our narrative data.

Approach A: The Static, Multi-Table Schema

The most straightforward normalized design is to create a separate table for each entity in our hierarchy:

1. authors table: To store information about authors.

   • id: BIGINT PRIMARY KEY (a surrogate key)
   • name: VARCHAR(255)
   • birth_year: INT
   • ...other author-specific data

2. books table: To store information about books.

   • id: BIGINT PRIMARY KEY
   • author_id: BIGINT, a FOREIGN KEY referencing authors(id).
   • title: TEXT
   • publication_year: INT

3. chapters table:

   • id: BIGINT PRIMARY KEY
   • book_id: BIGINT, a FOREIGN KEY referencing books(id).
   • chapter_number: INT
   • title: TEXT

4. chunks table: The core text units for your RAG system.

   • id: BIGINT PRIMARY KEY
   • chapter_id: BIGINT, a FOREIGN KEY referencing chapters(id).
   • content: TEXT
   • chunk_index_in_chapter: INT (to maintain order)

This design is fully normalized to 3NF. There is no redundant data. We can add an author without a book, and deleting the last chunk of a chapter doesn't delete the chapter itself.

However, this design is rigid. What if a book has "Parts" that contain chapters? What about prefaces or appendices? What if a text has no chapters at all? We would need to add more tables or make columns nullable, which can complicate logic.

Approach B: The Dynamic, Adjacency List Model

A more flexible and powerful approach for hierarchical data is the Adjacency List model. Here, we use a single table with a self-referencing foreign key to model the hierarchy.

Imagine a single table, documents:

• id: BIGINT PRIMARY KEY
• parent_id: BIGINT, a FOREIGN KEY referencing documents(id). This is the key to the model.
• document_type: VARCHAR(50) (e.g., 'book', 'chapter', 'chunk', 'part')
• title: TEXT
• content: TEXT
• position: INT (to order siblings, e.g., chapter 1, chapter 2)

In this model:

• A book is a row where parent_id is NULL.
• A chapter of that book is a row whose parent_id is the id of the book row.
• A chunk of that chapter is a row whose parent_id is the id of the chapter row.

This design is incredibly flexible. It can represent any depth of hierarchy without schema changes. It treats all narrative components as a generic "document," which aligns beautifully with future plans for building a knowledge graph.

The following resources provide an excellent breakdown of how to model hierarchical data in PostgreSQL, focusing on the adjacency list model.

To see this self-referencing foreign key in action, the following short video provides a very clear visual explanation.

Your Task: Design and Implement the Initial Schema

Your task is to write the CREATE TABLE SQL statements for the initial version of your project's database. I recommend you use the Adjacency List model (Approach B) as it provides the long-term flexibility your project requires. However, starting with the static multi-table model (Approach A) is also acceptable if you want to begin with a simpler structure, as long as you document the trade-offs.

Deliverable:
In your project repository, create a new SQL file (e.g., schema.sql). In this file, write the CREATE TABLE statements for your chosen design.

For each table, you must:

1. Define a primary key. Use BIGINT and GENERATED ALWAYS AS IDENTITY for auto-incrementing surrogate keys.
2. Define foreign keys with REFERENCES to enforce relationships.
3. Choose appropriate data types for each column (TEXT, VARCHAR, INT, TIMESTAMPTZ for created/updated timestamps, etc.).
4. Add NOT NULL constraints where appropriate.

In your engineering journal, for each table and column, write a brief justification for your choice of data type and constraints. For example: "I chose TEXT for the title column instead of VARCHAR(255) because some literary works have very long titles, and in modern PostgreSQL, the performance difference is negligible for our use case, while the flexibility is valuable."

Conclusion

In this lesson, you've moved from a user of data to an architect of data. You've learned that normalization is a critical process for ensuring data integrity by eliminating redundancy and its associated update, insertion, and deletion anomalies. You explored the progressive rules of the normal forms, internalizing the 3NF heuristic: "depend on the key, the whole key, and nothing but the key."

Most importantly, you applied these principles to the core challenge of your capstone project: modeling hierarchical narrative data. You compared a rigid, static schema with a flexible, dynamic adjacency list model, a design choice that will have long-term implications for your system's evolution.

Key Takeaways:

• Normalization prevents data anomalies by reducing redundancy.
• Achieving 3NF is the standard for robust relational schema design.
• Hierarchical data, like book -> chapter -> chunk, can be modeled using separate tables or, more flexibly, with a self-referencing adjacency list.
• Choosing the right data types (BIGINT, TEXT, TIMESTAMPTZ) and constraints (PRIMARY KEY, FOREIGN KEY, NOT NULL) is a fundamental part of schema implementation.

In the next lesson, we will explore how to retrieve information from our newly designed, multi-table schema. Now that you have decomposed your data into related tables, you will learn to use JOINs to put it back together in meaningful ways, a direct consequence of the "lossless join" property that good normalization guarantees.