3.1.2 Data Model

A relational schema optimized for efficient querying and scalability, ensuring quick access to user-generated content, interactions, and engagement metrics.

1. Posts Table

Stores all posts made by users.

Column	Type	Constraints & Notes
id	UUID	Primary Key, uniquely identifies each post.
userId	UUID	Foreign Key → users(id), links post to the creator.
content	TEXT	Post text content.
mediaURL	VARCHAR(255)	Optional, stores image/video URLs.
createdAt	TIMESTAMP	Indexed for sorting and retrieval speed.
updatedAt	TIMESTAMP	Stores last modification date.

Indexes:

• INDEX (userId, createdAt) → Optimized for retrieving user posts in chronological order.

---

2. Comments Table

Stores user comments on posts, including optional threading (replies to comments).

Column	Type	Constraints & Notes
id	UUID	Primary Key, uniquely identifies each comment.
postId	UUID	Foreign Key → posts(id), links comment to a post.
userId	UUID	Foreign Key → users(id), links comment to the user.
content	TEXT	Comment text content.
parentCommentId	UUID	Optional, used for threaded replies (self-referencing).
createdAt	TIMESTAMP	Indexed for retrieval speed.

Indexes:

• INDEX (postId, createdAt) → Speeds up fetching comments per post.

• INDEX (parentCommentId) → Optimized for threaded comments.

---

3. Likes/Reactions Table (Optional)

Stores user reactions to posts or comments.

Column	Type	Constraints & Notes
id	UUID	Primary Key, uniquely identifies each reaction.
postId	UUID	Foreign Key → posts(id), links reaction to a post (nullable if reacting to a comment).
commentId	UUID	Foreign Key → comments(id), links reaction to a comment (nullable if reacting to a post).
userId	UUID	Foreign Key → users(id), links reaction to a user.
reactionType	VARCHAR(20)	E.g., "like", "love", "dislike", "fire", etc.
createdAt	TIMESTAMP	Stores when reaction was made.

Indexes:

• INDEX (postId, userId, reactionType) → Optimized for counting reactions per post.

• INDEX (commentId, userId, reactionType) → Optimized for counting reactions per comment.

---

Schema Design Benefits

✅ Optimized Query Performance – Indexes ensure quick lookups, sorting, and filtering. ✅ Scalability & Future-Proofing – Supports threaded replies and multiple reaction types. ✅ Normalization & Efficiency – Avoids redundancy by keeping reactions separate from posts and comments.

This relational model ensures that posts, comments, and reactions can be retrieved, sorted, and counted efficiently while supporting future expansion. 🚀