数据库表设计实战：从业务场景推导字段的宠物领养系统完整解析

在后台开发过程中，我们常常面临数据库表设计的核心挑战：表结构该如何规划？字段是否需要添加？哪些信息属于主数据？哪些属于过程数据？许多系统在业务扩张后遇到的瓶颈，往往源于早期数据库设计阶段埋下的隐患。

表设计的关键在于——字段不是凭空臆想的，而是从业务场景中一步步推导得出的。下面通过一个完整的宠物领养流程，演示如何从业务需求自然推导出字段，最终形成可落地的数据库结构。宠物领养系统具备典型架构：用户、资源、申请、审核、结果，非常适合作为案例教学。

1 从业务识别实体：确定独立成表的数据单元

任何系统都离不开三类核心数据：

1. 主体对象：用户、商品、宠物等
2. 行为过程：申请、下单、审批等
3. 最终结果：成功记录、历史归档等

在宠物领养系统中，可以清晰识别出以下实体：

• 用户（User）：申请发起人
• 宠物（Pet）：被申请资源
• 宠物类别（Pet_Category）：结构化主数据
• 领养申请（Adoption）：用户行为过程
• 领养记录（Adoption_Record）：最终事实数据

这五类数据对应五张核心表，构成了系统的基础框架。

2 字段设计前奏：梳理业务流程

宠物领养的完整流程极具代表性：

1. 平台发布宠物信息
2. 用户浏览并提交申请
3. 管理员审核申请
4. 宠物被成功领养并生成记录

每个环节都会产生相应数据，这些数据就是字段的直接来源。设计字段时不应从技术角度出发，而要回答一个核心问题：为了让系统正确运行，此处需要记录什么信息？

E-R图展示：

3 用户表设计：只存储业务必需字段

用户在此系统中承担两个角色：申请发起者和审核联系对象。从业务需求倒推必要字段：

业务需求	必要字段	说明
登录系统	Username, Password	基础账号验证
填写申请	FullName, Age, Address	审核养宠条件
联系用户	PhoneNumber	通知与确认

用户表无需冗余字段（如邮箱、角色等），严格遵循业务要求。

用户表结构

CREATE TABLE User (
    UserID INT PRIMARY KEY,
    Username VARCHAR(50) NOT NULL,
    Password VARCHAR(50) NOT NULL,
    FullName VARCHAR(50) NOT NULL,
    Age INT,
    Address VARCHAR(100),
    PhoneNumber VARCHAR(20)
);

4 宠物类别表：固定选项独立管理

宠物类别属于固定选项数据，例如：Dog、Cat、Bird等。独立成表带来两大优势：

• 避免重复存储
• 便于扩展类别属性

类别表结构

CREATE TABLE Pet_Category (
    CategoryID INT PRIMARY KEY,
    CategoryName VARCHAR(50)
);

5 宠物表：资源数据与状态控制

宠物作为核心资源，其状态决定能否被申请。业务必需信息包括：

• 基础展示：名称、品种、年龄、性别
• 领养状态：是否可领养
• 类别关联：宠物分类

宠物表结构

CREATE TABLE Pet (
    PetID INT PRIMARY KEY,
    Name VARCHAR(50) NOT NULL,
    Breed VARCHAR(50),
    Age INT,
    Gender VARCHAR(10),
    AdoptionStatus VARCHAR(20),
    CategoryID INT,
    FOREIGN KEY (CategoryID) REFERENCES Pet_Category(CategoryID)
);

AdoptionStatus是最关键字段，控制宠物生命周期：Available → Adopted。

6 领养申请表：过程数据完整记录

领养申请属于过程数据，字段需覆盖两个阶段：用户填写信息和管理员审核状态。

需求	字段	说明
记录申请人	UserID	申请人标识
记录目标宠物	PetID	宠物标识
记录申请时间	ApplicationDate	审批排序依据
记录审核状态	ReviewStatus	Pending/Approved/Rejected

申请表结构

CREATE TABLE Adoption (
    ApplicationID INT PRIMARY KEY,
    UserID INT,
    PetID INT,
    ApplicationDate DATE,
    ReviewStatus VARCHAR(20),
    FOREIGN KEY (UserID) REFERENCES User(UserID),
    FOREIGN KEY (PetID) REFERENCES Pet(PetID)
);

7 领养记录表：事实数据独立存储

过程数据可以修改，但结果数据不可覆盖。领养记录必须独立成表，业务需要记录：

• 领养人信息
• 被领养宠物
• 成功时间
• 归还日期（如支持）

领养记录表结构

CREATE TABLE Adoption_Record (
    RecordID INT PRIMARY KEY,
    UserID INT,
    PetID INT,
    AdoptionDate DATE,
    ReturnDate DATE NULL,
    FOREIGN KEY (UserID) REFERENCES User(UserID),
    FOREIGN KEY (PetID) REFERENCES Pet(PetID)
);

8 模型总结与设计思路

宠物领养系统展示了数据库设计的三个核心原则：

1. 主体数据稳定独立 User、Pet、Pet_Category等数据变化频率低，结构长期固定。

2. 过程数据包含状态字段 Adoption表需要表达完整流程，而非仅最终状态。

3. 结果数据单独成表 Adoption_Record存储不可修改的事实数据，与申请过程分离。

这套方法论适用于几乎所有业务系统：报名系统、物品借还、工单审核、内容发布等。

9 适用场景扩展

本设计模型可直接推广到：

• 设备借用系统（设备、用户、申请、借用记录）
• 图书借阅系统（图书、用户、借阅申请、借阅记录）
• 志愿活动报名系统
• 审批类后台系统

只需替换实体名称即可适配不同业务场景。

10 完整MySQL源码实现

以下为宠物领养系统的完整数据库源码，可直接复制运行：

/* 数据库初始化脚本 */
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- 用户表
DROP TABLE IF EXISTS `user`;
CREATE TABLE `user` (
  `UserID` int NOT NULL,
  `Username` varchar(50) NOT NULL,
  `Password` varchar(50) NOT NULL,
  `Address` varchar(100) DEFAULT NULL,
  `PhoneNumber` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`UserID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 宠物类别表
DROP TABLE IF EXISTS `pet_category`;
CREATE TABLE `pet_category` (
  `CategoryID` int NOT NULL,
  `CategoryName` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`CategoryID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 宠物表
DROP TABLE IF EXISTS `pet`;
CREATE TABLE `pet` (
  `PetID` int NOT NULL,
  `Name` varchar(50) NOT NULL,
  `Breed` varchar(50) DEFAULT NULL,
  `Age` int DEFAULT NULL,
  `Gender` varchar(10) DEFAULT NULL,
  `AdoptionStatus` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`PetID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 领养申请表
DROP TABLE IF EXISTS `adoption`;
CREATE TABLE `adoption` (
  `ApplicationID` int NOT NULL,
  `UserID` int DEFAULT NULL,
  `PetID` int DEFAULT NULL,
  `ApplicationDate` date DEFAULT NULL,
  `ReviewStatus` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`ApplicationID`),
  KEY `UserID` (`UserID`),
  KEY `PetID` (`PetID`),
  CONSTRAINT `adoption_ibfk_1` FOREIGN KEY (`UserID`) REFERENCES `user` (`UserID`),
  CONSTRAINT `adoption_ibfk_2` FOREIGN KEY (`PetID`) REFERENCES `pet` (`PetID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 领养记录表
DROP TABLE IF EXISTS `adoption_record`;
CREATE TABLE `adoption_record` (
  `RecordID` int NOT NULL,
  `UserID` int DEFAULT NULL,
  `PetID` int DEFAULT NULL,
  `AdoptionDate` date DEFAULT NULL,
  `ReturnDate` date DEFAULT NULL,
  PRIMARY KEY (`RecordID`),
  KEY `UserID` (`UserID`),
  KEY `PetID` (`PetID`),
  CONSTRAINT `adoption_record_ibfk_1` FOREIGN KEY (`UserID`) REFERENCES `user` (`UserID`),
  CONSTRAINT `adoption_record_ibfk_2` FOREIGN KEY (`PetID`) REFERENCES `pet` (`PetID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 示例数据插入
INSERT INTO `user` VALUES 
(1,'Gu Lu','password1','762 Regent Street','(20) 5828 7700'),
(2,'Amy Ferguson','password2','909 Huaxia St, Jinghua Shangquan','760-266-9029');

INSERT INTO `pet_category` VALUES 
(1,'Dog'),(2,'Cat'),(3,'Bird');

INSERT INTO `pet` VALUES 
(1,'Max','Labrador',2,'M','Available'),
(2,'Bella','Persian Cat',3,'F','Available');

INSERT INTO `adoption` VALUES 
(1,2,1,'2015-07-23','Pending'),
(2,5,3,'2022-05-24','Approved');

INSERT INTO `adoption_record` VALUES 
(1,1,3,'2021-06-23','2023-11-09'),
(2,3,2,'2023-09-01','2023-11-12');

-- 视图：已领养宠物
CREATE VIEW `adoptedpets` AS 
SELECT `pet`.`PetID`, `pet`.`Name`, `pet`.`Breed`, 
       `adoption_record`.`AdoptionDate`, `adoption_record`.`ReturnDate` 
FROM (`pet` JOIN `adoption_record` ON (`pet`.`PetID` = `adoption_record`.`PetID`)) 
WHERE (`adoption_record`.`ReturnDate` IS NULL);

-- 存储过程：获取用户领养记录
DELIMITER ;;
CREATE PROCEDURE `GetAdoptionRecords`(IN p_UserID INT)
BEGIN
    SELECT * FROM adoption_record WHERE UserID = p_UserID;
END;;
DELIMITER ;

SET FOREIGN_KEY_CHECKS = 1;