PHP项目数据库设计与ER图

本文目录导读：

1. 数据库设计基本原则
2. 典型PHP项目ER图示例
3. 数据库表结构设计SQL
4. PHP代码集成示例
5. 设计最佳实践

我来帮你讲解PHP项目数据库设计的核心要点,并演示如何创建ER图。

数据库设计基本原则

规范化原则

• 第一范式(1NF): 每列都是不可分割的原子数据项
• 第二范式(2NF): 非主键列完全依赖于主键
• 第三范式(3NF): 非主键列不传递依赖于主键

命名规范

-- 表名：使用复数，小写
users, orders, products
-- 字段：使用小写和下划线
user_id, created_at, is_active
-- 主键：通常使用 id
id INT PRIMARY KEY AUTO_INCREMENT
-- 外键：关联表名_主键
user_id, order_id

典型PHP项目ER图示例

用户管理系统

erDiagram
    User ||--o{ Order : has
    User ||--o{ UserProfile : has
    User ||--o{ UserRole : belongs_to
    Role ||--o{ UserRole : contains
    Role ||--o{ RolePermission : has
    Permission ||--o{ RolePermission : assigned_to
    Order ||--o{ OrderItem : contains
    Product ||--o{ OrderItem : appears_in
    Category ||--o{ Product : categorizes
    User {
        int id PK
        string username
        string email
        string password_hash
        string status
        datetime created_at
        datetime updated_at
        datetime last_login
    }
    UserProfile {
        int id PK
        int user_id FK
        string first_name
        string last_name
        string phone
        string avatar_url
        date birth_date
        string gender
        text bio
    }
    Role {
        int id PK
        string name
        string description
        datetime created_at
    }
    UserRole {
        int user_id FK
        int role_id FK
    }
    Permission {
        int id PK
        string name
        string slug
        string module
    }
    RolePermission {
        int role_id FK
        int permission_id FK
    }
    Order {
        int id PK
        int user_id FK
        string order_number
        decimal total_amount
        string status
        string shipping_address
        string payment_method
        datetime created_at
        datetime paid_at
    }
    OrderItem {
        int id PK
        int order_id FK
        int product_id FK
        int quantity
        decimal unit_price
        decimal subtotal
    }
    Product {
        int id PK
        int category_id FK
        string name
        string sku
        text description
        decimal price
        int stock_quantity
        string status
        datetime created_at
    }
    Category {
        int id PK
        int parent_id FK
        string name
        string slug
        text description
        int sort_order
    }

数据库表结构设计SQL

建表SQL示例

-- 用户表
CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50) UNIQUE NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    status ENUM('active', 'inactive', 'banned') DEFAULT 'active',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    last_login TIMESTAMP NULL,
    INDEX idx_email (email),
    INDEX idx_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
-- 用户资料表
CREATE TABLE user_profiles (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT UNIQUE NOT NULL,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    phone VARCHAR(20),
    avatar_url VARCHAR(255),
    birth_date DATE,
    gender ENUM('male', 'female', 'other'),
    bio TEXT,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
-- 角色表
CREATE TABLE roles (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50) UNIQUE NOT NULL,
    description TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
-- 权限表
CREATE TABLE permissions (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) UNIQUE NOT NULL,
    slug VARCHAR(100) UNIQUE NOT NULL,
    module VARCHAR(50) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
-- 用户角色关联表
CREATE TABLE user_roles (
    user_id INT NOT NULL,
    role_id INT NOT NULL,
    PRIMARY KEY (user_id, role_id),
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (role_id) REFERENCES roles(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
-- 角色权限关联表
CREATE TABLE role_permissions (
    role_id INT NOT NULL,
    permission_id INT NOT NULL,
    PRIMARY KEY (role_id, permission_id),
    FOREIGN KEY (role_id) REFERENCES roles(id) ON DELETE CASCADE,
    FOREIGN KEY (permission_id) REFERENCES permissions(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
-- 产品分类表
CREATE TABLE categories (
    id INT PRIMARY KEY AUTO_INCREMENT,
    parent_id INT NULL,
    name VARCHAR(100) NOT NULL,
    slug VARCHAR(100) UNIQUE NOT NULL,
    description TEXT,
    sort_order INT DEFAULT 0,
    FOREIGN KEY (parent_id) REFERENCES categories(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
-- 产品表
CREATE TABLE products (
    id INT PRIMARY KEY AUTO_INCREMENT,
    category_id INT,
    name VARCHAR(200) NOT NULL,
    sku VARCHAR(50) UNIQUE NOT NULL,
    description TEXT,
    price DECIMAL(10, 2) NOT NULL,
    stock_quantity INT DEFAULT 0,
    status ENUM('active', 'inactive', 'out_of_stock') DEFAULT 'active',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (category_id) REFERENCES categories(id) ON DELETE SET NULL,
    INDEX idx_category (category_id),
    INDEX idx_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
-- 订单表
CREATE TABLE orders (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NOT NULL,
    order_number VARCHAR(50) UNIQUE NOT NULL,
    total_amount DECIMAL(12, 2) NOT NULL,
    status ENUM('pending', 'processing', 'shipped', 'delivered', 'cancelled') DEFAULT 'pending',
    shipping_address TEXT,
    payment_method VARCHAR(50),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    paid_at TIMESTAMP NULL,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE RESTRICT,
    INDEX idx_user (user_id),
    INDEX idx_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
-- 订单项表
CREATE TABLE order_items (
    id INT PRIMARY KEY AUTO_INCREMENT,
    order_id INT NOT NULL,
    product_id INT NOT NULL,
    quantity INT NOT NULL,
    unit_price DECIMAL(10, 2) NOT NULL,
    subtotal DECIMAL(12, 2) NOT NULL,
    FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE,
    FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE RESTRICT,
    INDEX idx_order (order_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

PHP代码集成示例

数据库连接类

<?php
class Database {
    private $host = "localhost";
    private $db_name = "your_database";
    private $username = "root";
    private $password = "";
    public $conn;
    public function getConnection() {
        $this->conn = null;
        try {
            $this->conn = new PDO(
                "mysql:host=" . $this->host . ";dbname=" . $this->db_name,
                $this->username,
                $this->password
            );
            $this->conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
        } catch(PDOException $e) {
            echo "Connection error: " . $e->getMessage();
        }
        return $this->conn;
    }
}
?>

用户模型示例

<?php
class User {
    private $conn;
    private $table = "users";
    public $id;
    public $username;
    public $email;
    public $password_hash;
    public function __construct($db) {
        $this->conn = $db;
    }
    public function create() {
        $query = "INSERT INTO " . $this->table . "
                  SET username=:username, email=:email, password_hash=:password_hash";
        $stmt = $this->conn->prepare($query);
        $stmt->bindParam(":username", $this->username);
        $stmt->bindParam(":email", $this->email);
        $stmt->bindParam(":password_hash", $this->password_hash);
        if($stmt->execute()) {
            return true;
        }
        return false;
    }
    public function read() {
        $query = "SELECT * FROM " . $this->table;
        $stmt = $this->conn->prepare($query);
        $stmt->execute();
        return $stmt;
    }
}
?>

设计最佳实践

字段类型选择

• 整数: INT, BIGINT
• 小数: DECIMAL(10,2)
• 字符串: VARCHAR(255)
• 文本: TEXT, LONGTEXT
• 日期: DATE, DATETIME, TIMESTAMP
• 布尔: TINYINT(1)
• 枚举: ENUM

索引优化

• 为主键自动创建索引
• 为经常查询的字段创建索引
• 避免过度索引
• 使用复合索引优化多条件查询

性能优化

• 使用批量插入
• 合理使用缓存
• 避免SELECT *
• 使用JOIN代替子查询

安全考虑

• 使用预处理语句防止SQL注入
• 密码使用bcrypt加密
• 敏感数据加密存储
• 实施访问控制

需要我详细解释某个特定部分吗？比如如何优化查询性能,或者如何处理特定类型的数据库关系？