Key Objects & Tables

1. Users Table
   • Purpose: Stores information about all platform users.
   • Key Columns: id, first_name, last_name, email, password_hash, role, created_date, address, city, postal_code, country
   • Relationships: One-to-many with Products, Orders, Reviews, Carts, Favorites Sellers, Favorites Products, Seller Ratings.
2. Products Table
   • Purpose: Contains product listings created by sellers.
   • Key Columns: id, product_name, product_description, price, seller_id, category_id, average_rating, review_count, stock_quantity, created_date
   • Relationships: Many-to-one with Categories; One-to-many with Reviews; Many-to-one with Orders via Order Items.
3. Categories Table
   • Purpose: Defines categories for organizing products.
   • Key Columns: id, categorie_name
   • Relationships: One-to-many with Products.
4. Orders Table
   • Purpose: Tracks customer orders.
   • Key Columns: id, user_id, total_price, delivery_service_id, tracking_number, status, created_date
   • Relationships: One-to-many with Order Items; Many-to-one with Delivery Services; One-to-one or one-to-many with Payments.
5. Delivery Services Table
   • Purpose: Details about third-party delivery providers.
   • Key Columns: id, name, website
   • Relationships: One-to-many with Orders.
6. Order Items Table
   • Purpose: Line items within an order.
   • Key Columns: id, order_id, product_id, quantity, price
   • Relationships: Many-to-one with Orders; Many-to-one with Products.
7. Payments Table
   • Purpose: Records transactions related to orders.
   • Key Columns: id, order_id, payment_date, payment_method, amount, status
   • Relationships: Many-to-one with Orders.
8. Reviews Table
   • Purpose: User-generated product reviews.
   • Key Columns: id, product_id, user_id, rating, comment, created_date
   • Relationships: Many-to-one with Products; Many-to-one with Users.
9. Carts Table
   • Purpose: Represents a shopping cart for each user.
   • Key Columns: id, user_id, created_date, last_updated_date
   • Relationships: One-to-one with Users; One-to-many with Cart Items.
10. Cart Items Table
    • Purpose: Items within a user's cart.
    • Key Columns: id, cart_id, product_id, quantity
    • Relationships: Many-to-one with Carts; Many-to-one with Products.
11. Favorite Sellers Table
    • Purpose: Tracks which sellers users have marked as favorites.
    • Key Columns: id, user_id, seller_id, created_date
    • Relationships: Many-to-one with Users; Many-to-one with Sellers.
12. Favorite Products Table
    • Purpose: Tracks favorite products saved by users.
    • Key Columns: id, user_id, product_id, created_date
    • Relationships: Many-to-one with Users; Many-to-one with Products.
13. Seller Ratings Table
    • Purpose: User ratings for sellers.
    • Key Columns: id, user_id, seller_id, rating, comment, created_date
    • Relationships: Many-to-one with Users; Many-to-one with Sellers.

Relationships Overview

• User to Products: One-to-many (A seller can list multiple products).
• Product to Category: Many-to-one (Each product belongs to one category).
• User to Orders: One-to-many (A user can place many orders).
• Order to Delivery Service: Many-to-one (Each order uses one delivery service).