Relational Data Modeling

In How Data Flows, the database is persistent storage. To build rigorous business apps, you need clear relational data modeling.

Momen uses enterprise PostgreSQL. This page builds modeling intuition from scratch.

Entities and relations

Decompose business scenarios into entities and relations:

The essence: many cohesive, simple tables + primary/foreign key (ID) links — not one giant duplicated table.

Table relations

One-to-one

One row in A maps to exactly one row in B, and vice versa.

• Account & wallet: one account, one wallet
• Account & profile: split rare/sensitive fields to account_profile for performance

Implementation: Place a foreign key on the child table and add a unique constraint on that FK column (so each parent row maps to at most one child row).

One-to-many

One row in A maps to many rows in B; each B row maps to one A row.

• Account & posts: one author, many posts
• Shop & products: one shop, many SKUs

Implementation: FK column on the many side (e.g. account_id on post).

Many-to-many

Many rows on both sides — e.g. posts & tags, students & courses.

Implementation: a junction table (e.g. post_tag):

1. Create post_tag
2. post → one-to-many → post_tag
3. tag → one-to-many → post_tag
4. Each junction row = one binding (post ID + tag ID)

Field types and standards

• Primary key (id): system-maintained unique row identity
• Timestamps: created_at, updated_at (system-managed, not editable)
• Decimals: Decimal type for money and precision-critical numbers

Field type reference: Data Types.

Updates and storage (MVCC)

Momen’s database uses Multi-Version Concurrency Control (MVCC):

• UPDATE / DELETE do not overwrite rows in place
• Old versions are marked invisible; new versions are written elsewhere
• Bulk updates can temporarily increase storage until VACUUM reclaims space during off-peak hours — automatic, no manual step

Related Reading

• Set Up the Database — create tables and relations in the editor