# Entity Relationship Diagram (ERD)

---

**Document Version:** 1.0  
**Project:** Student Bazaar  
**Document Type:** Entity Relationship Diagram (ERD)  
**Status:** Approved  
**Last Updated:** 29 June 2026  
**Author:** Nemmi Tangha

---

# 1. Overview

This document provides a high-level representation of the relationships between the database entities used in Student Bazaar.

The ERD represents the logical structure of Version 1 and serves as the reference for SQLAlchemy ORM relationships and MySQL foreign key constraints.

---

# 2. Authentication Module

```text
users
│
├────────────── user_profiles
├────────────── email_verifications
└────────────── password_reset_tokens
```

---

# 3. Marketplace Module

```text
categories
      │
      ▼
  listings
      │
      ├──────── listing_images
      ├──────── favorites
      ├──────── offers
      ├──────── conversations
      ├──────── meetups
      └──────── reports

users
   │
   ├──────── listings
   └──────── favorites
```

---

# 4. Negotiation Module

```text
users
   │
   ├──────── sender
   └──────── receiver
          │
          ▼
        offers
```

---

# 5. Chat Module

```text
listings
     │
     ▼
conversations
      │
      ▼
messages

users
   │
   ├──────── buyer
   ├──────── seller
   └──────── sender
```

---

# 6. Meet-up Module

```text
offers
    │
    ▼
meetups

users
   │
   ├──────── buyer
   └──────── seller
```

---

# 7. Notifications Module

```text
users
   │
   ▼
notifications
```

---

# 8. Administration Module

```text
users
   │
   ├──────── reports
   ├──────── premium_subscriptions
   ├──────── admin_logs
   └──────── system_settings
```

---

# 9. Complete ERD

```text
                                   users
                                      │
      ┌───────────────┬───────────────┼───────────────────┬───────────────┐
      │               │               │                   │               │
      ▼               ▼               ▼                   ▼               ▼
user_profiles   email_verifications password_reset_tokens listings   notifications
                                                               │
                                                   ┌───────────┼──────────────┐
                                                   ▼           ▼              ▼
                                            listing_images favorites      offers
                                                                          │
                                                                          ▼
                                                                     meetups
                                                   │
                                                   ▼
                                            conversations
                                                   │
                                                   ▼
                                               messages

categories
     │
     ▼
 listings

reports
     ▲
     │
 listings

premium_subscriptions

admin_logs

system_settings
```

---

# 10. Relationship Summary

| Parent | Child | Cardinality |
|----------|--------|------------|
| users | user_profiles | 1 : 1 |
| users | email_verifications | 1 : N |
| users | password_reset_tokens | 1 : N |
| users | listings | 1 : N |
| categories | listings | 1 : N |
| listings | listing_images | 1 : N |
| users | favorites | 1 : N |
| listings | favorites | 1 : N |
| listings | offers | 1 : N |
| listings | conversations | 1 : N |
| conversations | messages | 1 : N |
| offers | meetups | 1 : 1 |
| listings | meetups | 1 : 1 |
| users | notifications | 1 : N |
| users | reports | 1 : N |
| listings | reports | 1 : N |
| conversations | reports | 1 : N |
| users | premium_subscriptions | 1 : N |
| users | admin_logs | 1 : N |
| users | system_settings | 1 : N |

---

# 11. Design Notes

The Student Bazaar database follows these architectural principles:

- Third Normal Form (3NF)
- Referential Integrity
- UUID-based public identifiers
- Internal numeric primary keys
- Modular database design
- SQLAlchemy ORM compatibility
- Future scalability
- Security by Design

---

# 12. Conclusion

This Entity Relationship Diagram represents the approved Version 1 database architecture for Student Bazaar.

All SQLAlchemy models, Flask services, migrations, and business logic should conform to these relationships to ensure consistency, maintainability, and data integrity throughout the application.