数据库课设——校园失物招领平台（阶段二 系统设计）

以下是针对校园失物招领平台系统设计第二阶段的详细拓展，包括系统功能设计和数据库设计的各个子模块，以及如何进行每个部分的操作：

---

2. 系统设计

以下是针对校园失物招领平台系统功能设计中的各个模块，进行更为详细和完整的拓展描述： 

---

2.1系统架构设计

在校园失物招领平台的数据库课设中，实现三层架构设计需要明确各层的职责，并通过代码展示如何将设计转化为实际实现。

以下是各层的详细实现和分析，包括用户界面层（表示层）、业务逻辑层、数据访问层和数据库层。

2.1.1.用户界面层（表示层）

功能：负责与用户交互，展示信息和接收用户输入。

技术选择：使用Qt框架来创建GUI界面。

c++代码：

#include <QApplication>
#include <QWidget>
#include <QPushButton>
#include <QLabel>
#include <QLineEdit>
#include <QVBoxLayout>


class LoginWindow : public QWidget {
public:
LoginWindow(QWidget *parent = nullptr) : QWidget(parent) {
// 创建UI组件
QLabel *usernameLabel = new QLabel("Username:", this);
QLineEdit *usernameEdit = new QLineEdit(this);
QLabel *passwordLabel = new QLabel("Password:", this);
QLineEdit *passwordEdit = new QLineEdit(this);
passwordEdit->setEchoMode(QLineEdit::Password);
QPushButton *loginButton = new QPushButton("Login", this);


// 布局
QVBoxLayout *layout = new QVBoxLayout(this);
layout->addWidget(usernameLabel);
layout->addWidget(usernameEdit);
layout->addWidget(passwordLabel);
layout->addWidget(passwordEdit);
layout->addWidget(loginButton);


// 连接信号和槽
connect(loginButton, &QPushButton::clicked, this, [usernameEdit, passwordEdit]() {
QString username = usernameEdit->text();
QString password = passwordEdit->text();
// 调用业务逻辑层进行登录验证
if (UserService::login(username, password)) {
// 登录成功，打开主界面
} else {
// 登录失败，提示用户
}
});
}
};


int main(int argc, char *argv[]) {
QApplication app(argc, argv);
LoginWindow loginWindow;
loginWindow.show();
return app.exec();
}

设计要点：

• 使用Qt组件创建直观、易用的界面。
• 通过信号和槽机制与业务逻辑层交互，保持界面与业务逻辑解耦。

2.1.2.业务逻辑层

功能：处理核心业务逻辑，如用户注册、失物发布、认领审核、反馈处理等。

技术选择：使用C++进行开发。

c++代码：

#include <string>
#include <vector>


class UserService {
public:
static bool login(const std::string &username, const std::string &password) {
// 调用数据访问层验证用户
return DataAccess::validateUser(username, password);
}


static bool registerUser(const std::string &username, const std::string &password, const std::string &contactInfo) {
// 调用数据访问层注册用户
return DataAccess::registerUser(username, password, contactInfo);
}
};


class LostItemService {
public:
static bool postLostItem(int userId, const std::string &itemName, const std::string &description, const std::string &foundDate, const std::string &foundLocation) {
// 调用数据访问层发布失物信息
return DataAccess::postLostItem(userId, itemName, description, foundDate, foundLocation);
}
};

设计要点：

• 封装业务逻辑，确保业务规则的集中管理。
• 调用数据访问层接口进行数据库操作，处理业务异常情况。

2.1.3.数据访问层

功能：负责与数据库交互，封装所有数据库操作，提供数据持久化的接口。

技术选择：使用C++结合MySQL Connector/C++。

c++代码：

#include <mysql_connection.h>
#include <mysql_driver.h>
#include <cppconn/statement.h>
#include <cppconn/prepared_statement.h>
#include <string>


class DataAccess {
public:
static bool validateUser(const std::string &username, const std::string &password) {
sql::mysql::MySQL_Driver *driver;
sql::Connection *con;
sql::PreparedStatement *pstmt;


driver = sql::mysql::get_mysql_driver_instance();
con = driver->connect("tcp://127.0.0.1:3306", "username", "password");
con->setSchema("lost_and_found");


pstmt = con->prepareStatement("SELECT * FROM User WHERE Username = ? AND Password = ?");
pstmt->setString(1, username);
pstmt->setString(2, password);


sql::ResultSet *res = pstmt->executeQuery();
bool isValid = res->next();


delete res;
delete pstmt;
delete con;


return isValid;
}


static bool registerUser(const std::string &username, const std::string &password, const std::string &contactInfo) {
sql::mysql::MySQL_Driver *driver;
sql::Connection *con;
sql::PreparedStatement *pstmt;


driver = sql::mysql::get_mysql_driver_instance();
con = driver->connect("tcp://127.0.0.1:3306", "username", "password");
con->setSchema("lost_and_found");


pstmt = con->prepareStatement("INSERT INTO User (Username, Password, ContactInfo) VALUES (?, ?, ?)");
pstmt->setString(1, username);
pstmt->setString(2, password);
pstmt->setString(3, contactInfo);


bool isSuccess = pstmt->executeUpdate() > 0;


delete pstmt;
delete con;


return isSuccess;
}
};

设计要点：

• 封装数据库操作，提供简洁的接口供业务逻辑层调用。
• 处理数据库连接管理、事务管理、错误处理等。

2.1.4.数据库层

功能：存储和管理所有业务数据，提供数据持久化支持。

技术选择：使用MySQL数据库管理系统。

数据库表结构示例：

sql

CREATE TABLE User (
UserID INT AUTO_INCREMENT PRIMARY KEY,
Username VARCHAR(255) UNIQUE NOT NULL,
Password VARCHAR(255) NOT NULL,
ContactInfo VARCHAR(255),
Role VARCHAR(50) DEFAULT '普通用户',
RegistrationDate DATETIME DEFAULT CURRENT_TIMESTAMP,
LastLoginTime DATETIME
);


CREATE TABLE LostItem (
ItemID INT AUTO_INCREMENT PRIMARY KEY,
ItemName VARCHAR(255) NOT NULL,
Description TEXT,
FoundDate DATE NOT NULL,
FoundLocation VARCHAR(255) NOT NULL,
Status VARCHAR(50) DEFAULT '待认领',
ImageURL VARCHAR(255),
FinderID INT,
CategoryID INT,
ExpiryDate DATE,
FOREIGN KEY (FinderID) REFERENCES User(UserID),
FOREIGN KEY (CategoryID) REFERENCES Category(CategoryID)
);


-- 其他表结构类似，根据需求分析进行设计

设计要点：

• 根据需求分析设计数据库表结构，包括用户信息、失物信息、认领信息、失物类别、用户反馈、系统日志、用户偏好、通知等表。
• 确保数据库的完整性和一致性，使用外键、索引等优化查询性能。

通过上述代码和设计要点，可以清晰地看到如何在校园失物招领平台中实现三层架构设计。

用户界面层负责与用户交互，业务逻辑层处理核心业务逻辑，数据访问层封装数据库操作，数据库层负责数据持久化，便于维护和扩展。

---

ps：使用工具mermaid

---

2.2 系统功能设计

系统功能设计旨在明确系统应具备哪些功能模块，以满足用户需求，并确保系统的易用性、可维护性和可扩展性。以下是针对校园失物招领平台的详细功能模块划分，这些功能模块将与数据库设计紧密结合，实现数据的存储、查询、更新等操作。

---

2.2.1. 用户管理模块

• 用户注册/登录
  • 功能描述：
    • 用户注册：提供用户注册功能，用户需填写用户名、密码、联系方式（如手机号、邮箱）等基本信息，并可能需要进行验证码验证或邮箱/手机验证以确保用户身份的真实性。注册信息将存储在数据库的User表中。
    • 用户登录：支持用户名和密码登录，可能还包括第三方登录方式（如微信、QQ登录），提升用户体验。登录时系统会从User表中验证用户名和密码。
    • 密码找回：提供密码找回功能，用户可通过注册时绑定的手机号或邮箱找回密码，系统会更新User表中的密码字段。
• 用户信息管理
  • 功能描述：
    • 查看个人信息：用户登录后，可从数据库的User表中读取自己的个人信息，包括用户名、联系方式、注册时间等，并展示给用户。
    • 修改个人信息：用户可修改自己的联系方式、密码等个人信息，修改后的信息将更新到User表中。
    • 个人头像设置：用户可上传或修改个人头像，头像信息可存储在数据库的特定字段（如User表中可新增一个字段存储头像路径）或文件系统中，数据库中记录头像的存储路径。

2.2.2. 失物信息管理模块

• 发布失物信息
  • 功能描述：
    • 填写失物详情：用户可填写失物名称、详细描述、发现日期、发现地点、失物图片等信息，以便其他用户更好地了解失物情况。这些信息将存储在数据库的LostItem表中。
    • 选择失物类别：提供失物类别选择（如书籍、电子产品、衣物等），类别信息存储在Category表中，选择类别时将关联到Category表中的CategoryID。
    • 提交发布：用户确认信息无误后，系统将失物信息插入到LostItem表中。
• 浏览失物信息
  • 功能描述：
    • 待认领失物列表：从数据库的LostItem表中筛选出状态为“待认领”的失物信息，包括失物名称、图片、发现地点等简要信息，展示给用户。
    • 筛选与排序：支持按时间、类型、发现地点等条件对LostItem表中的数据进行筛选和排序，方便用户快速找到感兴趣的失物。
    • 查看详细信息：用户可点击失物信息，从LostItem表中读取详细描述、图片、发布者信息（从User表中关联查询）等，展示给用户。
• 修改/删除失物信息
  • 功能描述：
    • 修改失物信息：失物发布者可在失物未被认领前，修改LostItem表中失物名称、描述、图片等信息。
    • 删除失物信息：失物发布者可在失物未被认领前，从LostItem表中删除失物信息。若失物已被认领，则禁止删除，以确保LostItem表数据的完整性和可追溯性。

2.2.3. 认领管理模块

• 认领失物
  • 功能描述：
    • 提交认领申请：用户可对待认领的失物提交认领申请，将认领者ID（从User表中获取）、失物ID（从LostItem表中获取）、认领理由、联系方式等信息插入到Claim表中。
    • 上传凭证（可选）：对于某些高价值失物，系统可要求用户上传相关凭证（如购买发票、照片等），凭证信息可存储在数据库的特定字段或文件系统中，并在Claim表中记录凭证的存储路径。
• 认领审核
  • 功能描述：
    • 系统自动审核（可选）：对于部分简单认领申请，系统可根据预设规则进行自动审核，如认领理由是否合理、联系方式是否有效等，审核逻辑可基于Claim表中的数据进行判断。
    • 管理员审核：对于复杂或高价值失物的认领申请，需由管理员进行人工审核。管理员可查看Claim表中的认领者信息、LostItem表中的失物详情、认领理由等，并更新Claim表中的认领状态（“通过”或“拒绝”）。
    • 更新失物状态：审核通过后，系统自动将LostItem表中的失物状态更新为“已认领”，并通过通知功能（后续可扩展）通知认领者和失物发布者。
• 认领记录查询
  • 功能描述：
    • 查看认领记录：用户可从Claim表中查询自己的认领记录，包括认领的失物名称、认领状态、认领日期等信息。
    • 认领状态跟踪：用户可实时跟踪Claim表中的认领状态，如“待审核”、“已通过”、“已拒绝”等。

2.2.4. 系统管理模块

• 用户权限管理
  • 功能描述：
    • 角色管理：管理员可在数据库中创建、修改和删除用户角色（存储在Role表，若后续扩展角色表），如“普通用户”、“管理员”等，并为每个角色分配不同的权限（存储在权限表中，可与角色表关联）。
    • 权限分配：管理员可为每个用户分配角色，从而控制用户对系统功能的访问权限。例如，禁止某些用户发布失物信息或审核认领申请，通过更新用户与角色的关联关系实现。
• 数据统计与分析
  • 功能描述：
    • 失物发布数量统计：统计不同时间段内的失物发布数量，分析失物发布趋势。数据来源于LostItem表，通过查询该表并按时间分组统计实现。
    • 认领成功率统计：统计认领申请的成功率，分析认领成功率的影响因素。数据来源于Claim表，通过统计认领状态为“已通过”的数量与总认领申请数量的比例实现。
    • 数据可视化：提供图表、报表等可视化工具，帮助管理员直观地了解系统运行情况和用户行为，数据基于数据库中的统计结果生成。
• 系统日志管理
  • 功能描述：
    • 记录操作日志：系统自动将用户的操作日志，包括登录、发布失物、认领失物、审核认领申请等，插入到SystemLog表中。
    • 日志查询与导出：管理员可查询和导出SystemLog表中的系统日志，以便进行审计和故障排查。
• 系统设置与维护
  • 功能描述：
    • 系统参数设置：管理员可设置系统参数，如失物保留期限（影响LostItem表中失物状态的更新逻辑）、认领审核时间等，参数存储在数据库的特定表中（如SystemConfig表，可根据实际需求设计）。
    • 数据备份与恢复：定期备份系统数据，确保数据安全。在数据丢失或损坏时，可进行数据恢复，备份和恢复操作针对数据库进行。

---

2.3数据库设计

为了完整地完成校园失物招领平台的数据库设计部分，我细化数据库需求分析、概念结构设计（E-R图设计）的内容，包括详细的数据项列表、数据关系分析以及E-R图的描述。

2.3.1 数据库需求分析

列出数据项：

用户信息（User）

• 用户ID（UserID）：唯一标识用户的ID，自增主键。
• 用户名（Username）：用户登录时使用的名称，唯一。
• 密码（Password）：用户登录时使用的密码，加密存储。
• 联系方式（ContactInfo）：用户的联系方式，如手机号或邮箱。
• 角色（Role）：用户角色，如“普通用户”、“管理员”，默认值为“普通用户”。
• 注册时间（RegistrationDate）：用户注册的时间。
• 最后登录时间（LastLoginTime）：用户最后一次登录系统的时间。

失物信息（LostItem）

• 失物ID（ItemID）：唯一标识失物的ID，自增主键。
• 失物名称（ItemName）：失物的名称。
• 描述（Description）：失物的详细描述。
• 发现日期（FoundDate）：失物被发现的日期。
• 发现地点（FoundLocation）：失物被发现的地点。
• 状态（Status）：失物的当前状态，如“待认领”、“已认领”、“已过期”，默认值为“待认领”。
• 图片URL（ImageURL）：失物图片的存储路径或URL（可选）。
• 发布者ID（FinderID）：发布该失物的用户ID，关联用户表。
• 失物类别ID（CategoryID）：失物所属的类别ID，关联失物类别表。
• 失物过期日期（ExpiryDate）：失物过期日期（可选）。

认领信息（Claim）

• 认领ID（ClaimID）：唯一标识认领记录的ID，自增主键。
• 失物ID（ItemID）：被认领的失物ID，关联失物表。
• 认领者ID（ClaimerID）：提交认领申请的用户ID，关联用户表。
• 认领日期（ClaimDate）：提交认领申请的日期。
• 认领状态（ClaimStatus）：认领申请的状态，如“待审核”、“已通过”、“已拒绝”，默认值为“待审核”。
• 审核意见（ReviewComment）：管理员对认领申请的审核意见（可选）。
• 审核日期（ReviewDate）：审核日期（可选）。

失物类别（Category）

• 类别ID（CategoryID）：唯一标识失物类别的ID，自增主键。
• 类别名称（CategoryName）：失物类别的名称，如“书籍”、“电子产品”、“衣物”等，唯一。

用户反馈（Feedback）

• 反馈ID（FeedbackID）：唯一标识用户反馈的ID，自增主键。
• 用户ID（UserID）：提交反馈的用户ID，关联用户表。
• 失物ID（LostItemID）：反馈所针对的失物ID（可选），关联失物表。
• 反馈内容（FeedbackContent）：用户提交的反馈内容。
• 反馈日期（FeedbackDate）：用户提交反馈的日期。

系统日志（SystemLog）

• 日志ID（LogID）：唯一标识系统日志的ID，自增主键。
• 操作类型（OperationType）：操作类型，如“用户注册”、“失物发布”、“认领审核”等。
• 操作时间（OperationTime）：操作发生的时间。
• 操作者ID（OperatorID）：执行操作的用户ID（可选），关联用户表。
• 操作详情（Details）：操作的详细信息（可选）。

用户偏好（UserPreference）

• 偏好ID（PreferenceID）：唯一标识用户偏好的ID，自增主键。
• 用户ID（UserID）：关联的用户ID，关联用户表。
• 是否接收通知（NotificationPreference）：布尔值，默认值为TRUE。
• 语言偏好（LanguagePreference）：语言设置，默认值为“zh-CN”。
• 主题偏好（ThemePreference）：主题设置，如浅色/深色，默认值为“light”。

通知（Notification）

• 通知ID（NotificationID）：唯一标识通知的ID，自增主键。
• 用户ID（UserID）：接收通知的用户ID，关联用户表。
• 通知标题（Title）：通知的标题。
• 通知内容（Content）：通知的详细内容。
• 发送日期（SendDate）：通知发送的日期。
• 是否已读（IsRead）：布尔值，默认值为FALSE。

分析数据关系：

• 用户与失物信息：一对多关系。一个用户可以发布多个失物信息，但每个失物信息只能由一个用户发布。
• 失物信息与认领信息：一对多关系。一条失物信息可以被多个用户认领申请，但最终只能被一个用户成功认领。
• 失物信息与失物类别：多对一关系。多个失物信息可以属于同一个失物类别，但每个失物信息只能属于一个失物类别。
• 用户与认领信息：多对多关系（通过认领表关联）。一个用户可以提交多个认领申请，一个失物信息也可以被多个用户认领申请（但最终只能被一个用户成功认领）。
• 用户与用户反馈：一对多关系。一个用户可以提交多个反馈，但每个反馈只能由一个用户提交。
• 系统日志与用户：多对一关系（可选）。多个系统日志可以关联到同一个用户（如果操作是由用户触发的），但每个系统日志只能关联到一个用户。
• 用户与用户偏好：一对多关系（但实际设计中为一一对应，因为每个用户只有一个偏好设置）。
• 用户与通知：一对多关系。一个用户可以接收多个通知，但每个通知只能发送给一个用户。

2.3.2 数据库概念结构设计（E-R图设计）

实体定义：

用户（User）

• 属性：UserID（主键）、Username、Password、ContactInfo、Role、RegistrationDate、LastLoginTime

失物（LostItem）

• 属性：ItemID（主键）、ItemName、Description、FoundDate、FoundLocation、Status、ImageURL、FinderID（外键）、CategoryID（外键）、ExpiryDate

认领（Claim）

• 属性：ClaimID（主键）、ItemID（外键）、ClaimerID（外键）、ClaimDate、ClaimStatus、ReviewComment、ReviewDate

失物类别（Category）

• 属性：CategoryID（主键）、CategoryName

用户反馈（Feedback）

• 属性：FeedbackID（主键）、UserID（外键）、LostItemID（外键）、FeedbackContent、FeedbackDate

系统日志（SystemLog）

• 属性：LogID（主键）、OperationType、OperationTime、OperatorID（外键）、Details

用户偏好（UserPreference）

• 属性：PreferenceID（主键）、UserID（外键）、NotificationPreference、LanguagePreference、ThemePreference

通知（Notification）

• 属性：NotificationID（主键）、UserID（外键）、Title、Content、SendDate、IsRead

关系定义：

• 用户发布失物（Publish）：
  • 用户（User）与失物（LostItem）之间的一对多关系。
  • 用户（User）发布失物（LostItem），失物（LostItem）的FinderID指向用户（User）的UserID。
• 用户认领失物（Claim）：
  • 用户（User）与认领（Claim）之间通过认领表关联的多对多关系（实际设计时通过外键实现一对多关联，但业务逻辑上用户可对多个失物发起认领，失物可被多个用户认领申请）。
  • 用户（User）可以提交多个认领（Claim），认领（Claim）的ClaimerID指向用户（User）的UserID。
  • 失物（LostItem）可被多个认领（Claim）关联，认领（Claim）的ItemID指向失物（LostItem）的ItemID。
• 失物属于类别（BelongTo）：
  • 失物（LostItem）与失物类别（Category）之间的多对一关系。
  • 失物（LostItem）的CategoryID指向失物类别（Category）的CategoryID。
• 用户提交反馈（Submit）：
  • 用户（User）与用户反馈（Feedback）之间的一对多关系。
  • 用户（User）可以提交多个反馈（Feedback），反馈（Feedback）的UserID指向用户（User）的UserID。
• 系统记录日志（Log）：
  • 用户（User）与系统日志（SystemLog）之间的多对一关系（可选）。
  • 系统日志（SystemLog）的OperatorID指向用户（User）的UserID（如果操作是由用户触发的）。
• 用户设置偏好（SetPreference）：
  • 用户（User）与用户偏好（UserPreference）之间的一对一关系（实际设计时通过外键实现，因为每个用户只有一个偏好设置）。
  • 用户偏好（UserPreference）的UserID指向用户（User）的UserID。
• 用户接收通知（ReceiveNotification）：
  • 用户（User）与通知（Notification）之间的一对多关系。
  • 用户（User）可以接收多个通知（Notification），通知（Notification）的UserID指向用户（User）的UserID。

---

使用工具：mysql workbench

具体的SQL实现：

use `database course`;

-- 用户表
CREATE TABLE `t_user` (
  `user_id` BIGINT NOT NULL AUTO_INCREMENT COMMENT '用户ID，自增主键',
  `username` VARCHAR(50) NOT NULL COMMENT '用户名，唯一',
  `password` VARCHAR(255) NOT NULL COMMENT '密码，加密存储',
  `contact_info` VARCHAR(100) DEFAULT NULL COMMENT '联系方式，如手机号或邮箱',
  `role` VARCHAR(20) NOT NULL DEFAULT '普通用户' COMMENT '用户角色，如"普通用户"、"管理员"',
  `registration_date` TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '注册时间',
  `last_login_time` TIMESTAMP NULL DEFAULT NULL COMMENT '最后登录时间',
  `create_time` TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_time` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  `create_user` VARCHAR(50) NULL DEFAULT NULL COMMENT '创建用户',
  `update_user` VARCHAR(50) NULL DEFAULT NULL COMMENT '更新用户',
  PRIMARY KEY (`user_id`),
  UNIQUE KEY `idx_username` (`username`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户表';

-- 失物类别表
CREATE TABLE `t_category` (
  `category_id` BIGINT NOT NULL AUTO_INCREMENT COMMENT '类别ID，自增主键',
  `category_name` VARCHAR(50) NOT NULL COMMENT '类别名称，如"书籍"、"电子产品"、"衣物"等，唯一',
  `create_time` TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_time` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  `create_user` VARCHAR(50) NULL DEFAULT NULL COMMENT '创建用户',
  `update_user` VARCHAR(50) NULL DEFAULT NULL COMMENT '更新用户',
  PRIMARY KEY (`category_id`),
  UNIQUE KEY `idx_category_name` (`category_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='失物类别表';

-- 失物表
CREATE TABLE `t_lost_item` (
  `item_id` BIGINT NOT NULL AUTO_INCREMENT COMMENT '失物ID，自增主键',
  `item_name` VARCHAR(100) NOT NULL COMMENT '失物名称',
  `description` TEXT DEFAULT NULL COMMENT '失物的详细描述',
  `found_date` DATE NOT NULL COMMENT '发现日期',
  `found_location` VARCHAR(255) NOT NULL COMMENT '发现地点',
  `status` VARCHAR(20) NOT NULL DEFAULT '待认领' COMMENT '状态，如"待认领"、"已认领"、"已过期"',
  `image_url` VARCHAR(255) DEFAULT NULL COMMENT '图片URL，失物图片的存储路径或URL',
  `finder_id` BIGINT NOT NULL COMMENT '发布者ID，关联用户表',
  `category_id` BIGINT NOT NULL COMMENT '失物类别ID，关联失物类别表',
  `expiry_date` DATE DEFAULT NULL COMMENT '失物过期日期',
  `create_time` TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_time` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  `create_user` VARCHAR(50) NULL DEFAULT NULL COMMENT '创建用户',
  `update_user` VARCHAR(50) NULL DEFAULT NULL COMMENT '更新用户',
  PRIMARY KEY (`item_id`),
  KEY `idx_finder_id` (`finder_id`),
  KEY `idx_category_id` (`category_id`),
  CONSTRAINT `fk_lost_item_user` FOREIGN KEY (`finder_id`) REFERENCES `t_user` (`user_id`) ON DELETE CASCADE,
  CONSTRAINT `fk_lost_item_category` FOREIGN KEY (`category_id`) REFERENCES `t_category` (`category_id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='失物信息表';

-- 认领表
CREATE TABLE `t_claim` (
  `claim_id` BIGINT NOT NULL AUTO_INCREMENT COMMENT '认领ID，自增主键',
  `item_id` BIGINT NOT NULL COMMENT '被认领的失物ID，关联失物表',
  `claimer_id` BIGINT NOT NULL COMMENT '认领者ID，关联用户表',
  `claim_date` TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '认领日期',
  `claim_status` VARCHAR(20) NOT NULL DEFAULT '待审核' COMMENT '认领状态，如"待审核"、"已通过"、"已拒绝"',
  `review_comment` TEXT DEFAULT NULL COMMENT '审核意见',
  `review_date` TIMESTAMP NULL DEFAULT NULL COMMENT '审核日期',
  `create_time` TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_time` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  `create_user` VARCHAR(50) NULL DEFAULT NULL COMMENT '创建用户',
  `update_user` VARCHAR(50) NULL DEFAULT NULL COMMENT '更新用户',
  PRIMARY KEY (`claim_id`),
  KEY `idx_item_id` (`item_id`),
  KEY `idx_claimer_id` (`claimer_id`),
  CONSTRAINT `fk_claim_item` FOREIGN KEY (`item_id`) REFERENCES `t_lost_item` (`item_id`) ON DELETE CASCADE,
  CONSTRAINT `fk_claim_user` FOREIGN KEY (`claimer_id`) REFERENCES `t_user` (`user_id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='认领信息表';

-- 用户反馈表
CREATE TABLE `t_feedback` (
  `feedback_id` BIGINT NOT NULL AUTO_INCREMENT COMMENT '反馈ID，自增主键',
  `user_id` BIGINT NOT NULL COMMENT '提交反馈的用户ID，关联用户表',
  `lost_item_id` BIGINT DEFAULT NULL COMMENT '反馈所针对的失物ID，关联失物表',
  `feedback_content` TEXT NOT NULL COMMENT '用户提交的反馈内容',
  `feedback_date` TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '反馈日期',
  `create_time` TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_time` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  `create_user` VARCHAR(50) NULL DEFAULT NULL COMMENT '创建用户',
  `update_user` VARCHAR(50) NULL DEFAULT NULL COMMENT '更新用户',
  PRIMARY KEY (`feedback_id`),
  KEY `idx_user_id` (`user_id`),
  KEY `idx_lost_item_id` (`lost_item_id`),
  CONSTRAINT `fk_feedback_user` FOREIGN KEY (`user_id`) REFERENCES `t_user` (`user_id`) ON DELETE CASCADE,
  CONSTRAINT `fk_feedback_item` FOREIGN KEY (`lost_item_id`) REFERENCES `t_lost_item` (`item_id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户反馈表';

-- 系统日志表
CREATE TABLE `t_system_log` (
  `log_id` BIGINT NOT NULL AUTO_INCREMENT COMMENT '日志ID，自增主键',
  `operation_type` VARCHAR(50) NOT NULL COMMENT '操作类型，如"用户注册"、"失物发布"、"认领审核"等',
  `operation_time` TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '操作发生的时间',
  `operator_id` BIGINT DEFAULT NULL COMMENT '执行操作的用户ID，关联用户表',
  `details` TEXT DEFAULT NULL COMMENT '操作的详细信息',
  `create_time` TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_time` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  `create_user` VARCHAR(50) NULL DEFAULT NULL COMMENT '创建用户',
  `update_user` VARCHAR(50) NULL DEFAULT NULL COMMENT '更新用户',
  PRIMARY KEY (`log_id`),
  KEY `idx_operator_id` (`operator_id`),
  CONSTRAINT `fk_log_user` FOREIGN KEY (`operator_id`) REFERENCES `t_user` (`user_id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='系统日志表';

-- 用户偏好表
CREATE TABLE `t_user_preference` (
  `preference_id` BIGINT NOT NULL AUTO_INCREMENT COMMENT '偏好ID，自增主键',
  `user_id` BIGINT NOT NULL COMMENT '关联的用户ID，关联用户表',
  `notification_preference` BOOLEAN NOT NULL DEFAULT TRUE COMMENT '是否接收通知',
  `language_preference` VARCHAR(10) NOT NULL DEFAULT 'zh-CN' COMMENT '语言偏好',
  `theme_preference` VARCHAR(10) NOT NULL DEFAULT 'light' COMMENT '主题偏好，如浅色/深色',
  `create_time` TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_time` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  `create_user` VARCHAR(50) NULL DEFAULT NULL COMMENT '创建用户',
  `update_user` VARCHAR(50) NULL DEFAULT NULL COMMENT '更新用户',
  PRIMARY KEY (`preference_id`),
  UNIQUE KEY `idx_user_id` (`user_id`),
  CONSTRAINT `fk_preference_user` FOREIGN KEY (`user_id`) REFERENCES `t_user` (`user_id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户偏好表';

-- 通知表
CREATE TABLE `t_notification` (
  `notification_id` BIGINT NOT NULL AUTO_INCREMENT COMMENT '通知ID，自增主键',
  `user_id` BIGINT NOT NULL COMMENT '接收通知的用户ID，关联用户表',
  `title` VARCHAR(100) NOT NULL COMMENT '通知的标题',
  `content` TEXT NOT NULL COMMENT '通知的详细内容',
  `send_date` TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '通知发送的日期',
  `is_read` BOOLEAN NOT NULL DEFAULT FALSE COMMENT '是否已读',
  `create_time` TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_time` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  `create_user` VARCHAR(50) NULL DEFAULT NULL COMMENT '创建用户',
  `update_user` VARCHAR(50) NULL DEFAULT NULL COMMENT '更新用户',
  PRIMARY KEY (`notification_id`),
  KEY `idx_user_id` (`user_id`),
  CONSTRAINT `fk_notification_user` FOREIGN KEY (`user_id`) REFERENCES `t_user` (`user_id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='通知表';

绘制E-R图：

2.3.3 数据库逻辑结构设计（表结构设计）

• 设计表

1. Users（用户表）

字段名	字段类型	约束条件	描述
UserID	INT	PRIMARY KEY, AUTO_INCREMENT	用户ID，自增主键
Username	VARCHAR(50)	NOT NULL, UNIQUE	用户名，唯一
Password	VARCHAR(255)	NOT NULL	密码（加密存储）
ContactInfo	VARCHAR(100)		联系方式
Role	VARCHAR(20)	DEFAULT '普通用户'	用户角色
RegistrationDate	DATETIME	NOT NULL	注册时间
LastLoginTime	DATETIME		最后登录时间

2. LostItems（失物表）

字段名	字段类型	约束条件	描述
ItemID	INT	PRIMARY KEY, AUTO_INCREMENT	失物ID，自增主键
ItemName	VARCHAR(100)	NOT NULL	失物名称
Description	TEXT		失物描述
FoundDate	DATE	NOT NULL	发现日期
FoundLocation	VARCHAR(200)		发现地点
Status	VARCHAR(20)	DEFAULT '待认领'	失物状态
ImageURL	VARCHAR(255)		失物图片URL（可选）
FinderID	INT	FOREIGN KEY REFERENCES Users(UserID)	发现者ID，关联Users表
CategoryID	INT	FOREIGN KEY REFERENCES Categories(CategoryID)	失物类别ID，关联Categories表
ExpiryDate	DATE		失物过期日期（可选）

3. Categories（失物类别表）

字段名	字段类型	约束条件	描述
CategoryID	INT	PRIMARY KEY, AUTO_INCREMENT	类别ID，自增主键
CategoryName	VARCHAR(50)	NOT NULL, UNIQUE	类别名称

4. Claims（认领表）

字段名	字段类型	约束条件	描述
ClaimID	INT	PRIMARY KEY, AUTO_INCREMENT	认领ID，自增主键
ItemID	INT	FOREIGN KEY REFERENCES LostItems(ItemID)	失物ID，关联LostItems表
ClaimerID	INT	FOREIGN KEY REFERENCES Users(UserID)	认领者ID，关联Users表
ClaimDate	DATE	NOT NULL	认领日期
ClaimStatus	VARCHAR(20)	DEFAULT '待审核'	认领状态
ReviewComment	TEXT		审核意见（可选）
ReviewDate	DATE		审核日期（可选）

5. Feedbacks（用户反馈表）

字段名	字段类型	约束条件	描述
FeedbackID	INT	PRIMARY KEY, AUTO_INCREMENT	反馈ID，自增主键
UserID	INT	FOREIGN KEY REFERENCES Users(UserID)	用户ID，关联Users表
LostItemID	INT	FOREIGN KEY REFERENCES LostItems(ItemID)	失物ID，关联LostItems表（可选）
FeedbackContent	TEXT	NOT NULL	反馈内容
FeedbackDate	DATE	NOT NULL	反馈日期

6. SystemLogs（系统日志表）

字段名	字段类型	约束条件	描述
LogID	INT	PRIMARY KEY, AUTO_INCREMENT	日志ID，自增主键
OperationType	VARCHAR(50)	NOT NULL	操作类型（如用户注册、失物发布等）
OperationTime	DATETIME	NOT NULL	操作时间
OperatorID	INT	FOREIGN KEY REFERENCES Users(UserID)	操作者ID，关联Users表（可选）
Details	TEXT		操作详情（可选）

7. UserPreferences（用户偏好表）

字段名	字段类型	约束条件	描述
PreferenceID	INT	PRIMARY KEY, AUTO_INCREMENT	偏好ID，自增主键
UserID	INT	FOREIGN KEY REFERENCES Users(UserID)	用户ID，关联Users表
NotificationPreference	BOOLEAN	DEFAULT TRUE	是否接收通知
LanguagePreference	VARCHAR(10)	DEFAULT 'zh-CN'	语言偏好
ThemePreference	VARCHAR(20)	DEFAULT 'light'	主题偏好（如浅色/深色）

8. Notifications（通知表）

字段名	字段类型	约束条件	描述
NotificationID	INT	PRIMARY KEY, AUTO_INCREMENT	通知ID，自增主键
UserID	INT	FOREIGN KEY REFERENCES Users(UserID)	用户ID，关联Users表
Title	VARCHAR(100)	NOT NULL	通知标题
Content	TEXT	NOT NULL	通知内容
SendDate	DATETIME	NOT NULL	发送日期
IsRead	BOOLEAN	DEFAULT FALSE	是否已读

---

2.3.4 数据库物理结构设计

1. 数据库管理系统选择
        MySQL：MySQL作为数据库管理系统，它是一个开源的关系型数据库，支持事务、索引、外键约束等特性，与应用层使用的C++和Qt框架兼容性良好。
2. 存储引擎选择
        InnoDB：默认选择InnoDB作为存储引擎，因为它支持事务、外键约束、行级锁等特性，适合需要高可靠性和事务支持的应用程序。

3. 字符集与排序规则
        选择：utf8mb4字符集，utf8mb4_general_ci排序规则
        理由：完整支持Unicode，包括emoji表情

4.表结构设计
        4.1 用户表 (t_user)

create database `校园失物招领平台`;
use `校园失物招领平台`;

CREATE TABLE `t_user` (
  `user_id` BIGINT NOT NULL AUTO_INCREMENT COMMENT '用户ID，自增主键',
  `username` VARCHAR(50) NOT NULL COMMENT '用户名，唯一',
  `password` VARCHAR(255) NOT NULL COMMENT '密码，加密存储',
  `contact_info` VARCHAR(100) DEFAULT NULL COMMENT '联系方式，如手机号或邮箱',
  `role` VARCHAR(20) NOT NULL DEFAULT '普通用户' COMMENT '用户角色，如"普通用户"、"管理员"',
  `registration_date` TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '注册时间',
  `last_login_time` TIMESTAMP NULL DEFAULT NULL COMMENT '最后登录时间',
  `create_time` TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_time` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  `create_user` VARCHAR(50) NULL DEFAULT NULL COMMENT '创建用户',
  `update_user` VARCHAR(50) NULL DEFAULT NULL COMMENT '更新用户',
  PRIMARY KEY (`user_id`),
  UNIQUE KEY `idx_username` (`username`),
  INDEX `idx_role` (`role`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户表';

ps：①使用BIGINT作为主键，支持大量用户
       ②密码字段长度255，适合存储加密后的密码
       ③为常用查询字段(role)添加索引

        4.2失物类别表 (t_category)

CREATE TABLE `t_category` (
  `category_id` BIGINT NOT NULL AUTO_INCREMENT COMMENT '类别ID，自增主键',
  `category_name` VARCHAR(50) NOT NULL COMMENT '类别名称，如"书籍"、"电子产品"、"衣物"等，唯一',
  `create_time` TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_time` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  `create_user` VARCHAR(50) NULL DEFAULT NULL COMMENT '创建用户',
  `update_user` VARCHAR(50) NULL DEFAULT NULL COMMENT '更新用户',
  PRIMARY KEY (`category_id`),
  UNIQUE KEY `idx_category_name` (`category_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='失物类别表';

4.3失物表 (t_lost_item)

CREATE TABLE `t_lost_item` (
  `item_id` BIGINT NOT NULL AUTO_INCREMENT COMMENT '失物ID，自增主键',
  `item_name` VARCHAR(100) NOT NULL COMMENT '失物名称',
  `description` TEXT DEFAULT NULL COMMENT '失物的详细描述',
  `found_date` DATE NOT NULL COMMENT '发现日期',
  `found_location` VARCHAR(255) NOT NULL COMMENT '发现地点',
  `status` VARCHAR(20) NOT NULL DEFAULT '待认领' COMMENT '状态，如"待认领"、"已认领"、"已过期"',
  `image_url` VARCHAR(255) DEFAULT NULL COMMENT '图片URL，失物图片的存储路径或URL',
  `finder_id` BIGINT NOT NULL COMMENT '发布者ID，关联用户表',
  `category_id` BIGINT NOT NULL COMMENT '失物类别ID，关联失物类别表',
  `expiry_date` DATE DEFAULT NULL COMMENT '失物过期日期',
  `create_time` TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_time` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  `create_user` VARCHAR(50) NULL DEFAULT NULL COMMENT '创建用户',
  `update_user` VARCHAR(50) NULL DEFAULT NULL COMMENT '更新用户',
  PRIMARY KEY (`item_id`),
  KEY `idx_finder_id` (`finder_id`),
  KEY `idx_category_id` (`category_id`),
  KEY `idx_status` (`status`),
  KEY `idx_found_date` (`found_date`),
  CONSTRAINT `fk_lost_item_user` FOREIGN KEY (`finder_id`) REFERENCES `t_user` (`user_id`) ON DELETE CASCADE,
  CONSTRAINT `fk_lost_item_category` FOREIGN KEY (`category_id`) REFERENCES `t_category` (`category_id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='失物信息表';

ps：①添加多个索引支持常用查询
        ②设置外键级联删除，保证数据一致性
        ③状态字段默认值为"待认领"

4.4认领表 (t_claim)

CREATE TABLE `t_claim` (
  `claim_id` BIGINT NOT NULL AUTO_INCREMENT COMMENT '认领ID，自增主键',
  `item_id` BIGINT NOT NULL COMMENT '被认领的失物ID，关联失物表',
  `claimer_id` BIGINT NOT NULL COMMENT '认领者ID，关联用户表',
  `claim_date` TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '认领日期',
  `claim_status` VARCHAR(20) NOT NULL DEFAULT '待审核' COMMENT '认领状态，如"待审核"、"已通过"、"已拒绝"',
  `review_comment` TEXT DEFAULT NULL COMMENT '审核意见',
  `review_date` TIMESTAMP NULL DEFAULT NULL COMMENT '审核日期',
  `create_time` TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_time` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  `create_user` VARCHAR(50) NULL DEFAULT NULL COMMENT '创建用户',
  `update_user` VARCHAR(50) NULL DEFAULT NULL COMMENT '更新用户',
  PRIMARY KEY (`claim_id`),
  KEY `idx_item_id` (`item_id`),
  KEY `idx_claimer_id` (`claimer_id`),
  KEY `idx_claim_status` (`claim_status`),
  CONSTRAINT `fk_claim_item` FOREIGN KEY (`item_id`) REFERENCES `t_lost_item` (`item_id`) ON DELETE CASCADE,
  CONSTRAINT `fk_claim_user` FOREIGN KEY (`claimer_id`) REFERENCES `t_user` (`user_id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='认领信息表';

4.5用户反馈表 (t_feedback)

CREATE TABLE `t_feedback` (
  `feedback_id` BIGINT NOT NULL AUTO_INCREMENT COMMENT '反馈ID，自增主键',
  `user_id` BIGINT NOT NULL COMMENT '提交反馈的用户ID，关联用户表',
  `lost_item_id` BIGINT DEFAULT NULL COMMENT '反馈所针对的失物ID，关联失物表',
  `feedback_content` TEXT NOT NULL COMMENT '用户提交的反馈内容',
  `feedback_date` TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '反馈日期',
  `create_time` TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_time` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  `create_user` VARCHAR(50) NULL DEFAULT NULL COMMENT '创建用户',
  `update_user` VARCHAR(50) NULL DEFAULT NULL COMMENT '更新用户',
  PRIMARY KEY (`feedback_id`),
  KEY `idx_user_id` (`user_id`),
  KEY `idx_lost_item_id` (`lost_item_id`),
  CONSTRAINT `fk_feedback_user` FOREIGN KEY (`user_id`) REFERENCES `t_user` (`user_id`) ON DELETE CASCADE,
  CONSTRAINT `fk_feedback_item` FOREIGN KEY (`lost_item_id`) REFERENCES `t_lost_item` (`item_id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户反馈表';

4.6系统日志表 (t_system_log)

CREATE TABLE `t_system_log` (
  `log_id` BIGINT NOT NULL AUTO_INCREMENT COMMENT '日志ID，自增主键',
  `operation_type` VARCHAR(50) NOT NULL COMMENT '操作类型，如"用户注册"、"失物发布"、"认领审核"等',
  `operation_time` TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '操作发生的时间',
  `operator_id` BIGINT DEFAULT NULL COMMENT '执行操作的用户ID，关联用户表',
  `details` TEXT DEFAULT NULL COMMENT '操作的详细信息',
  `create_time` TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_time` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  `create_user` VARCHAR(50) NULL DEFAULT NULL COMMENT '创建用户',
  `update_user` VARCHAR(50) NULL DEFAULT NULL COMMENT '更新用户',
  PRIMARY KEY (`log_id`),
  KEY `idx_operation_time` (`operation_time`),
  KEY `idx_operator_id` (`operator_id`),
  CONSTRAINT `fk_log_user` FOREIGN KEY (`operator_id`) REFERENCES `t_user` (`user_id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='系统日志表';

4.7 用户偏好表 (t_user_preference)

CREATE TABLE `t_user_preference` (
  `preference_id` BIGINT NOT NULL AUTO_INCREMENT COMMENT '偏好ID，自增主键',
  `user_id` BIGINT NOT NULL COMMENT '关联的用户ID，关联用户表',
  `notification_preference` BOOLEAN NOT NULL DEFAULT TRUE COMMENT '是否接收通知',
  `language_preference` VARCHAR(10) NOT NULL DEFAULT 'zh-CN' COMMENT '语言偏好',
  `theme_preference` VARCHAR(10) NOT NULL DEFAULT 'light' COMMENT '主题偏好，如浅色/深色',
  `create_time` TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_time` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  `create_user` VARCHAR(50) NULL DEFAULT NULL COMMENT '创建用户',
  `update_user` VARCHAR(50) NULL DEFAULT NULL COMMENT '更新用户',
  PRIMARY KEY (`preference_id`),
  UNIQUE KEY `idx_user_id` (`user_id`),
  CONSTRAINT `fk_preference_user` FOREIGN KEY (`user_id`) REFERENCES `t_user` (`user_id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户偏好表';

4.8通知表 (t_notification)

CREATE TABLE `t_notification` (
  `notification_id` BIGINT NOT NULL AUTO_INCREMENT COMMENT '通知ID，自增主键',
  `user_id` BIGINT NOT NULL COMMENT '接收通知的用户ID，关联用户表',
  `title` VARCHAR(100) NOT NULL COMMENT '通知的标题',
  `content` TEXT NOT NULL COMMENT '通知的详细内容',
  `send_date` TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '通知发送的日期',
  `is_read` BOOLEAN NOT NULL DEFAULT FALSE COMMENT '是否已读',
  `create_time` TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_time` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  `create_user` VARCHAR(50) NULL DEFAULT NULL COMMENT '创建用户',
  `update_user` VARCHAR(50) NULL DEFAULT NULL COMMENT '更新用户',
  PRIMARY KEY (`notification_id`),
  KEY `idx_user_id` (`user_id`),
  KEY `idx_send_date` (`send_date`),
  KEY `idx_is_read` (`is_read`),
  CONSTRAINT `fk_notification_user` FOREIGN KEY (`user_id`) REFERENCES `t_user` (`user_id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='通知表';

5.索引策略
        1.主键索引：所有表的主键自动创建聚簇索引
        2.外键索引：为所有外键字段创建普通索引
        3.常用查询字段：
                用户表：username, role
                失物表：status, found_date, finder_id, category_id
                认领表：claim_status, item_id, claimer_id
                通知表：user_id, is_read, send_date
        4.复合索引：根据查询需求创建适当的复合索引

6.安全设计
        1.用户数据保护：
                密码加密存储，使用强加密算法
                敏感操作需要二次验证
                记录所有重要操作的日志
        2.访问控制：
                基于角色的权限控制(RBAC)
                最小权限原则
                数据库用户权限分离(读写、只读等)
        3.数据备份与恢复：
                定期全量备份
                增量备份策略
                测试恢复流程

2.4数据库视图设计

        2.4.1当前待认领失物视图

CREATE VIEW v_current_lost_items AS
SELECT 
    li.item_id,
    li.item_name,
    li.description,
    li.found_date,
    li.found_location,
    li.image_url,
    u.username AS finder_name,
    c.category_name,
    li.status
FROM 
    t_lost_item li
JOIN 
    t_user u ON li.finder_id = u.user_id
JOIN 
    t_category c ON li.category_id = c.category_id
WHERE 
    li.status = '待认领'
    AND (li.expiry_date IS NULL OR li.expiry_date >= CURDATE());

2.4.2用户认领记录视图

CREATE VIEW v_user_claims AS
SELECT 
    c.claim_id,
    li.item_name,
    u.username AS claimer_name,
    c.claim_date,
    c.claim_status,
    c.review_comment,
    c.review_date
FROM 
    t_claim c
JOIN 
    t_lost_item li ON c.item_id = li.item_id
JOIN 
    t_user u ON c.claimer_id = u.user_id;

2.4.3管理员统计视图

CREATE VIEW v_admin_stats AS
SELECT 
    DATE(li.create_time) AS date,
    COUNT(CASE WHEN li.status = '待认领' THEN 1 END) AS pending_items,
    COUNT(CASE WHEN li.status = '已认领' THEN 1 END) AS claimed_items,
    COUNT(CASE WHEN li.status = '已过期' THEN 1 END) AS expired_items,
    COUNT(c.claim_id) AS total_claims,
    COUNT(CASE WHEN c.claim_status = '已通过' THEN 1 END) AS approved_claims
FROM 
    t_lost_item li
LEFT JOIN 
    t_claim c ON li.item_id = c.item_id
GROUP BY 
    DATE(li.create_time)
ORDER BY 
    date DESC;

2.4.4用户通知汇总视图

CREATE VIEW v_user_notifications AS
SELECT 
    u.user_id,
    u.username,
    COUNT(CASE WHEN n.is_read = FALSE THEN 1 END) AS unread_count,
    COUNT(n.notification_id) AS total_notifications
FROM 
    t_user u
LEFT JOIN 
    t_notification n ON u.user_id = n.user_id
GROUP BY 
    u.user_id, u.username;

---

数据流图：

---

2.5 函数设计和存储过程设计

• 2.5.1函数设计

1.密码加密函数

DELIMITER //
CREATE FUNCTION fn_encrypt_password(plain_text VARCHAR(255)) 
RETURNS VARCHAR(255)
DETERMINISTIC
BEGIN
    RETURN SHA2(plain_text, 256);
END //
DELIMITER ;

2.检查用户权限函数

DELIMITER //
CREATE FUNCTION fn_check_user_permission(user_id BIGINT, required_role VARCHAR(20)) 
RETURNS BOOLEAN
DETERMINISTIC
BEGIN
    DECLARE user_role VARCHAR(20);
    
    SELECT role INTO user_role
    FROM t_user
    WHERE user_id = user_id;
    
    RETURN user_role = required_role OR user_role = '管理员';
END //
DELIMITER ;

3.计算失物过期状态函数

DELIMITER //
CREATE FUNCTION fn_calculate_item_status(found_date DATE, expiry_date DATE) 
RETURNS VARCHAR(20)
DETERMINISTIC
BEGIN
    IF expiry_date IS NOT NULL AND expiry_date < CURDATE() THEN
        RETURN '已过期';
    ELSEIF found_date < DATE_SUB(CURDATE(), INTERVAL 30 DAY) THEN
        RETURN '已过期';
    ELSE
        RETURN '待认领';
    END IF;
END //
DELIMITER ;

• ---

  测试函数功能：

1.

2.

3.

---

• 2.5.2存储过程设计

1.用户注册存储过程

DELIMITER //
CREATE PROCEDURE sp_register_user(
    IN p_username VARCHAR(50),
    IN p_password VARCHAR(255),
    IN p_contact_info VARCHAR(100),
    OUT p_user_id BIGINT,
    OUT p_result BOOLEAN,
    OUT p_message VARCHAR(255)
)
BEGIN
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        GET DIAGNOSTICS CONDITION 1 @sqlstate = RETURNED_SQLSTATE, 
        @errno = MYSQL_ERRNO, @text = MESSAGE_TEXT;
        SET p_result = FALSE;
        SET p_message = CONCAT('Error: ', @text);
    END;
    
    -- 检查用户名是否已存在
    IF EXISTS (SELECT 1 FROM t_user WHERE username = p_username) THEN
        SET p_result = FALSE;
        SET p_message = '用户名已存在';
    ELSE
        -- 插入新用户
        INSERT INTO t_user (username, password, contact_info)
        VALUES (p_username, fn_encrypt_password(p_password), p_contact_info);
        
        SET p_user_id = LAST_INSERT_ID();
        SET p_result = TRUE;
        SET p_message = '注册成功';
    END IF;
END //
DELIMITER ;

             2.发布失物存储过程

DELIMITER //
CREATE PROCEDURE sp_post_lost_item(
    IN p_user_id BIGINT,
    IN p_item_name VARCHAR(100),
    IN p_description TEXT,
    IN p_found_date DATE,
    IN p_found_location VARCHAR(255),
    IN p_category_id BIGINT,
    IN p_image_url VARCHAR(255),
    IN p_expiry_date DATE,
    OUT p_item_id BIGINT,
    OUT p_result BOOLEAN,
    OUT p_message VARCHAR(255)
)
BEGIN
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        GET DIAGNOSTICS CONDITION 1 @sqlstate = RETURNED_SQLSTATE, 
        @errno = MYSQL_ERRNO, @text = MESSAGE_TEXT;
        SET p_result = FALSE;
        SET p_message = CONCAT('Error: ', @text);
    END;
    
    -- 检查用户是否存在
    IF NOT EXISTS (SELECT 1 FROM t_user WHERE user_id = p_user_id) THEN
        SET p_result = FALSE;
        SET p_message = '用户不存在';
    -- 检查类别是否存在
    ELSEIF NOT EXISTS (SELECT 1 FROM t_category WHERE category_id = p_category_id) THEN
        SET p_result = FALSE;
        SET p_message = '类别不存在';
    ELSE
        -- 插入失物信息
        INSERT INTO t_lost_item (
            item_name, description, found_date, found_location, 
            finder_id, category_id, image_url, expiry_date, status
        )
        VALUES (
            p_item_name, p_description, p_found_date, p_found_location,
            p_user_id, p_category_id, p_image_url, p_expiry_date,
            fn_calculate_item_status(p_found_date, p_expiry_date)
        );
        
        SET p_item_id = LAST_INSERT_ID();
        SET p_result = TRUE;
        SET p_message = '失物发布成功';
    END IF;
END //
DELIMITER ;

3.认领失物存储过程

DELIMITER //
CREATE PROCEDURE sp_claim_lost_item(
    IN p_user_id BIGINT,
    IN p_item_id BIGINT,
    IN p_claim_reason TEXT,
    OUT p_claim_id BIGINT,
    OUT p_result BOOLEAN,
    OUT p_message VARCHAR(255)
)
BEGIN
    DECLARE v_status VARCHAR(20);
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        GET DIAGNOSTICS CONDITION 1 @sqlstate = RETURNED_SQLSTATE, 
        @errno = MYSQL_ERRNO, @text = MESSAGE_TEXT;
        SET p_result = FALSE;
        SET p_message = CONCAT('Error: ', @text);
    END;
    
    -- 检查用户是否存在
    IF NOT EXISTS (SELECT 1 FROM t_user WHERE user_id = p_user_id) THEN
        SET p_result = FALSE;
        SET p_message = '用户不存在';
    -- 检查失物是否存在
    ELSEIF NOT EXISTS (SELECT 1 FROM t_lost_item WHERE item_id = p_item_id) THEN
        SET p_result = FALSE;
        SET p_message = '失物不存在';
    -- 检查失物状态是否可认领
    ELSE
        SELECT status INTO v_status
        FROM t_lost_item
        WHERE item_id = p_item_id;
        
        IF v_status != '待认领' THEN
            SET p_result = FALSE;
            SET p_message = '该失物当前不可认领';
        ELSE
            -- 插入认领记录
            INSERT INTO t_claim (item_id, claimer_id, claim_date, review_comment)
            VALUES (p_item_id, p_user_id, NOW(), p_claim_reason);
            
            SET p_claim_id = LAST_INSERT_ID();
            SET p_result = TRUE;
            SET p_message = '认领申请已提交';
        END IF;
    END IF;
END //
DELIMITER ;

4.审核认领申请存储过程

DELIMITER //
CREATE PROCEDURE sp_review_claim(
    IN p_admin_id BIGINT,
    IN p_claim_id BIGINT,
    IN p_decision VARCHAR(20),
    IN p_comment TEXT,
    OUT p_result BOOLEAN,
    OUT p_message VARCHAR(255)
)
BEGIN
    DECLARE v_item_id BIGINT;
    DECLARE v_claimer_id BIGINT;
    DECLARE v_current_status VARCHAR(20);
    
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        GET DIAGNOSTICS CONDITION 1 @sqlstate = RETURNED_SQLSTATE, 
        @errno = MYSQL_ERRNO, @text = MESSAGE_TEXT;
        SET p_result = FALSE;
        SET p_message = CONCAT('Error: ', @text);
    END;
    
    -- 检查管理员权限
    IF NOT fn_check_user_permission(p_admin_id, '管理员') THEN
        SET p_result = FALSE;
        SET p_message = '无权限执行此操作';
    -- 检查认领申请是否存在
    ELSEIF NOT EXISTS (SELECT 1 FROM t_claim WHERE claim_id = p_claim_id) THEN
        SET p_result = FALSE;
        SET p_message = '认领申请不存在';
    ELSE
        -- 获取相关信息
        SELECT item_id, claimer_id, claim_status INTO v_item_id, v_claimer_id, v_current_status
        FROM t_claim
        WHERE claim_id = p_claim_id;
        
        -- 只有待审核的申请可以处理
        IF v_current_status != '待审核' THEN
            SET p_result = FALSE;
            SET p_message = '该申请已处理过';
        ELSE
            -- 更新认领状态
            UPDATE t_claim
            SET 
                claim_status = p_decision,
                review_comment = p_comment,
                review_date = NOW()
            WHERE claim_id = p_claim_id;
            
            -- 如果审核通过，更新失物状态
            IF p_decision = '已通过' THEN
                UPDATE t_lost_item
                SET status = '已认领'
                WHERE item_id = v_item_id;
                
                -- 创建通知
                INSERT INTO t_notification (user_id, title, content, send_date)
                VALUES (
                    v_claimer_id,
                    '认领申请已通过',
                    CONCAT('您认领的失物已审核通过，请及时领取。'),
                    NOW()
                );
                
                -- 通知失物发布者
                INSERT INTO t_notification (user_id, title, content, send_date)
                SELECT 
                    finder_id,
                    '失物已认领',
                    CONCAT('您发布的失物已被认领，认领者信息已通过审核。'),
                    NOW()
                FROM t_lost_item
                WHERE item_id = v_item_id;
            END IF;
            
            SET p_result = TRUE;
            SET p_message = '审核操作已完成';
        END IF;
    END IF;
END //
DELIMITER ;

---

测试以上功能：

1.

2.

---

流程图：

---

2.6 触发器设计

---

2.6.1失物状态自动更新触发器

DELIMITER //
CREATE TRIGGER trg_lost_item_status_update
BEFORE INSERT ON t_lost_item
FOR EACH ROW
BEGIN
    SET NEW.status = fn_calculate_item_status(NEW.found_date, NEW.expiry_date);
END //
DELIMITER ;

2.6.2用户最后登录时间更新触发器

DELIMITER //
CREATE TRIGGER trg_user_last_login
BEFORE UPDATE ON t_user
FOR EACH ROW
BEGIN
    IF NEW.last_login_time IS NULL THEN
        SET NEW.last_login_time = NOW();
    END IF;
END //
DELIMITER ;

2.6.3认领后防止失物信息修改触发器

DELIMITER //
CREATE TRIGGER trg_prevent_item_update_after_claim
BEFORE UPDATE ON t_lost_item
FOR EACH ROW
BEGIN
    DECLARE claim_count INT;
    
    SELECT COUNT(*) INTO claim_count
    FROM t_claim
    WHERE item_id = NEW.item_id;
    
    IF claim_count > 0 AND (
        NEW.item_name != OLD.item_name OR
        NEW.description != OLD.description OR
        NEW.found_date != OLD.found_date OR
        NEW.found_location != OLD.found_location OR
        NEW.category_id != OLD.category_id OR
        NEW.image_url != OLD.image_url
    ) THEN
        SIGNAL SQLSTATE '45000' 
        SET MESSAGE_TEXT = '已有认领记录的失物信息不可修改';
    END IF;
END //
DELIMITER ;

2.6.4自动创建用户偏好记录触发器

DELIMITER //
CREATE TRIGGER trg_create_user_preference
AFTER INSERT ON t_user
FOR EACH ROW
BEGIN
    INSERT INTO t_user_preference (user_id)
    VALUES (NEW.user_id);
END //
DELIMITER ;

2.6.5失物过期自动状态更新触发器

-- 首先确保事件调度器已启用
SET GLOBAL event_scheduler = ON;

-- 修改触发器，确保在插入时设置正确的初始状态
DELIMITER //
CREATE TRIGGER trg_check_expired_items_on_insert
BEFORE INSERT ON t_lost_item
FOR EACH ROW
BEGIN
    -- 在插入时设置初始状态
    SET NEW.status = fn_calculate_item_status(NEW.found_date, NEW.expiry_date);
END //
DELIMITER ;

-- 创建定期检查过期物品的事件
DELIMITER //
CREATE EVENT IF NOT EXISTS evt_check_expired_items
ON SCHEDULE EVERY 1 DAY
STARTS CURRENT_TIMESTAMP
DO
BEGIN
    UPDATE t_lost_item
    SET status = '已过期'
    WHERE (expiry_date IS NOT NULL AND expiry_date < CURDATE())
       OR (expiry_date IS NULL AND found_date < DATE_SUB(CURDATE(), INTERVAL 30 DAY));
END //
DELIMITER ;

---

触发器设计功能测试：

1.测试失物状态自动更新触发器 (trg_lost_item_status_update)

（1）插入数据

（2）测试功能

2.测试用户最后登录时间更新触发器 (trg_user_last_login)

3.测试认领后防止失物信息修改触发器 (trg_prevent_item_update_after_claim)

（1）失败情况：

错误实际上证明了我的触发器设计是有效的，它正确地阻止了对已被认领失物信息的修改，保护了数据的完整性。这是预期中安全情况。

（2）成功情况：

4.测试自动创建用户偏好记录触发器 (trg_create_user_preference)

5.测试失物过期自动状态更新功能（触发器+事件）

---

ps：感谢我自己、我的编译器和我的浏览器。。。