# Marketplace Database Design --- **Document Version:** 1.0 **Project:** Student Bazaar **Document Type:** Marketplace Database Schema **Status:** Approved **Last Updated:** 28 June 2026 **Author:** Nemmi Tangha --- # 1. Overview The Marketplace module is the core component of Student Bazaar. It enables students to create listings, upload product images, browse categories, save favorite items, and manage the lifecycle of products from listing creation to successful sale. The Marketplace module consists of four tables: 1. categories 2. listings 3. listing_images 4. favorites The design supports future expansion while maintaining a normalized and scalable database structure. --- # 2. Table: categories ## Purpose Stores all marketplace categories used to organize product listings. Each listing belongs to one category. --- ## Columns | Column | Data Type | Nullable | Default | Key | Description | | ------------- | ------------ | -------- | ----------------- | ------ | ------------------------------------------- | | id | BIGINT | No | AUTO_INCREMENT | PK | Internal primary key | | public_id | CHAR(36) | No | UUID | UNIQUE | Public identifier | | name | VARCHAR(100) | No | — | UNIQUE | Category name | | description | TEXT | Yes | NULL | — | Category description | | icon | VARCHAR(255) | Yes | NULL | — | Category icon or image | | is_active | BOOLEAN | No | TRUE | INDEX | Indicates whether the category is available | | display_order | INT | No | 1 | — | Order in which categories are displayed | | created_at | DATETIME | No | CURRENT_TIMESTAMP | — | Record creation timestamp | | updated_at | DATETIME | No | CURRENT_TIMESTAMP | — | Last update timestamp | --- ## Relationships * One Category → Many Listings --- ## Indexes * PRIMARY KEY (`id`) * UNIQUE (`public_id`) * UNIQUE (`name`) * INDEX (`is_active`) * INDEX (`display_order`) --- ## Business Rules * Category names must be unique. * Categories can be disabled without deleting them. * Listings cannot belong to inactive categories. * Categories should be displayed according to `display_order`. --- # 3. Table: listings ## Purpose Stores all products listed by students for sale. Each listing belongs to one seller and one category. --- ## Columns | Column | Data Type | Nullable | Default | Key | Description | | -------------- | --------------------------------------------------------------- | -------- | ----------------- | ------ | ----------------------------------------------- | | id | BIGINT | No | AUTO_INCREMENT | PK | Internal primary key | | public_id | CHAR(36) | No | UUID | UNIQUE | Public identifier | | sku | VARCHAR(50) | No | Generated | UNIQUE | Human-readable listing reference | | seller_id | BIGINT | No | — | FK | References users.id | | category_id | BIGINT | No | — | FK | References categories.id | | title | VARCHAR(200) | No | — | INDEX | Listing title | | slug | VARCHAR(255) | No | — | UNIQUE | SEO-friendly listing URL | | description | TEXT | No | — | — | Product description | | price | DECIMAL(10,2) | No | — | — | Original listing price | | sold_price | DECIMAL(10,2) | Yes | NULL | — | Final agreed selling price | | condition | ENUM('new','excellent','good','fair','poor') | No | good | INDEX | Product condition | | status | ENUM('draft','available','reserved','sold','expired','removed') | No | draft | INDEX | Listing status | | visibility | ENUM('public','hidden') | No | public | INDEX | Listing visibility | | negotiable | BOOLEAN | No | TRUE | — | Indicates whether offers are allowed | | is_featured | BOOLEAN | No | FALSE | INDEX | Premium featured listing | | location | VARCHAR(150) | Yes | NULL | — | General meetup location (e.g., Campus, Library) | | review_status | ENUM('approved','rejected') | No | approved | INDEX | Listing review result | | reviewed_by | BIGINT | Yes | NULL | FK | References users.id (Super Admin) | | updated_by | BIGINT | Yes | NULL | FK | References users.id | | published_at | DATETIME | Yes | NULL | — | Publication timestamp | | expires_at | DATETIME | Yes | NULL | INDEX | Listing expiration | | deleted_at | DATETIME | Yes | NULL | INDEX | Soft deletion timestamp | | view_count | INT | No | 0 | — | Number of listing views | | favorite_count | INT | No | 0 | — | Number of users who favorited the listing | | offer_count | INT | No | 0 | — | Number of offers received | | created_at | DATETIME | No | CURRENT_TIMESTAMP | INDEX | Record creation timestamp | | updated_at | DATETIME | No | CURRENT_TIMESTAMP | — | Last update timestamp | --- ## Relationships * One Student → Many Listings * One Category → Many Listings * One Listing → Many Images * One Listing → Many Offers * One Listing → Many Conversations * One Listing → Many Favorites * One Listing → One Meet-up (after an accepted offer) --- ## Indexes * PRIMARY KEY (`id`) * UNIQUE (`public_id`) * UNIQUE (`sku`) * UNIQUE (`slug`) * INDEX (`seller_id`) * INDEX (`category_id`) * INDEX (`condition`) * INDEX (`status`) * INDEX (`visibility`) * INDEX (`is_featured`) * INDEX (`expires_at`) * INDEX (`deleted_at`) * INDEX (`created_at`) --- ## Business Rules * Every listing belongs to one student. * Every listing belongs to one category. * A listing must contain at least one image before publication. * A listing may contain a maximum of seven images. * Only the listing owner can edit or delete the listing. * Super Admins may hide or remove listings when necessary. * Soft deletion is used instead of permanent deletion. * Listings marked as **Sold** cannot receive new offers. * Listings marked as **Reserved** remain visible but cannot accept additional buyers. * Expired listings are automatically hidden from marketplace searches. * View, favorite, and offer counters must remain synchronized with platform activity. * Student Bazaar does not process payments. Transactions are completed directly between students. ``` ``` --- # 4. Table: listing_images ## Purpose Stores images associated with marketplace listings. Each listing must contain at least one image and may contain a maximum of seven images. Images are displayed according to their display order. --- ## Columns | Column | Data Type | Nullable | Default | Key | Description | | ------------- | ------------ | -------- | ----------------- | ------ | -------------------------- | | id | BIGINT | No | AUTO_INCREMENT | PK | Internal primary key | | public_id | CHAR(36) | No | UUID | UNIQUE | Public identifier | | listing_id | BIGINT | No | — | FK | References listings.id | | image_path | VARCHAR(255) | No | — | — | Stored image path | | caption | VARCHAR(100) | Yes | NULL | — | Optional image caption | | display_order | INT | No | 1 | INDEX | Display order of the image | | created_at | DATETIME | No | CURRENT_TIMESTAMP | — | Record creation timestamp | --- ## Relationships * One Listing → Many Listing Images --- ## Indexes * PRIMARY KEY (`id`) * UNIQUE (`public_id`) * INDEX (`listing_id`) * INDEX (`display_order`) --- ## Business Rules * Every listing must contain at least one image. * A listing may contain a maximum of seven images. * Images must be validated before upload. * Only image files are permitted. * The image with the lowest `display_order` is used as the cover image. * Image order can be changed by the listing owner. --- # 5. Table: favorites ## Purpose Allows students to save listings for future reference. Each student may favorite many listings, and each listing may be favorited by many students. --- ## Columns | Column | Data Type | Nullable | Default | Key | Description | | ---------- | --------- | -------- | ----------------- | ------ | ------------------------------ | | id | BIGINT | No | AUTO_INCREMENT | PK | Internal primary key | | public_id | CHAR(36) | No | UUID | UNIQUE | Public identifier | | user_id | BIGINT | No | — | FK | References users.id | | listing_id | BIGINT | No | — | FK | References listings.id | | created_at | DATETIME | No | CURRENT_TIMESTAMP | — | Date the listing was favorited | --- ## Relationships * One Student → Many Favorites * One Listing → Many Favorites --- ## Indexes * PRIMARY KEY (`id`) * INDEX (`user_id`) * INDEX (`listing_id`) * UNIQUE (`user_id`, `listing_id`) --- ## Business Rules * A student may favorite a listing only once. * Removing a favorite deletes the favorite record. * The `favorite_count` field in the listings table must remain synchronized. --- # 6. Marketplace Relationships ```text Category │ │ 1 ▼ Listings │ ├──────────────┐ │ │ │ ▼ │ Listing Images │ ▼ Favorites Student │ ├──────────────► Listings │ └──────────────► Favorites ``` --- # 7. Marketplace Constraints The Marketplace module follows the following constraints: * Every listing belongs to exactly one student. * Every listing belongs to exactly one category. * Listings require a minimum of one image. * Listings support a maximum of seven images. * Each student may favorite the same listing only once. * Soft deletion is used for listings. * Public identifiers must remain unique. * Listing prices must use `DECIMAL(10,2)`. --- # 8. Marketplace Business Rules The Marketplace module follows these business rules: * Students can create draft listings before publishing. * Listings become visible only when their status is **Available**. * Reserved listings remain visible but cannot accept additional buyers. * Sold listings are read-only. * Expired listings are hidden automatically. * Featured listings are available only to Premium members. * Students may edit only their own listings. * Super Admins may hide or remove listings that violate platform policies. * Marketplace transactions occur outside Student Bazaar. * Student Bazaar is not responsible for payments, deliveries, or disputes between students. * Favorite, offer, and view counters must accurately reflect platform activity. * Images must pass file validation before storage. --- # 9. Marketplace Security Rules The Marketplace module enforces the following security measures: * Ownership validation before editing or deleting listings. * Ownership validation before uploading images. * Secure image upload validation. * File type verification. * File size validation. * Protection against unauthorized listing modification. * Soft deletion instead of permanent deletion. * Parameterized database queries using SQLAlchemy. * Input validation on every create and update request. --- # 10. Marketplace Summary The Marketplace module is the foundation of Student Bazaar. It enables students to create listings, organize products into categories, upload images, save favorite products, and manage the complete listing lifecycle. The database design supports: * Secure ownership validation * Efficient searching and filtering * Premium features * Marketplace analytics * Future scalability * Normalized database design * Integration with the Offers, Chat, Notifications, and Meet-up modules The Marketplace schema follows the approved Student Bazaar database standards and serves as the foundation for all marketplace-related functionality.