# Database Design

---

**Document Version:** 1.0
**Project:** Student Bazaar
**Document Type:** Database Design Specification
**Status:** Approved
**Last Updated:** 28 June 2026
**Author:** Nemmi Tangha

---

# 1. Purpose

This document defines the database architecture for Student Bazaar.

It serves as the primary reference for designing, implementing, and maintaining the MySQL database used by the application.

The database is designed to support a scalable, secure, and normalized marketplace while maintaining high performance and data integrity.

---

# 2. Database Management System

The application uses:

**Database:** MySQL

The application communicates with MySQL using:

* Flask
* Flask-SQLAlchemy (ORM)

---

# 3. Database Design Principles

The database follows these principles:

* Third Normal Form (3NF)
* Data Integrity
* Referential Integrity
* Minimal Data Redundancy
* Scalable Table Design
* Efficient Query Performance
* Secure Data Storage

---

# 4. Naming Conventions

## Tables

Use lowercase plural names.

Examples:

* users
* listings
* messages
* notifications

---

## Primary Keys

Every table uses:

id

as an auto-increment primary key.

---

## Foreign Keys

Use:

user_id

listing_id

conversation_id

offer_id

etc.

---

## Timestamps

Every major table contains:

created_at

updated_at

where appropriate.

---

# 5. Database Entities

The Student Bazaar database consists of the following major entities.

## Authentication

* Users
* Email Verification
* Password Reset

---

## Marketplace

* Listings
* Product Images
* Categories
* Favorites

---

## Negotiation

* Offers
* Offer History

---

## Communication

* Conversations
* Messages
* Notifications

---

## Meet-up

* Meet-up Schedule

---

## Administration

* Reports
* Advertisements
* Premium Subscriptions
* Super Admin Logs

---

## Analytics

* Listing Views
* Seller Statistics

---

# 6. Database Relationships

The database is primarily relational.

Examples include:

* One User → Many Listings
* One Listing → Many Images
* One Listing → Many Offers
* One Conversation → Many Messages
* One User → Many Notifications
* One Listing → One Seller
* One Listing → Many Buyers (offers)
* One Offer → One Meet-up (if accepted)

Foreign keys are used to maintain referential integrity.

---

# 7. Data Integrity Rules

The following rules must always be enforced:

* Every listing belongs to exactly one student.
* A listing must contain at least one image.
* A listing may contain a maximum of seven images.
* Offers must reference an existing listing.
* Messages must belong to a valid conversation.
* Notifications must reference an existing user.
* Meet-ups can only be created after an offer is accepted.

---

# 8. Indexing Strategy

Indexes will be created for frequently searched fields including:

* email
* listing title
* category
* listing status
* created_at
* conversation_id
* sender_id
* receiver_id

Additional indexes may be added based on future performance analysis.

---

# 9. Soft Delete Strategy

Where appropriate, records should be marked as inactive rather than permanently deleted.

Examples:

* Listings
* Advertisements
* Premium Plans

This preserves historical data and supports auditing.

---

# 10. Security Considerations

The database must support:

* Parameterized Queries
* Password Hashing
* Secure Session Storage
* Email Verification Tokens
* Password Reset Tokens

Sensitive information must never be stored in plain text.

---

# 11. Initial Database Modules

The first version of Student Bazaar will include database structures for:

* Authentication
* Marketplace
* Product Images
* Categories
* Favorites
* Offers
* Conversations
* Messages
* Meet-ups
* Notifications
* Reports
* Advertisements
* Premium Subscriptions
* Seller Statistics
* Super Admin Logs

---

# 12. Future Database Expansion

The database architecture allows future integration of:

* Mobile Applications
* AI Recommendations
* Payment Gateway
* QR Verification
* College Verification
* Push Notifications
* Delivery Tracking

No major structural redesign should be required when these features are introduced.

---

# 13. Conclusion

The Student Bazaar database is designed as a scalable relational database that supports all Version 1 functionality while remaining flexible for future enhancements.

Normalization, indexing, and modular entity design ensure that the database remains maintainable, efficient, and secure throughout the lifecycle of the application.