Database Schema Design | Golang Monolith Mastery

Moving from functional requirements and High-Level Design (HLD) to low-level implementation requires bridging business capabilities into a concrete data model. In relational monolithic architectures, the schema serves as the foundational contract for the entire application, dictating data integrity, query performance, and the boundaries of business domain logic.

1. The Architectural Bridge: From HLD to Schema Design

In modern software engineering, translating a concept into code follows an intentional pipeline:

Once the high-level components (such as reverse proxies, application servers, object storage, and relational storage) are chosen, engineers must decide how to model the domain. Designing the database schema before writing application logic is not merely a habit; it is a conscious architectural choice driven by the nature of the application.

2. Paradigms of Software Design: Data-First vs. Behavior-First

Software systems are generally approached through one of two central design paradigms: Data-First Design or Behavior-First Design. Selecting the wrong paradigm can introduce unnecessary cognitive overhead or lead to fragile data integrity.

Data-First Design (Data-Driven Design)

The data-first approach models the application by first identifying physical or conceptual "things" (entities).

• Mental Model: "Find the entities, map their relationships, create tables, and expose actions over them."
• Primary Artifact: The relational database schema, Entity-Relationship (ER) diagrams, and table structures.
• Workflow:
  1. Identify nouns in the requirements (e.g., User, Listing, Category).
  2. Map these nouns to database tables with primary and foreign keys.
  3. Construct CRUD (Create, Read, Update, Delete) endpoints and business queries on top of the established schema.
• Typical Use Cases: Content management systems (CMS), inventory trackers, classified platforms, and standard e-commerce sites where the user interface heavily revolves around listing, filtering, and editing records.

Behavior-First Design (Domain-Driven Design / Clean Architecture)

The behavior-first approach begins with the business workflows, domain logic, and complex business invariants, completely detaching the business layer from how data is persisted.

• Mental Model: "Model the business behavior first, validate domain invariants, and decide how to persist the state later."
• Primary Artifact: Aggregates, domain entities, value objects, domain events, and pure business logic decoupled from frameworks.
• Workflow:
  1. Identify verbs and business rules (e.g., PlaceOrder, CancelBooking, DisburseLoan).
  2. Implement rich domain models encapsulating complex business invariants and edge cases.
  3. Define storage interfaces (repositories) using architectural patterns like Hexagonal Architecture or Clean Architecture.
  4. Implement database persistence as an external adapter at the boundary.
• Typical Use Cases: Banking engines, financial settlement systems, dynamic insurance quote generators, and multi-step booking platforms (e.g., airline ticket reservations) where invariant violation leads to financial or legal discrepancies.

3. Comparative Matrix: Choosing the Right Architectural Paradigm

Choosing between Data-Driven and Behavior-Driven design depends on the structural complexity of business rules versus the dominance of data display.

Application Context: The Classifieds Platform

The platform under design is a Classifieds Marketplace. The core user flows focus on:

• Users posting listings.
• Users browsing, filtering, and viewing lists of products across categories.
• Managing item states and associated images.

Because the system is primarily driven by content discovery, listing displays, and data state manipulation rather than complex banking-grade state machines, Data-First Design provides an efficient, clean, and maintainable foundation.

4. Extracting Core Entities from Requirements

To initiate the Data-First approach, the system's functional requirements are analyzed to extract domain entities:

These four entities form the structural foundation of the platform:

1. User: Identifies platform participants, authentication attributes, and ownership boundaries.
2. Listing: The central transaction unit representing items for sale.
3. Category: A classification dimension for categorization, indexing, and filtering.
4. Image: Asset metadata tracking binary media stored in object storage.

5. Designing the User Entity (users)

The users table handles identity and authentication.

Table Definition

• id: Unique surrogate key serving as the primary identifier across foreign key references.
• name: Consolidated full name of the user (simplifies identity requirements over split first/last names).
• email: Unique identity field for authentication and notification routing.
• password: Secure cryptographic hash string (e.g., Argon2id or bcrypt). Plain text passwords must never be stored.
• created_at / updated_at: Universal audit timestamps tracking row lifecycle events.

CREATE TABLE users (
    id BIGSERIAL PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    email VARCHAR(255) NOT NULL UNIQUE,
    password VARCHAR(255) NOT NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

Note: Audit columns (created_at and updated_at) are standard across production relational tables. They allow systems to handle cache invalidation, asynchronous reconciliation, and operational debugging.

6. Designing the Listing Entity (listings)

The listings table represents the primary entity users search for, browse, and manage.

Table Definition

• id: Unique identifier for the listing.
• title: Short, human-readable summary of the product (e.g., "MacBook Pro M2 16-inch").
• description: Detailed text explaining condition, terms, and specifications.
• price: Numeric monetary value. In production, this is represented using scaled integers (cents) or arbitrary-precision decimals (NUMERIC(12, 2)) to eliminate floating-point rounding errors.
• city: Geographic location tag for regional filtering.
• status: Lifecycle indicator of the listing (e.g., draft, processing, active, sold, archived). Crucial for managing asynchronous operations such as image moderation and malware scanning.
• user_id: Foreign key establishing ownership with the users table.
• category_id: Foreign key associating the item with a normalized classification.
• created_at / updated_at: Standard audit timestamps.

CREATE TABLE listings (
    id BIGSERIAL PRIMARY KEY,
    user_id BIGINT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    category_id BIGINT NOT NULL REFERENCES categories(id) ON DELETE RESTRICT,
    title VARCHAR(255) NOT NULL,
    description TEXT NOT NULL,
    price NUMERIC(12, 2) NOT NULL,
    city VARCHAR(100) NOT NULL,
    status VARCHAR(50) NOT NULL DEFAULT 'processing',
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

7. Normalization Trade-Offs: The Category Entity (categories)

When associating categories with listings, engineers typically face two implementation choices:

Option A: The Denormalized Approach (Inline String)

Embed a text string directly into the listings table (e.g., category = 'Computers').

The Update Anomaly Problem: If the category name changes from "Electronics" to "Computers", the database must execute a table-wide scan, locking and modifying millions of rows:

-- High-cost write operation: risks lock contention and index thrashing
UPDATE listings SET category = 'Computers' WHERE category = 'Electronics';

Option B: The Normalized Relational Approach (Separate Table)

Isolate the category into its own table and reference its surrogate primary key from the listings table.

Renaming the category requires updating only a single row in the categories table:

-- Constant-time single row write: O(1) row modification
UPDATE categories SET name = 'Computers' WHERE id = 10;

CREATE TABLE categories (
    id BIGSERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL UNIQUE,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

8. Relational Storage vs. Document Inlining: The Image Entity (images)

Listings support multiple product images (e.g., 5 to 12 images per listing). Deciding how to store this media metadata highlights key differences between relational and document models.

Approach 1: Inlined Array/JSON Document Strategy

Store an array of image keys or serialized JSON directly within a column of the listings table:

-- Embedded PostgreSQL JSONB / Array Column
ALTER TABLE listings ADD COLUMN image_keys TEXT[];

• Pros: Zero joins required to retrieve images when reading a listing.
• Cons: Difficult to query, validate, or enrich individual images with rich metadata (such as MIME types, dimensions, storage bucket origins, or processing state flags) without rewriting the entire array.

Approach 2: Normalized Relational Entity (images Table)

Define an independent table where every image occupies its own row, linked via a foreign key to the parent listing.

CREATE TABLE images (
    id BIGSERIAL PRIMARY KEY,
    listing_id BIGINT NOT NULL REFERENCES listings(id) ON DELETE CASCADE,
    object_key VARCHAR(512) NOT NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

Architectural Rationale for Normalizing Images

1. Extensible Media Metadata: Real-world images require more than just a path. They contain metadata including byte size, checksums (SHA-256), processing statuses (pending, processed, failed), and MIME variants (image/webp, image/jpeg).
2. Object Storage Decoupling: Application servers store actual image binaries inside cloud object storage (e.g., AWS S3, MinIO). The database retains only the object_key, allowing the application to construct signed or CDN URLs dynamically without touching the database schema.
3. Database Consistency: Maintains 1st Normal Form (1NF) across relational engines, enabling declarative foreign key constraints, cascading deletions (ON DELETE CASCADE), and independent indexing.

9. Entity-Relationship Topology and Cardinality Mapping

With all four entities established, the schema defines three distinct relationships, each with specific structural cardinality.

Cardinality Breakdown

1. users to listings ($1:N$):

   • One user can create zero or many ($N$) listings.
   • Each listing belongs strictly to one ($1$) user.
   • Enforced by: listings.user_id REFERENCES users(id).

2. categories to listings ($1:N$):

   • One category can classify zero or many ($N$) listings.
   • In this platform design, each listing is mapped to one primary category to simplify filtering and navigation.
   • Enforced by: listings.category_id REFERENCES categories(id).

3. listings to images ($1:N$):

   • One listing can contain multiple ($N$) images (bounded by application business rules, e.g., max 10 images).
   • Each image record belongs to one ($1$) listing.
   • Enforced by: images.listing_id REFERENCES listings(id).

10. Production Schema Implementation (PostgreSQL DDL & Go Domain Models)

Complete DDL Migration Script

The complete Data Definition Language (DDL) creates all tables, constraints, and relational indexes:

-- 1. Identity & Access
CREATE TABLE users (
    id BIGSERIAL PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    email VARCHAR(255) NOT NULL UNIQUE,
    password VARCHAR(255) NOT NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- 2. Taxonomic Classifications
CREATE TABLE categories (
    id BIGSERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL UNIQUE,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- 3. Core Domain Listings
CREATE TABLE listings (
    id BIGSERIAL PRIMARY KEY,
    user_id BIGINT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    category_id BIGINT NOT NULL REFERENCES categories(id) ON DELETE RESTRICT,
    title VARCHAR(255) NOT NULL,
    description TEXT NOT NULL,
    price NUMERIC(12, 2) NOT NULL,
    city VARCHAR(100) NOT NULL,
    status VARCHAR(50) NOT NULL DEFAULT 'processing',
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- Indexes for frequent query access paths
CREATE INDEX idx_listings_user_id ON listings(user_id);
CREATE INDEX idx_listings_category_id ON listings(category_id);
CREATE INDEX idx_listings_city ON listings(city);

-- 4. Media Asset Metadata
CREATE TABLE images (
    id BIGSERIAL PRIMARY KEY,
    listing_id BIGINT NOT NULL REFERENCES listings(id) ON DELETE CASCADE,
    object_key VARCHAR(512) NOT NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_images_listing_id ON images(listing_id);

Go Domain Model Representation

In a Go monolith, these tables are mapped to strongly-typed domain structs:

package domain

import (
	"time"
)

// User represents an authenticated platform actor.
type User struct {
	ID        int64     `json:"id"`
	Name      string    `json:"name"`
	Email     string    `json:"email"`
	Password  string    `json:"-"` // Omit password hash from JSON serialization
	CreatedAt time.Time `json:"created_at"`
	UpdatedAt time.Time `json:"updated_at"`
}

// Category represents a listing taxonomy classification.
type Category struct {
	ID        int64     `json:"id"`
	Name      string    `json:"name"`
	CreatedAt time.Time `json:"created_at"`
	UpdatedAt time.Time `json:"updated_at"`
}

// Listing represents a classifieds advertisement.
type Listing struct {
	ID          int64     `json:"id"`
	UserID      int64     `json:"user_id"`
	CategoryID  int64     `json:"category_id"`
	Title       string    `json:"title"`
	Description string    `json:"description"`
	Price       float64   `json:"price"`
	City        string    `json:"city"`
	Status      string    `json:"status"`
	Images      []Image   `json:"images,omitempty"`
	CreatedAt   time.Time `json:"created_at"`
	UpdatedAt   time.Time `json:"updated_at"`
}

// Image represents media storage metadata associated with a listing.
type Image struct {
	ID        int64     `json:"id"`
	ListingID int64     `json:"listing_id"`
	ObjectKey string    `json:"object_key"`
	CreatedAt time.Time `json:"created_at"`
	UpdatedAt time.Time `json:"updated_at"`
}

11. Schema Evolution and Migration Strategies in Monoliths

A database schema is not static; it evolves alongside business requirements. Planning for schema evolution is a prerequisite for production-grade engineering.

Future Architectural Additions

1. Asynchronous Background Processing (jobs Table):
   • When a user uploads four high-resolution photos, the web request should not block while generating thumbnails, optimizing WebP compression, or running moderation checks.
   • An asynchronous queue or a database-backed job table (e.g., image_processing_jobs) will be required to decouple HTTP requests from background processing workers.
2. Schema Migrations in Production:
   • Production systems must avoid disruptive table locks. Alterations such as adding non-nullable columns or building large indexes require migration strategies like versioned migration tools (e.g., golang-migrate), non-blocking index creation (CREATE INDEX CONCURRENTLY), and phased code deployments.

12. Summary and Key Takeaways

• Architectural Paradigms: Software design broadly follows either a Data-First (data-driven) or Behavior-First (domain-driven) approach. Data-First is ideal for list-intensive, CRUD, and classified systems, whereas Behavior-First is suited for rule-dense, high-invariance domains like core banking.
• Entity Identification: The classifieds monolith maps cleanly to four primary entities extracted directly from functional requirements: User, Listing, Category, and Image.
• Normalization vs. Duplication: Isolating categories into a dedicated table prevents update anomalies, replacing costly table-wide string updates with $O(1)$ single-row modifications.
• Media Architecture: Instead of storing image paths in an unvalidated array or storing large binaries inside the database, images are modeled as an independent relational table storing object_key references that point to external object storage.
• Relational Integrity: Foreign key constraints maintain relational invariants across the system using $1:N$ relationships:
  • $\text{User} \xrightarrow{1:N} \text{Listing}$
  • $\text{Category} \xrightarrow{1:N} \text{Listing}$
  • $\text{Listing} \xrightarrow{1:N} \text{Image}$
• Iterative Evolution: Initial schema models focus on primary entities and attributes; operational concerns like asynchronous job tracking, phone numbers, and moderation flags are layered on incrementally via automated database migrations.