CS50 SQL Final Project: Building a Local Marketplace Database (Schema, Triggers, Views, Indexes)

Check out my final project for Harvard's CS50 Introduction to Databases with SQL course! I designed and implemented "Neighbourly," a comprehensive SQLite database backend for a local marketplace application.

Project Goal:
Neighbourly aims to connect local customers, business owners, and delivery agents by providing a robust system to manage their interactions, transactions, and feedback within a specific geographic location.

Key Features & Concepts:
Comprehensive Schema: Designed a detailed relational schema with 19 tables to manage diverse entities.
User Roles: Supports distinct user roles (Customer, Business Owner, Delivery Agent) with specific permissions and interactions managed via the users table.
Product & Service Duality: Handles both tangible products (with detailed product_models for variants like size, color, stock) and bookable services (with availability slots, duration) offered by local businesses.
Order Management: Separate workflows for product orders (managed via user_carts containing product_orders) and service_orders, each with distinct status tracking (order_statuses).
Payment Tracking: Manages payments (cart_payments, service_payments) linked to orders, tracking status (payment_statuses) and mode (payment_modes).
Ratings System: Allows users to rate both specific product_models and services (product_ratings, service_ratings).
Data Integrity: Extensive use of Primary Keys, Foreign Keys (with ON DELETE actions like CASCADE, SET NULL, SET DEFAULT), CHECK constraints, UNIQUE constraints, and NOT NULL constraints throughout the schema.

Implemented a wide range of triggers categorized as:
Logging: Capturing changes (Inserts, Updates, Deletes) into a dedicated logs table for audit trails.
Automating: Automatically updating product stock/service slots upon order confirmation/completion, updating payment statuses, calculating prices.
Validating: Ensuring data correctness (e.g., business owner role validation, stock availability checks, date comparisons, status transition logic).
Restricting: Preventing invalid operations (e.g., updating confirmed orders, exceeding stock/slots, preventing password reuse - Note: Password logic assumes application-level hashing).

Optimized Data Access (Views):
Created numerous views to simplify common queries, provide aggregated data (e.g., rating summaries, order counts), partition data (e.g., product vs service businesses), and enhance security (e.g., customer_info hiding sensitive data).

Performance Tuning (Indexes):
Strategically implemented indexes on frequently queried columns, primary keys, and foreign keys to accelerate data retrieval and overall database performance.

Technology used: SQLite 3

This project demonstrates database design principles, complex relationship modeling, data integrity enforcement, automation through triggers, and performance optimization techniques learned in CS50's SQL course.

Checkout the entire code and full documentation on GitHub:
https://www.github.com/devamanikantas...

Also, checkout my submissions of course-work's assignments on GitHub:
https://www.github.com/devamanikantas...

Attribution & Credits:

The "Order Confirmed Concept Illustration" image shown around the 3:31 mark is sourced from Iconfinder and used for illustrative purposes only, Find it here: https://www.iconfinder.com/icons/9307...

Educational Content & Copyright:

This video and the project presented are created for educational purposes as part of the CS50 SQL coursework. Any third-party assets (like images or concepts) are used illustratively under the principles of fair use for education and commentary.

If you are a copyright holder and believe any content used in this video constitutes an infringement, please contact me directly at my (  / deva.codes  ) before submitting a formal takedown notice. I am committed to respecting intellectual property rights and will address any valid concerns promptly.

Timestamps:
(0:00) Intro & Project Goal
(0:17) Database Structure & ER Diagram
(1:43) Optimizations Overview
(1:58) Optimizations: Views (Security & Aggregation)
(2:36) Optimizations: Indexes (Performance)
(3:11) Optimizations: Triggers Introduction
(3:26) Triggers: Automating Examples
(3:49) Triggers: Validating & Restricting Logic
(4:14) Triggers: Logging Examples
(4:51) Project Limitations
(5:16) Conclusion & Outro

Thanks for watching! Let me know what you think in the comments.

#cs50 #SQL #SQLite #DatabaseDesign #Databases #WebDevelopment #Programming #Harvard #FinalProject #Portfolio #Triggers #Views #Indexes #LocalMarketplace #project