Database Schema Design (Core MVP)

[Fis-ayo]

Description

Design the core database schema for CodeFolio using Neon (PostgreSQL). This includes defining all necessary tables, relationships, and fields required to support MVP features such as user authentication, profiles, projects, comments, and connections.

The goal is to create a scalable and well-structured schema that supports both current MVP features and future stretch features.

Tasks

• Identify core entities:
  • Users
  • Profiles
  • Projects
  • Connections
• Define fields for each table
  • Example: id, email, password, created_at, etc
• Establish relationships:
  • User → Projects (one-to-many)
  • User → Comments (one-to-many)
  • User ↔ Connections (many-to-many via join table)
• Add constraints:
  • Primary keys
  • Foreign keys
  • Unique constraints (e.g., email)
• Add timestamps (created_at, updated_at)
• Define connection status (pending, accepted)
• Create ER diagram

Design Requirements

• Use PostgreSQL-compatible data types (Neon DB)
• Ensure normalization (avoid redundant data)
• Use consistent naming conventions (snake_case recommended)
• Keep schema simple and easy to extend later for features such as
  • Likes
  • Bookmarks
  • Notifications

References

• Project Planning Doc README.md planning/user_stories.md
• ER Diagram Tool (optional): https://dbdiagram.io/

Acceptance Criteria

• All core tables are defined
• Relationships between tables are clearly established
• Schema supports all MVP features
• Schema is documented (diagram or markdown)
• Schema is reviewed by at least one team member
• Ready for backend implementation

[codewithMycah]