# Database Relationships --- **Document Version:** 1.0 **Project:** Student Bazaar **Document Type:** Database Relationships **Status:** Approved **Last Updated:** 29 June 2026 **Author:** Nemmi Tangha --- # 1. Overview This document defines every relationship between database tables used within Student Bazaar. These relationships are implemented using foreign keys and SQLAlchemy ORM relationships. The database follows the principles of: - Referential Integrity - Third Normal Form (3NF) - High Cohesion - Low Coupling --- # 2. Authentication Relationships ## users → user_profiles Relationship: One-to-One ``` users │ │ 1 ▼ user_profiles ``` Foreign Key ``` user_profiles.user_id → users.id ``` --- ## users → email_verifications Relationship: One-to-Many ``` users │ │ 1 ▼ email_verifications ``` Foreign Key ``` email_verifications.user_id → users.id ``` --- ## users → password_reset_tokens Relationship: One-to-Many ``` users │ │ 1 ▼ password_reset_tokens ``` Foreign Key ``` password_reset_tokens.user_id → users.id ``` --- # 3. Marketplace Relationships ## categories → listings Relationship: One-to-Many ``` categories │ │ 1 ▼ listings ``` Foreign Key ``` listings.category_id → categories.id ``` --- ## users → listings Relationship: One-to-Many ``` users │ │ 1 ▼ listings ``` Foreign Key ``` listings.seller_id → users.id ``` --- ## listings → listing_images Relationship: One-to-Many ``` listings │ │ 1 ▼ listing_images ``` Foreign Key ``` listing_images.listing_id → listings.id ``` --- ## users → favorites Relationship: One-to-Many ``` users │ │ 1 ▼ favorites ``` Foreign Key ``` favorites.user_id → users.id ``` --- ## listings → favorites Relationship: One-to-Many ``` listings │ │ 1 ▼ favorites ``` Foreign Key ``` favorites.listing_id → listings.id ``` --- # 4. Negotiation Relationships ## listings → offers Relationship: One-to-Many ``` listings │ │ 1 ▼ offers ``` Foreign Key ``` offers.listing_id → listings.id ``` --- ## users → offers (Sender) Relationship: One-to-Many ``` users │ │ 1 ▼ offers ``` Foreign Key ``` offers.sender_id → users.id ``` --- ## users → offers (Receiver) Relationship: One-to-Many ``` users │ │ 1 ▼ offers ``` Foreign Key ``` offers.receiver_id → users.id ``` --- # 5. Chat Relationships ## listings → conversations Relationship: One-to-Many ``` listings │ │ 1 ▼ conversations ``` Foreign Key ``` conversations.listing_id → listings.id ``` --- ## users → conversations (Buyer) Relationship: One-to-Many Foreign Key ``` conversations.buyer_id → users.id ``` --- ## users → conversations (Seller) Relationship: One-to-Many Foreign Key ``` conversations.seller_id → users.id ``` --- ## conversations → messages Relationship: One-to-Many ``` conversations │ │ 1 ▼ messages ``` Foreign Key ``` messages.conversation_id → conversations.id ``` --- ## users → messages Relationship: One-to-Many Foreign Key ``` messages.sender_id → users.id ``` --- # 6. Meet-up Relationships ## listings → meetups Relationship: One-to-One ``` listings │ │ 1 ▼ meetups ``` Foreign Key ``` meetups.listing_id → listings.id ``` --- ## offers → meetups Relationship: One-to-One ``` offers │ │ 1 ▼ meetups ``` Foreign Key ``` meetups.accepted_offer_id → offers.id ``` --- ## users → meetups Relationship: One-to-Many Foreign Keys ``` meetups.buyer_id → users.id meetups.seller_id → users.id ``` --- # 7. Notification Relationships ## users → notifications Relationship: One-to-Many ``` users │ │ 1 ▼ notifications ``` Foreign Key ``` notifications.user_id → users.id ``` --- # 8. Administration Relationships ## users → reports Relationship: One-to-Many Foreign Keys ``` reports.reporter_id → users.id reports.reported_user_id → users.id reports.resolved_by → users.id ``` --- ## listings → reports Relationship: One-to-Many Foreign Key ``` reports.listing_id → listings.id ``` --- ## conversations → reports Relationship: One-to-Many Foreign Key ``` reports.conversation_id → conversations.id ``` --- ## users → premium_subscriptions Relationship: One-to-Many Foreign Key ``` premium_subscriptions.user_id → users.id ``` --- ## users → admin_logs Relationship: One-to-Many Foreign Key ``` admin_logs.admin_id → users.id ``` --- # 9. System Settings Relationships ## users → system_settings Relationship: One-to-Many Foreign Key ``` system_settings.updated_by → users.id ``` --- # 10. Relationship Summary | Parent Table | Child Table | Relationship | |--------------|-------------|--------------| | users | user_profiles | One-to-One | | users | email_verifications | One-to-Many | | users | password_reset_tokens | One-to-Many | | categories | listings | One-to-Many | | users | listings | One-to-Many | | listings | listing_images | One-to-Many | | users | favorites | One-to-Many | | listings | favorites | One-to-Many | | listings | offers | One-to-Many | | users | offers | One-to-Many | | listings | conversations | One-to-Many | | conversations | messages | One-to-Many | | listings | meetups | One-to-One | | offers | meetups | One-to-One | | users | notifications | One-to-Many | | users | reports | One-to-Many | | listings | reports | One-to-Many | | conversations | reports | One-to-Many | | users | premium_subscriptions | One-to-Many | | users | admin_logs | One-to-Many | | users | system_settings | One-to-Many | --- # 11. Conclusion The Student Bazaar database is designed using normalized relational principles with clearly defined one-to-one and one-to-many relationships. These relationships provide the foundation for SQLAlchemy ORM models, ensuring referential integrity, scalability, and maintainability across the application. --- # 12. Delete Behavior The following table defines the expected delete behavior for each relationship. These rules maintain referential integrity and guide the implementation of foreign key constraints and SQLAlchemy cascade options. | Relationship | Delete Behavior | Reason | |--------------|-----------------|--------| | users → user_profiles | CASCADE | Profile is removed when the user is deleted. | | users → email_verifications | CASCADE | Verification records are no longer needed. | | users → password_reset_tokens | CASCADE | Reset tokens become invalid. | | categories → listings | RESTRICT | Categories with listings cannot be deleted. | | users → listings | RESTRICT | Users with active listings should not be deleted. | | listings → listing_images | CASCADE | Images belong only to their listing. | | users → favorites | CASCADE | Favorites are removed with the user. | | listings → favorites | CASCADE | Favorites are removed when the listing is deleted. | | listings → offers | RESTRICT | Offer history must be preserved. | | users → offers | RESTRICT | Offer history must remain intact. | | listings → conversations | RESTRICT | Conversations are part of marketplace history. | | conversations → messages | CASCADE | Messages belong only to their conversation. | | listings → meetups | RESTRICT | Meet-up history should be preserved. | | offers → meetups | RESTRICT | Accepted offers must remain linked to meet-ups. | | users → notifications | CASCADE | Notifications are removed with the user. | | users → reports | RESTRICT | Reports form part of the audit history. | | listings → reports | RESTRICT | Report history should remain available. | | conversations → reports | RESTRICT | Reported conversations must remain traceable. | | users → premium_subscriptions | RESTRICT | Subscription history should be preserved. | | users → admin_logs | RESTRICT | Audit logs must never be lost. | | users → system_settings | SET NULL | Preserve settings even if the Super Admin account changes. |