制作各表的主题字段

数据库名称：sales_data
 
1. 商品表（Products）
 
CREATE TABLE products (
  ProductID INT AUTO_INCREMENT PRIMARY KEY,
  ProductName VARCHAR(255) NOT NULL,
  Category VARCHAR(255) NOT NULL,
  CurrentPrice DECIMAL(10, 2) NOT NULL,
  StockQuantity INT NOT NULL,
  Size VARCHAR(50) NOT NULL,
  Color VARCHAR(50) NOT NULL,
  ListingStatus VARCHAR(50) NOT NULL,
  SKU CHAR(20) NOT NULL
);
 
2. 用户表（Users）
 
CREATE TABLE IF NOT EXISTS User Table (
  user_id INT NOT NULL,
  name VARCHAR(255) NOT NULL,
  contact_info VARCHAR(100) NOT NULL,
  product_id VARCHAR(100) NOT NULL,
  product_name VARCHAR(255) NOT NULL,
  specification TEXT NOT NULL,
  quantity INT NOT NULL,
  order_number INT NOT NULL,
  order_status VARCHAR(50) NOT NULL,
  payment_method VARCHAR(50) NOT NULL,
  unit_price DECIMAL(10, 2) NOT NULL,
  total_amount DECIMAL(10, 2) NOT NULL,
  address VARCHAR(255) NOT NULL,
  logistics_company VARCHAR(100) NOT NULL,
  logistics_number VARCHAR(100) NOT NULL,
  PRIMARY KEY (user_id, order_number)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
 
3. 订单表（Orders）
 
CREATE TABLE IF NOT EXISTS OrderTable (
  user_id INT NOT NULL,
  name VARCHAR(255) NOT NULL,
  contact_info VARCHAR(100) NOT NULL,
  product_id VARCHAR(100) NOT NULL,
  product_name VARCHAR(255) NOT NULL,
  specification TEXT NOT NULL,
  quantity INT NOT NULL,
  order_number INT NOT NULL,
  order_status VARCHAR(50) NOT NULL,
  payment_method VARCHAR(50) NOT NULL,
  unit_price DECIMAL(10, 2) NOT NULL,
  total_amount DECIMAL(10, 2) NOT NULL,
  address VARCHAR(255) NOT NULL,
  logistics_company VARCHAR(100) NOT NULL,
  logistics_number VARCHAR(100) NOT NULL,
  PRIMARY KEY (user_id, order_number)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
 
4. 竞争对手表（Competitors）
 
CREATE TABLE competitorproducts (
 Product_ID INT NOT NULL,
 Product_Name VARCHAR(255) NOT NULL,
 Category VARCHAR(100) NOT NULL,
 Current_Price DECIMAL(10, 2) NOT NULL,
 Stock_Quantity INT NOT NULL,
 Size VARCHAR(50) NOT NULL,
 Color VARCHAR(50) NOT NULL,
 Listing_Status VARCHAR(50) NOT NULL,
 SKU VARCHAR(100) NOT NULL
);
 
5.商品销售表（sales）
 
CREATE TABLE Sales (
    SaleID INT AUTO_INCREMENT PRIMARY KEY COMMENT '销售记录唯一标识',
    ProductID INT NOT NULL COMMENT '商品ID，关联商品表',
    OrderID INT NOT NULL COMMENT '订单ID，关联订单表',
    UserID INT NOT NULL COMMENT '用户ID，关联用户表',
    Quantity INT NOT NULL COMMENT '销售数量',
    SalePrice DECIMAL(10, 2) NOT NULL COMMENT '销售单价',
    TotalAmount DECIMAL(10, 2) COMMENT '销售总价',
    SaleDate DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '销售时间',
    Discount DECIMAL(5, 2) COMMENT '折扣金额',
    PromotionID INT COMMENT '促销活动ID，关联促销表',
    Channel VARCHAR(50) COMMENT '销售渠道（如网站、APP、线下）',
    Region VARCHAR(100) COMMENT '销售地区',
    Status VARCHAR(50) COMMENT '销售状态（如已完成、已退款等）',
    FOREIGN KEY (ProductID) REFERENCES Products(ProductID),
    FOREIGN KEY (OrderID) REFERENCES Orders(OrderID),
    FOREIGN KEY (UserID) REFERENCES Users(UserID),
    FOREIGN KEY (PromotionID) REFERENCES Promotions(PromotionID)
) ;