Should product managers participate in data modeling?

Yes, but at the conceptual and logical levels, not the physical level. PMs should define what entities exist, what attributes matter, and what business rules apply. Engineers handle the physical implementation (index types, storage engines, partitioning). The most productive data modeling sessions have both PM and engineering in the room, because PMs know the business rules and engineers know the technical constraints.

How do I decide between storing data as a separate entity or as a JSON field?

If you need to query, filter, or sort by values within the data, make it a separate entity with proper columns. If the data is opaque to the application (metadata, preferences, configuration blobs), JSON is appropriate. If you are unsure, start with a JSON field and promote it to a proper entity when query patterns emerge. The [schema design template](/templates/schema-design-template) covers this tradeoff in more detail.

When should I use soft deletes vs. hard deletes?

Use soft deletes when users might want to restore deleted items, when you need audit trails, or when downstream systems depend on the record existing. Use hard deletes for junction tables, session data, and truly ephemeral records. Soft deletes add complexity: every query needs a `WHERE deleted_at IS NULL` filter. Partial indexes help with performance.

How do I model features that have customizable fields?

Three common approaches: (1) Entity-Attribute-Value tables (flexible but slow to query), (2) JSON columns (good for PostgreSQL with JSONB, moderate query support), (3) dedicated columns with a schema migration for each new field (rigid but fastest to query). For user-facing customization (custom fields on a CRM), JSON or EAV works. For internal product fields, dedicated columns are almost always better.

How far ahead should I plan the data model for future features?

Plan one iteration ahead, not three. Over-engineering a data model for hypothetical future features creates unnecessary complexity. If you know a feature is coming in the next quarter, model for it now. If it might come in a year, note it in the "Evolution and Migration" section and revisit when it is confirmed. Adding a column later is cheaper than maintaining unused abstraction today. ---

Data Model Template for Engineering Teams

What This Template Is For

Every product feature is built on a data model, whether the team designs it intentionally or stumbles into one. Teams that skip data modeling end up with columns that mean different things in different contexts, relationships that require three-way joins to traverse, and schemas that need painful migrations every time a new feature ships. Intentional data modeling prevents these problems by forcing teams to agree on what entities exist, how they relate, and what rules govern them before writing code.

This template helps product managers and engineers collaboratively design data models for new features or refactor existing ones. It covers entity definitions, attribute specifications, relationship mapping, constraints, and indexing considerations. It is not a DBA-level database schema spec. It is the shared artifact that ensures product and engineering agree on the data structure before implementation begins.

The schema design template covers the physical database implementation of data models. For teams building data infrastructure, the data warehouse template addresses analytical data modeling separately from operational models. The Technical PM Handbook covers how product managers should engage with technical architecture decisions. Understanding feature adoption helps connect data model design to the metrics you will track.

When to Use This Template

Use this template when designing the data structure for a new feature that introduces new entities or relationships, when refactoring a data model that has become a bottleneck for development speed, when two teams need to share data and must agree on a common model, or when preparing a technical spec that requires product and engineering alignment on what data to store and how to structure it.

How to Use This Template

Start with the business concepts. List the real-world things your feature deals with (users, orders, subscriptions, projects, tasks). These become your entities.

Define attributes for each entity. For every entity, list what you need to know about it. Include data types and whether each attribute is required or optional.

Map relationships. Draw connections between entities. Specify cardinality (one-to-one, one-to-many, many-to-many) and whether relationships are required or optional.

Add constraints and business rules. Document what makes a record valid, what combinations are not allowed, and what uniqueness rules apply.

Consider access patterns. Talk to engineering about how the data will be queried. The most common queries should be efficient, which may influence indexing and denormalization decisions.

Review with the full team. Data models affect everyone: product, engineering, design, analytics. Get input before committing to a schema.

The Template

Model Overview

☐ Feature or system name
☐ Business context (what product capability does this model support)
☐ Model scope (operational, analytical, or both)
☐ Target database or storage system
☐ Entity-Relationship diagram (visual representation)
☐ Estimated data volume (records per entity, growth rate)
☐ Key access patterns (most common queries this model must support)

Entity Definitions

For each entity:

☐ Entity name (singular, PascalCase by convention)
☐ Business description (what real-world thing does this represent)
☐ Primary key definition (UUID, auto-increment, composite)
☐ Soft delete or hard delete strategy
☐ Audit fields (created_at, updated_at, created_by, updated_by)
☐ Versioning strategy (if entity history needs to be preserved)
☐ Estimated record count at launch and at 12 months

Attribute Specifications

For each attribute within an entity:

☐ Attribute name (snake_case by convention)
☐ Data type (string, integer, float, boolean, timestamp, enum, JSON, array)
☐ Required or optional (NOT NULL or nullable)
☐ Default value (if any)
☐ Max length or value range constraints
☐ Business description (what this field means in product terms)
☐ Example values (2-3 representative samples)
☐ Sensitivity classification (PII, confidential, internal, public)
☐ Indexed (yes/no, with justification)

Relationship Mapping

For each relationship between entities:

☐ Parent entity and child entity
☐ Relationship type (one-to-one, one-to-many, many-to-many)
☐ Required or optional on each side
☐ Foreign key column and reference
☐ Cascade behavior on delete (cascade, set null, restrict, no action)
☐ Cascade behavior on update
☐ Junction table definition (for many-to-many relationships)
☐ Business rule description (e.g. "A project must have exactly one owner")

Constraints and Business Rules

☐ Unique constraints (which field combinations must be unique)
☐ Check constraints (value validation rules enforced at the database level)
☐ Composite key constraints
☐ Mutual exclusivity rules (if field A is set, field B must be null)
☐ Temporal constraints (effective dates, expiry logic)
☐ Cross-entity validation rules (e.g. "order total must equal sum of line items")
☐ Status transition rules (allowed state changes, e.g. draft to published to archived)

Access Patterns and Indexing

☐ Top 5 most frequent queries described in plain language
☐ Recommended indexes for each frequent query
☐ Full-text search requirements (which fields, which engine)
☐ Sorting requirements (default sort orders for list views)
☐ Filtering requirements (which fields appear in filter UIs)
☐ Pagination strategy (offset, cursor, keyset)
☐ Aggregation requirements (counts, sums, averages for dashboards)

Evolution and Migration

☐ Known future extensions (fields or entities likely to be added)
☐ Flexibility provisions (JSON columns for unstructured extensions)
☐ Breaking change risks (what model changes would require data migration)
☐ Backward compatibility requirements (API contracts that depend on this model)

Filled Example

Model Overview

Feature: Project Management Module

Business Context: Users create projects, add tasks with assignees and due dates, and track progress through customizable status columns.

Model Scope: Operational (serves the application API directly)

Target Database: PostgreSQL 15

Estimated Volume: 50K projects, 500K tasks at launch. Growing 20% monthly.

Key Access Patterns: List tasks by project (filtered by status, sorted by due date). Get project with task counts by status. Search tasks by title across all user projects.

Entity Definitions

Project

Represents a container for related tasks owned by one user with optional team members.
Primary key: id (UUID v4, generated by application)
Soft delete: yes (deleted_at timestamp, null means active)
Audit fields: created_at, updated_at, created_by

Task

Represents a unit of work within a project, assigned to one user.
Primary key: id (UUID v4)
Soft delete: yes
Audit fields: created_at, updated_at

ProjectMember (junction table)

Represents a user's membership in a project with a specific role.
Primary key: composite (project_id, user_id)
Hard delete: yes (removing membership deletes the row)

Attribute Specifications (Task entity)

Attribute	Type	Required	Default	Description	Indexed
id	UUID	Yes	Generated	Unique task identifier	PK
project_id	UUID	Yes	None	Parent project reference	Yes (FK)
title	VARCHAR(255)	Yes	None	Short task description	Full-text
description	TEXT	No	NULL	Detailed task description	No
status	ENUM	Yes	'todo'	Current status (todo, in_progress, review, done)	Yes
priority	ENUM	No	'medium'	Task priority (low, medium, high, urgent)	Yes
assignee_id	UUID	No	NULL	User assigned to this task	Yes (FK)
due_date	DATE	No	NULL	Target completion date	Yes
position	INTEGER	Yes	0	Sort order within status column	No
created_at	TIMESTAMPTZ	Yes	NOW()	Record creation timestamp	No
updated_at	TIMESTAMPTZ	Yes	NOW()	Last modification timestamp	No
deleted_at	TIMESTAMPTZ	No	NULL	Soft delete timestamp	Yes

Relationship Mapping

Relationship	Type	Required	On Delete	Business Rule
Project has Tasks	One-to-many	Project: optional, Task: required	Cascade (delete project deletes all tasks)	A task always belongs to exactly one project
User owns Project	One-to-many	Project: required, User: optional	Restrict (cannot delete user with projects)	Every project has one owner
User assigned Task	One-to-many	Task: optional, User: optional	Set null (if user deleted, unassign tasks)	A task can have zero or one assignee
Project has Members	Many-to-many (via ProjectMember)	Both optional	Cascade on project side, restrict on user side	A user can be a member of multiple projects

Access Patterns and Indexing

List tasks for a project filtered by status: Index on (project_id, status, deleted_at)
Get project with task count per status: Index on (project_id, status) with partial index excluding deleted
My assigned tasks across projects sorted by due date: Index on (assignee_id, due_date, deleted_at)
Search tasks by title: GIN index on title using pg_trgm extension
Recently updated tasks: Index on (updated_at DESC) with partial index excluding deleted

Key Takeaways

Start data modeling from business concepts, not database tables. Entities should map to things your users think about
Specify every attribute with its type, constraints, and business meaning. Ambiguity here causes bugs later
Define cascade behaviors on delete explicitly. The wrong cascade setting can wipe a user's data
Design indexes based on your top 5 queries, not on intuition. Unused indexes slow writes for no benefit
Plan for model evolution. Leave room for known future extensions without over-engineering for hypothetical ones

About This Template

Created by: Tim Adair

Last Updated: 3/5/2026

Version: 1.0.0

License: Free for personal and commercial use

Data Model Template for Engineering Teams