#数据库设计

-- 不符合 1NF
CREATE TABLE users (
    id INT,
    name VARCHAR(50),
    phones VARCHAR(200)  -- '13800138000,13900139000'
);

-- 符合 1NF
CREATE TABLE users (
    id INT,
    name VARCHAR(50)
);
CREATE TABLE user_phones (
    user_id INT,
    phone VARCHAR(20)
);

-- 表名：复数、小写、下划线
users, orders, order_items

-- 列名：小写、下划线
user_id, created_at, total_amount

-- 索引名：idx_表名_列名
idx_users_email, idx_orders_user_id

-- 使用合适的数据类型
id INT UNSIGNED AUTO_INCREMENT,
price DECIMAL(10,2),  -- 不用 FLOAT
status ENUM('active', 'inactive'),
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP

-- 用户表
CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50) UNIQUE NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 商品表
CREATE TABLE products (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    price DECIMAL(10,2) NOT NULL,
    stock INT DEFAULT 0
);

-- 订单表
CREATE TABLE orders (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NOT NULL,
    total_amount DECIMAL(10,2),
    status ENUM('pending', 'paid', 'shipped', 'delivered'),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id)
);

-- 订单项表
CREATE TABLE order_items (
    id INT PRIMARY KEY AUTO_INCREMENT,
    order_id INT NOT NULL,
    product_id INT NOT NULL,
    quantity INT NOT NULL,
    price DECIMAL(10,2) NOT NULL,
    FOREIGN KEY (order_id) REFERENCES orders(id),
    FOREIGN KEY (product_id) REFERENCES products(id)
);