Data Modeling and Schema Design

Data modeling is deciding how your information is structured before you build on top of it — what tables exist, how they relate, and what rules they enforce. It's one of the highest-leverage decisions you'll make, because a good model makes features easy to add and a bad one makes everything a fight. If Cartara flagged this in your diff, you likely changed your database schema in a way that could create data integrity issues, make queries harder, or become painful to migrate later.

Why the model comes first

Your data model outlives almost everything else. UI gets redesigned, frameworks get swapped, but the shape of your data tends to stick around — and changing it later means running migrations on live data that real users depend on. A few hours thinking about the model upfront saves weeks of pain later.

The goal isn't a "perfect" model. It's one that honestly represents your domain and supports what your app actually needs to do.

Entities and relationships

Start by identifying your entities — the nouns in your domain (users, orders, products, comments). Each typically becomes a table. Then figure out how they relate.

One-to-many is the most common. One user has many orders. One post has many comments. Implemented by putting a foreign key on the "many" side — each order row stores the user_id it belongs to.

Many-to-many needs a join table. A student takes many courses; a course has many students. You need a separate table whose rows pair one student_id with one course_id:

students        enrollments (join table)        courses
--------        --------------------            -------
id | name       student_id | course_id          id | title
1  | Matt       1          | 101                101 | Biology
2  | Sara       1          | 102                102 | History
                2          | 101

One-to-one means one record links to exactly one other. Often these can be columns on the same table — split them out only when there's a good reason, like keeping rarely-accessed or sensitive data separate.

Primary keys: UUIDs vs integers

Every table needs a primary key — the unique identifier for each row. Two common choices:

• Auto-incrementing integers (1, 2, 3...) — compact, human-readable, but they reveal how many records you have and are guessable in URLs
• UUIDs (a3f8b2...) — globally unique, safe to expose in URLs, can be generated by the client before insert

UUIDs are the safer default for anything user-facing. Integers are fine for internal reference tables or places where you want simple, sequential IDs.

Normalization vs denormalization

This is the central tension in schema design.

Normalization means storing each fact exactly once. A user's name lives in the users table; orders reference it by user_id. Benefits: no contradictory data, update in one place. Cost: you need joins to reassemble data.

Denormalization means deliberately duplicating data to avoid joins and speed up reads. You might copy user_name directly onto each order so displaying a list doesn't require a join. Benefits: faster reads. Cost: if the user changes their name, you now have stale copies to update, and they risk getting out of sync.

Rule of thumb: normalize by default. It keeps data correct and works fine for the vast majority of apps. Denormalize deliberately and only when you've measured a real performance problem — it's an optimization, not a starting point.

What You'll See in Your Code

Cartara frequently flags schemas that are missing foreign key constraints or timestamps:

-- Missing constraints: nothing enforces the relationship
CREATE TABLE orders (
  id UUID PRIMARY KEY,
  user_id UUID,              -- no foreign key constraint
  status TEXT,               -- no check constraint on valid values
  total DECIMAL
  -- no created_at, updated_at
);

-- Better: constraints enforce data integrity
CREATE TABLE orders (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  status TEXT NOT NULL CHECK (status IN ('pending', 'shipped', 'delivered', 'cancelled')),
  total DECIMAL(10,2) NOT NULL,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

A common many-to-many pattern Cartara may flag when it sees repeated data instead of a join table:

-- Problematic: storing related IDs as a text array
CREATE TABLE posts (
  id UUID PRIMARY KEY,
  tag_ids TEXT  -- comma-separated tag IDs, hard to query
);

-- Better: join table
CREATE TABLE post_tags (
  post_id UUID NOT NULL REFERENCES posts(id) ON DELETE CASCADE,
  tag_id UUID NOT NULL REFERENCES tags(id) ON DELETE CASCADE,
  PRIMARY KEY (post_id, tag_id)
);

Common patterns worth knowing

• Timestamps everywhere — add created_at and updated_at to almost every table. You'll want them eventually, and adding them later requires a migration
• Soft deletes — instead of deleting a row, set a deleted_at timestamp. Lets you recover data and keep audit history. Remember to filter these out in queries
• Enums for fixed value sets — for fields like order status (pending/shipped/delivered), use a database enum or check constraint rather than free-text strings
• Avoid derived data — don't store a total you can compute from line items unless performance forces it

Things to avoid

• The "god table" — one giant table with 60 columns trying to represent everything. Split distinct entities apart
• Structured data as a string — cramming JSON or comma-separated values into a single column when it should be real columns or rows. Native JSON columns are fine when data is genuinely unstructured
• No constraints — letting the database accept nulls, duplicates, and orphaned references everywhere. Constraints (NOT NULL, UNIQUE, foreign keys) are free correctness guarantees

Related Concepts

• Database Fundamentals
• Database Scaling and Performance
• System Design Basics