本文目录导读：
我来帮你讲解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加密
- 敏感数据加密存储
- 实施访问控制
需要我详细解释某个特定部分吗？比如如何优化查询性能,或者如何处理特定类型的数据库关系？
