Skip to content

3. Database Design

Jump to bottom
Maruf Bepary edited this page May 9, 2026 · 2 revisions

Overview

The Next Discussion Platform persists all application data in Cloud Firestore, a horizontally scalable, document-oriented NoSQL database provided by Firebase. Firestore organises data in a hierarchy of collections → documents → subcollections rather than in relational tables.

Key architectural choices that shape the schema:

  • No server-side joins. Every query is scoped to a single collection or collection group. Data that is frequently co-read is denormalised onto the same document.
  • Per-document read billing. Minimising the number of documents fetched per screen render is a primary design constraint.
  • Atomic multi-document operations. Firestore transactions and batched writes ensure consistency across related documents without requiring a relational engine.
  • Document size limit. Each Firestore document is capped at 1 MiB. Unbounded lists (e.g. comments, votes) are stored as separate top-level collection documents rather than as embedded arrays.

The schema comprises four top-level collections (communities, posts, comments, users) and three subcollections nested under users/{uid} (communitySnippets, postVotes, savedPosts).

Collections

communities

Each document represents one community (analogous to a subreddit). The document ID is the community's unique name, chosen at creation time.

Fields

Field Firestore Type Required Notes
id string Yes Required field in the application type; populated from the Firestore document ID on read, not stored in the document body
creatorId string Yes Foreign key → users/{uid}
numberOfMembers number Yes Maintained atomically via batch writes; incremented/decremented on join/leave
privacyType string Yes Enumerated value: "public", "restricted", or "private"
createdAt timestamp No Server timestamp written at document creation
imageURL string No Resolved download URL of the community image in Firebase Storage
adminIds array<string> No Array of user uid values; source of truth for admin membership

Constraints & Validation

  • Community name (used as the document ID): 3–21 characters, alphanumeric characters only — validated via Zod at creation.
  • privacyType must be one of public, restricted, or private — validated via Zod enum at creation.
  • numberOfMembers must remain ≥ 0; enforced via application logic during batch writes.

posts

Each document represents a single post submitted to a community.

Fields

Field Firestore Type Required Notes
id string No Optional TypeScript field; absent before document creation, populated from Firestore document ID after addDoc returns
communityId string Yes Foreign key → communities/{communityId}
creatorId string Yes Foreign key → users/{uid}
creatorUsername string Yes Denormalised from Firebase Auth displayName at post creation
title string Yes Maximum 300 characters (Zod); copied into Comment.postTitle and SavedPost.postTitle
body string Yes Post body text; stored as an empty string when the user submits without body content
numberOfComments number Yes Maintained atomically via batch writes; incremented on comment creation, decremented on deletion
voteStatus number Yes Net vote score; updated atomically using FieldValue.increment() on each vote action
imageURL string No Resolved download URL of the post image in Firebase Storage
communityImageURL string No Denormalised from Community.imageURL at post creation
createTime timestamp Yes Server timestamp written at document creation

Constraints & Validation

  • title is required and may not exceed 300 characters — enforced via Zod at form submission.
  • A composite Firestore index on (communityId, voteStatus DESC) is required to support the popular home feed query.

comments

Each document represents a single comment or threaded reply on a post. Comments are stored as a flat top-level collection rather than embedded arrays to avoid the 1 MiB document limit and to allow independent querying.

Fields

Field Firestore Type Required Notes
id string Yes Document ID; also stored as a field for self-referencing
creatorId string Yes Foreign key → users/{uid}
creatorDisplayText string Yes Denormalised from the prefix of the creator's Firebase Auth email address
communityId string Yes Foreign key → communities/{communityId}
postId string Yes Foreign key → posts/{postId}
postTitle string Yes Denormalised from Post.title at comment creation
text string Yes Minimum 1 character — enforced via Zod
createdAt timestamp Yes Server timestamp written at document creation
parentId string No Self-referential foreign key → comments/{commentId}; absent or undefined indicates a top-level comment
depth number Yes Threading depth: 0 = top-level comment, 1 = first-level reply, 2 = second-level reply (maximum)

Constraints & Validation

  • text must be at least 1 character — validated via Zod.
  • Maximum comment depth is 2 — enforced in application logic (not a Firestore security rule).
  • depth is always set by the application when writing; it is not derived server-side.

users

Each document corresponds to a registered user. The document shape is sparse; most identity data is held in Firebase Authentication and propagated to other collections at write time.

Fields

Field Firestore Type Required Notes
email string Yes The user's email address; used as the source for Comment.creatorDisplayText (email prefix)
displayName string No Display name from Firebase Auth profile; source for Post.creatorUsername; may be null

Note: The users collection document is sparse by design. Identity fields such as uid, photoURL, and authentication provider data are managed by Firebase Authentication rather than stored in Firestore directly.

Constraints & Validation

  • email format is validated by Firebase Authentication prior to document creation.
  • displayName updates must be propagated to all existing posts authored by the user via a batch write on creatorId.

Application-Level Read Projections

These types are not stored as independent Firestore collections; they are shaped projections of the users/{uid} document used in specific application contexts:

AdminUser

Used when searching for or listing community admins. Reads email and displayName from users/{uid}.

Field Firestore Type Required Notes
uid string Yes Firestore document ID of the user
email string Yes User's email address
displayName string No Optional; may be undefined if not set

CommunityMember

Used when listing all members of a community. Reads email and displayName from users/{uid}.

Field Firestore Type Required Notes
uid string Yes Firestore document ID of the user
email string Yes User's email address
displayName string | null Yes Required but explicitly nullable; null when the user has not set a display name

Note: The distinction between AdminUser.displayName (string? — optional, may be undefined) and CommunityMember.displayName (string | null — required but nullable) reflects different read contexts. AdminUser is used for admin candidate search where the field is not critical; CommunityMember is used to render a member list where the field is always expected to be present or explicitly absent.

Subcollections

users/{uid}/communitySnippets

A subcollection under each user document. Each document represents one community that the user has joined. The document ID matches the communityId.

Fields

Field Firestore Type Required Notes
communityId string Yes Foreign key → communities/{communityId}; also serves as the document ID
isAdmin boolean No Denormalised from whether uid appears in Community.adminIds; defaults to false
imageURL string No Denormalised from Community.imageURL; updated when the community image changes

users/{uid}/postVotes

A subcollection under each user document. Each document records the user's vote on a single post.

Fields

Field Firestore Type Required Notes
id string Yes Document ID
postId string Yes Foreign key → posts/{postId}
communityId string Yes Foreign key → communities/{communityId}
voteValue number Yes 1 for an upvote; -1 for a downvote

Invariant: There is at most one postVotes document per user per post. Re-voting upserts the existing document rather than creating a duplicate.

users/{uid}/savedPosts

A subcollection under each user document. Each document records a post that the user has bookmarked. The document ID matches the postId.

Fields

Field Firestore Type Required Notes
id string Yes Document ID; same value as postId
postId string Yes Foreign key → posts/{postId}; also serves as the document ID
communityId string Yes Foreign key → communities/{communityId}
postTitle string Yes Denormalised from Post.title at save time
communityImageURL string No Denormalised from Community.imageURL at save time

Relationships

Collection Hierarchy

Firestore root
├── communities/{communityId}
├── posts/{postId}
├── comments/{commentId}
│     └── (self-referential: parentId → comments/{commentId})
└── users/{uid}
      ├── communitySnippets/{communityId}
      ├── postVotes/{voteId}
      └── savedPosts/{postId}

Cross-Collection References

The following table enumerates all foreign-key style references between entities. Firestore does not enforce referential integrity natively; consistency is maintained by the application layer.

Entity A Relationship Entity B Via Field
communities created by one users communities.creatorId → users/{uid}
posts belongs to one communities posts.communityId → communities/{communityId}
posts created by one users posts.creatorId → users/{uid}
comments belongs to one posts comments.postId → posts/{postId}
comments scoped to one communities comments.communityId → communities/{communityId}
comments created by one users comments.creatorId → users/{uid}
communitySnippets references one communities communitySnippets.communityId → communities/{communityId}
postVotes references one posts postVotes.postId → posts/{postId}
postVotes scoped to one communities postVotes.communityId → communities/{communityId}
savedPosts references one posts savedPosts.postId → posts/{postId}
savedPosts scoped to one communities savedPosts.communityId → communities/{communityId}

Self-Referential Relationship (Comment Threading)

The comments collection implements a flat-list-with-parent-ID threading model. Each comment document carries a parentId field that optionally references another document within the same comments collection:

  • When parentId is absent, the comment is a top-level comment (depth = 0) attached directly to a post.
  • When parentId is present, the comment is a reply (depth = 1 or depth = 2) to the referenced comment.
  • A maximum nesting depth of 2 is enforced by application logic (not a Firestore rule), giving three tiers: post → comment → reply.

This flat structure avoids recursive subcollection nesting, keeps all comments queryable in a single postId == filter, and prevents unbounded document growth.

Denormalisation Strategy

Rationale

Firestore charges per document read and does not support server-side joins. Co-locating display data (e.g. a community's image, the post author's username) on the document that renders it eliminates multi-document fetch chains for common read paths. The trade-off is that the application must propagate changes to denormalised copies whenever the source value changes.

Denormalised Fields Inventory

Denormalised Field Carrier Collection Source Collection Source Field Sync Trigger Risk
creatorUsername posts Firebase Auth displayName Post creation; batch write on display name change Stale display name if batch write fails
creatorDisplayText comments Firebase Auth email (prefix) Comment creation; batch write on email change Stale display text if batch write fails
communityImageURL posts communities imageURL Post creation; batch write on community image update Stale image on posts if propagation is incomplete
communityImageURL savedPosts communities imageURL Save action; batch write on community image update Stale image on saved posts if propagation is incomplete
imageURL communitySnippets communities imageURL Join community; batch write on community image update Stale snippet image if propagation is incomplete
postTitle comments posts title Comment creation No sync logic; relies on post title immutability
postTitle savedPosts posts title Save action No sync logic; relies on post title immutability
isAdmin communitySnippets communities adminIds (membership) addCommunityAdmin transaction Inconsistency if admin array is modified outside the transaction

Sync Mechanism

  • Community image updates: A collectionGroup("communitySnippets") query retrieves all snippet documents across all users and issues a batch write to update imageURL. Individual posts and savedPosts documents are updated by querying on communityId == and batch-writing communityImageURL.
  • User display name updates: The posts and comments collections are queried by creatorId == and creatorUsername / creatorDisplayText are batch-written across all matching documents.
  • Admin promotion: A Firestore transaction reads the communities document, appends the target uid to adminIds, and writes isAdmin: true to the corresponding communitySnippets document — all atomically.
  • Post title: Treated as effectively immutable after creation. No sync logic is implemented; Comment.postTitle and SavedPost.postTitle reflect the title at the time of their respective write operations.

Query Patterns

communities Queries

Query Filter Order Limit Purpose
Recommendation list — numberOfMembers DESC Configurable Display top communities in the sidebar
Public search privacyType == "public" — — Restrict search results to publicly visible communities

posts Queries

Query Filter Order Limit Purpose
Community feed communityId == createTime DESC Paginated Display all posts for a single community
Home feed (new) communityId IN [...] createTime DESC Paginated Display recent posts from the user's joined communities
Home feed (popular) communityId IN [...] voteStatus DESC Paginated Display top-voted posts from the user's joined communities; requires composite index
Display name sync creatorId == — — Retrieve all posts for a given user to batch-update creatorUsername

Composite index required: (communityId, voteStatus DESC) for the popular home feed query.

comments Queries

Query Filter Order Limit Purpose
Post comments postId == createdAt ASC — Load all comments for a single post
Cascade delete postId IN [...] — Chunked Retrieve all comments associated with posts to be deleted
Display text sync creatorId == — — Retrieve all comments for a given user to batch-update creatorDisplayText

users Queries

Query Filter Order Limit Purpose
Exact lookup email == — 1 Locate a specific user for admin promotion
Prefix search email >= term, email <= term + "\uf8ff" — — Search for users by email prefix in admin tooling

Subcollection Queries

Query Target Subcollection Filter Order Limit Purpose
Vote state for feed users/{uid}/postVotes postId IN [...] — Chunks of 10 Determine current user's vote on each visible post (Firestore IN limit of 10)
Cascade delete votes users/{uid}/postVotes communityId == — — Remove all votes scoped to a community being deleted
Snippet image sync collectionGroup("communitySnippets") communityId == — Batched Propagate imageURL changes across all users' snippets

Atomic Operations

Transactions

Firestore transactions perform a read then a conditional write atomically. They are used when the write value depends on the current state of the document.

Operation Collections Involved Description
createCommunity communities, users/{uid}/communitySnippets Writes the new community document and the creator's initial snippet in a single atomic unit, preventing a state where the community exists without a corresponding creator snippet
addCommunityAdmin communities, users/{uid}/communitySnippets Reads the community document, appends the target uid to adminIds using FieldValue.arrayUnion(), and sets isAdmin: true on the target user's snippet — all in one atomic operation

Batched Writes

Batched writes apply multiple write operations atomically without requiring a preceding read. They are used for multi-document consistency where the new values are already known.

Operation Collections / Documents Affected Description
Join community communities (increment numberOfMembers), users/{uid}/communitySnippets (create) Member count and the user's snippet are written together; partial application is prevented
Leave community communities (decrement numberOfMembers), users/{uid}/communitySnippets (delete) Mirror of join; ensures the count does not diverge from actual membership
Vote on post posts (update voteStatus via FieldValue.increment()), users/{uid}/postVotes (upsert) The post's aggregate score and the user's individual vote record are updated together; FieldValue.increment() is used server-side to prevent race conditions
Create comment posts (increment numberOfComments), comments (create) The comment count on the parent post and the comment document are created together
Delete comment posts (decrement numberOfComments), comments (delete) Mirror of comment creation
Cascade delete community communities, all associated posts, all associated comments, all users/{uid}/postVotes, all member communitySnippets Full deletion across multiple collections; chunked into batches of up to 500 documents to respect the Firestore batch size limit

Firebase Storage

Firebase Storage provides object storage for binary assets. Firestore documents store the resolved download URL as a string field; the storage path below is the canonical location of the asset.

Storage Path Purpose Referenced By
posts/{postId}/image Image attached to a post posts.imageURL
communities/{communityId}/image Community avatar or banner image communities.imageURL; denormalised into communitySnippets.imageURL, posts.communityImageURL, savedPosts.communityImageURL
users/{userId}/profileImage User profile photograph User profile display; not stored as a Firestore field

Normalisation Analysis

Firestore is a document-oriented database; traditional relational normal forms (1NF–3NF) do not apply directly. The following analysis maps the equivalent concerns onto the document model.

Document Atomicity (1NF Equivalent)

All document fields store scalar values or typed arrays of scalars. No nested repeated groups are used. Notably:

  • Comments are stored as a flat top-level collection rather than as an embedded array on the posts document, avoiding unbounded document growth.
  • Community.adminIds is an array of scalar uid strings, which is an acceptable Firestore primitive — not a nested object collection.

Partial Dependency Avoidance (2NF Equivalent)

Each collection document is uniquely identified by a single document ID (Firestore has no composite primary keys). All non-denormalised fields in a document depend fully on that document's identity. The intentional denormalised fields are the sole exceptions and are documented explicitly.

Transitive Dependencies (3NF Equivalent)

The following transitive dependencies exist by deliberate design for read performance:

  • Post.communityImageURL → depends on communityId → Community.imageURL
  • Post.creatorUsername → depends on creatorId → Auth.displayName
  • Comment.postTitle → depends on postId → Post.title
  • SavedPost.postTitle → depends on postId → Post.title
  • CommunitySnippet.imageURL → depends on communityId → Community.imageURL
  • CommunitySnippet.isAdmin → depends on communityId → Community.adminIds membership

Each of these violations is an accepted trade-off: avoiding the additional document reads that would be required to resolve the dependency at query time reduces both latency and billing cost.

Intentional Denormalization

Denormalised fields introduce update anomalies: if the source value changes, the copies must be updated in kind. The codebase mitigates this risk as follows:

Update Anomaly Mitigation
Community image change Batch-propagated via collectionGroup("communitySnippets") query; posts and savedPosts updated by communityId == query
User display name change Batch-propagated via creatorId == query on posts and comments
Post title change Risk accepted — post title is treated as effectively immutable; no sync logic is implemented
Admin status change Handled atomically within the addCommunityAdmin transaction

ER Diagram

erDiagram
    communities ||--o{ posts : "contains"
    communities ||--o{ comments : "scopes"
    communities ||--o{ communitySnippets : "represented by"
    communities ||--o{ postVotes : "scopes"
    communities ||--o{ savedPosts : "scopes"
    users ||--o{ posts : "creates"
    users ||--o{ comments : "creates"
    users ||--o{ communitySnippets : "owns"
    users ||--o{ postVotes : "owns"
    users ||--o{ savedPosts : "owns"
    posts ||--o{ comments : "receives"
    posts ||--o{ postVotes : "receives"
    posts ||--o{ savedPosts : "bookmarked as"
    comments ||--o{ comments : "replied to by"
Loading

References

  1. Firebase Firestore Data Model — https://firebase.google.com/docs/firestore/data-model
  2. Firestore: Choose a Data Structure — https://firebase.google.com/docs/firestore/manage-data/structure-data
  3. Firestore: Aggregation Queries and Distributed Counters — https://firebase.google.com/docs/firestore/solutions/aggregation
  4. Firestore Transactions and Batched Writes — https://firebase.google.com/docs/firestore/manage-data/transactions
  5. Firestore Collection Group Queries — https://firebase.google.com/docs/firestore/query-data/queries#collection-group-query
  6. Firebase Storage — https://firebase.google.com/docs/storage

Clone this wiki locally