MySQL

CREATE TABLE Consumer (
    ConsumerId INT AUTO_INCREMENT PRIMARY KEY,
    ConsumerName VARCHAR(50) NOT NULL UNIQUE,
    Password VARCHAR(255) NOT NULL,
    Email VARCHAR(100) UNIQUE,
    Phone VARCHAR(20) UNIQUE,
    CreateTime TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
CREATE TABLE ConsumerWallet (
    WalletId INT AUTO_INCREMENT PRIMARY KEY,
    ConsumerId INT NOT NULL,
    Balance DECIMAL(10, 2) NOT NULL DEFAULT 0.00,
    CreatedAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (ConsumerId) REFERENCES Consumer(ConsumerId) ON DELETE CASCADE
);
CREATE TABLE ConsumerWalletLog (
    LogId INT AUTO_INCREMENT PRIMARY KEY,
    ConsumerId INT NOT NULL,
    TransactionType VARCHAR(50) NOT NULL,
    Amount DECIMAL(10, 2) NOT NULL,
    TransactionTime TIMESTAMP NOT NULL,
    FOREIGN KEY (ConsumerId) REFERENCES Consumer(ConsumerId) ON DELETE CASCADE
);
 
CREATE TABLE ProductType (
    TypeId INT AUTO_INCREMENT PRIMARY KEY,
    TypeName VARCHAR(100) NOT NULL,
    ParentId INT NULL,
    Description TEXT,
    CreatedAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
CREATE TABLE Product (
    ProductId INT AUTO_INCREMENT PRIMARY KEY,
    ProductName VARCHAR(255) NOT NULL,
    Price DECIMAL(10, 2) NOT NULL DEFAULT 0.00,
    Stock INT NOT NULL DEFAULT 0,
    TypeId INT NOT NULL,
    CreatedAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (TypeId) REFERENCES ProductType(TypeId) ON DELETE RESTRICT
);
 
CREATE TABLE `Order` (
    `OrderId` INT AUTO_INCREMENT PRIMARY KEY,
    `ConsumerId` INT NOT NULL,
    `OrderStatus` VARCHAR(50) NOT NULL DEFAULT '待支付',
    `OrderTime` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `PaymentStatus` VARCHAR(50) NOT NULL DEFAULT '未支付',
    `PaymentTime` TIMESTAMP NULL,
    `TotalPrice` DECIMAL(10, 2) NOT NULL DEFAULT 0.00,
    FOREIGN KEY (`ConsumerId`) REFERENCES `Consumer`(`ConsumerId`) ON DELETE RESTRICT
);
 
CREATE TABLE OrderInfo (
    OrderInfoId INT AUTO_INCREMENT PRIMARY KEY,
    OrderId INT NOT NULL,
    ProductId INT NOT NULL,
    Quantity INT NOT NULL,
    UnitPrice DECIMAL(10, 2) NOT NULL,
    FOREIGN KEY (OrderId) REFERENCES `Order`(OrderId) ON DELETE CASCADE,
    FOREIGN KEY (ProductId) REFERENCES Product(ProductId) ON DELETE RESTRICT
);