Database Schema

Relevant source files

The following files were used as context for generating this wiki page:

• backend/model/product.js
• backend/model/coupounCode.js
• backend/controller/product.js
• backend/app.js
• README.md
• backend/package.json

The database for this E-Commerce platform is built using a combination of SQL and NoSQL-like patterns, primarily implemented with Sequelize for a MySQL database. This hybrid approach supports structured data for core entities like products and shops, while leveraging JSON fields for flexibility in storing less structured or variable data such as product reviews, images, and order contents.

The schema is designed to manage all key aspects of the e-commerce workflow, including user and shop management, product cataloging, order processing, promotional events, and real-time chat. The models are defined in the backend/model/ directory, establishing the structure and relationships for entities like Product, User, Shop, Order, CoupounCode, Event, and Conversation.

Entity Relationship Diagram

The following diagram illustrates the high-level relationships between the primary data models in the system. Note that some relationships, like the one between Order and Product, are managed within JSON fields rather than through traditional foreign key constraints.

Product Schema

The Product model represents items available for sale in a shop. It contains details about the product, pricing, inventory, and associations with its parent shop. Several fields are denormalized and stored as JSON to simplify data retrieval.

Sources: backend/model/product.js

Fields

Field	Type	Constraints & Description
id	INTEGER	Primary Key, Auto-increment
name	STRING	Not Null. The name of the product.
description	TEXT	Not Null. A detailed description of the product.
category	STRING	Not Null. The product category.
tags	STRING	Optional. Comma-separated tags for searchability.
originalPrice	DECIMAL(10, 2)	Optional. The original price before any discount.
discountPrice	DECIMAL(10, 2)	Not Null. The final selling price.
stock	INTEGER	Not Null. The available quantity in stock.
images	JSON	A JSON array of image filenames associated with the product.
reviews	JSON	A JSON array of review objects. Each review can contain user info, rating, and comment.
ratings	DECIMAL(3, 2)	The average rating calculated from reviews.
shopId	STRING	Not Null. Foreign key linking to the Shop model.
shop	JSON	Not Null. A denormalized JSON object containing basic shop information.
sold_out	INTEGER	Default: 0. A counter for the number of units sold.
created_at	DATE	Default: NOW(). Timestamp of creation.

Code Definition

This snippet from product.js shows the Sequelize model definition.

// Source: backend/model/product.js:5-61
const Product = sequelize.define(
  "Product",
  {
    name: {
      type: DataTypes.STRING,
      allowNull: false,
    },
    description: {
      type: DataTypes.TEXT,
      allowNull: false,
    },
    // ... other fields
    images: {
      type: DataTypes.JSON,
    },
    reviews: {
      type: DataTypes.JSON,
    },
    shop: {
      type: DataTypes.JSON,
      allowNull: false,
    },
    sold_out: {
      type: DataTypes.INTEGER,
      defaultValue: 0,
    },
    // ...
  },
  {
    tableName: "products",
    timestamps: false,
  }
);

Coupon Code Schema

The CoupounCode model stores information about discount codes that can be applied to purchases. Each code is associated with a specific shop.

Sources: backend/model/coupounCode.js

Fields

Field	Type	Constraints & Description
id	INTEGER	Primary Key, Auto-increment
name	STRING	Not Null, Unique. The unique code string (e.g., "SUMMER20").
value	DECIMAL(10, 2)	Not Null. The percentage value of the discount.
minAmount	DECIMAL(10, 2)	Optional. The minimum order amount to apply the coupon.
maxAmount	DECIMAL(10, 2)	Optional. The maximum discount amount.
shopId	STRING	Not Null. Foreign key linking to the Shop model.
selectedProducts	TEXT	Optional. A JSON string of product IDs this coupon applies to. Uses a getter/setter to parse.
created_at	DATE	Default: NOW(). Timestamp of creation.

Special Logic: selectedProducts

The selectedProducts field uses a getter and setter to automatically handle JSON serialization and deserialization, making it easier to work with the array of product IDs in the application logic.

// Source: backend/model/coupounCode.js:25-34
selectedProducts: {
    type: DataTypes.TEXT,
    allowNull: true,
    get() {
      const value = this.getDataValue("selectedProducts");
      return value ? JSON.parse(value) : [];
    },
    set(value) {
      this.setDataValue("selectedProducts", JSON.stringify(value));
    },
},

Other Core Models

While the full source code for all models was not provided, their structure can be inferred from their usage in controllers, routes, and the project's README.md.

Sources: README.md, backend/app.js, backend/controller/product.js

User Model

• Purpose: Manages user accounts, including customers, sellers, and administrators.
• Inferred Fields: id, name, email, password (hashed), avatar, role (e.g., "Admin", "Seller", "user"), addresses (JSON).
• Key Logic: The role field is used for authorization middleware to protect routes. Admins have the ability to update user roles.
  Source: backend/app.js:81-112

Shop Model

• Purpose: Represents a seller's storefront. It is linked to a User with the "Seller" role.
• Inferred Fields: id, name, email, password (hashed), description, address, phoneNumber, avatar, zipCode, userId (FK).
• Relationships: A shop has many products, events, and coupon codes.
  Source: backend/controller/product.js:18-20

Order Model

• Purpose: Stores details of a customer's order.
• Inferred Fields: id, cart (JSON of products), shippingAddress (JSON), user (JSON), totalPrice, status (e.g., "Processing", "Delivered"), paymentInfo (JSON), userId (FK).
• Note: The cart, shippingAddress, and user fields are stored as JSON, which is a form of denormalization to simplify order retrieval.

Event Model

• Purpose: Manages promotional events, which are similar to products but have a limited duration.
• Inferred Fields: id, name, description, start_Date, Finish_Date, status, discountPrice, stock, images (JSON), shopId (FK).

Conversation Model

• Purpose: Facilitates the real-time chat feature between users and shops.
• Inferred Fields: id, groupTitle (unique identifier), members (JSON array of user/shop IDs), lastMessage, lastMessageId.