DB_DESIGN

1. 文档说明

本文档用于描述 HomeInventory 项目的数据库设计建议，包括核心表、字段说明、关系约束和索引建议。

说明：

当前为数据库设计草案
实际字段名、类型、约束应以最终代码与 SQL 脚本为准
建议结合 BUSINESS_RULES.md 与 API_LIST.md 一起维护
若当前项目已存在 schema.sql，建议以本文档为补充说明或对照检查文档

2. 设计目标

系统数据库设计应满足以下目标：

支持用户注册与登录
支持家庭创建、成员管理、邀请流程
支持家庭级分类管理
支持物品库存管理
支持库存变化日志审计
支持通知中心
支持基础审计字段
保证基础唯一性、关联性和查询性能

3. 表设计总览

建议核心表如下：

users 用户表
families 家庭表
family_members 家庭成员表
family_invitations 家庭邀请表
categories 分类表
items 物品表
inventory_logs 库存变更日志表
notifications 通知表

4. 表设计详情

4.1 users

表说明

存储系统用户信息。

建议字段

字段名	类型	约束	说明
id	BIGINT	PK, AUTO_INCREMENT	主键
username	VARCHAR(64)	NOT NULL, UNIQUE	用户名
password	VARCHAR(255)	NOT NULL	加密后的密码
nickname	VARCHAR(64)	NULL	昵称
email	VARCHAR(128)	NULL, UNIQUE 可选	邮箱
phone	VARCHAR(32)	NULL	手机号
status	VARCHAR(32)	NOT NULL	用户状态，如 ACTIVE / DISABLED
created_at	DATETIME	NOT NULL	创建时间
updated_at	DATETIME	NOT NULL	更新时间
created_by	BIGINT	NULL	创建人
updated_by	BIGINT	NULL	更新人

约束建议

username 唯一
如果启用邮箱唯一，则 email 唯一
密码必须为加密值，不存明文

索引建议

唯一索引：uk_users_username
唯一索引：uk_users_email（如启用）

4.2 families

表说明

存储家庭基础信息。

建议字段

字段名	类型	约束	说明
id	BIGINT	PK, AUTO_INCREMENT	主键
name	VARCHAR(128)	NOT NULL	家庭名称
description	VARCHAR(255)	NULL	家庭描述
owner_id	BIGINT	NOT NULL	家庭拥有者用户 ID
status	VARCHAR(32)	NOT NULL	家庭状态，如 ACTIVE
created_at	DATETIME	NOT NULL	创建时间
updated_at	DATETIME	NOT NULL	更新时间
created_by	BIGINT	NULL	创建人
updated_by	BIGINT	NULL	更新人

约束建议

owner_id 关联 users.id
可根据业务需要决定家庭名称是否全局唯一，一般不强制

索引建议

普通索引：idx_families_owner_id

4.3 family_members

表说明

存储家庭与用户的成员关系。

建议字段

字段名	类型	约束	说明
id	BIGINT	PK, AUTO_INCREMENT	主键
family_id	BIGINT	NOT NULL	家庭 ID
user_id	BIGINT	NOT NULL	用户 ID
role	VARCHAR(32)	NOT NULL	角色，如 OWNER / ADMIN / MEMBER
joined_at	DATETIME	NOT NULL	加入时间
created_at	DATETIME	NOT NULL	创建时间
updated_at	DATETIME	NOT NULL	更新时间
created_by	BIGINT	NULL	创建人
updated_by	BIGINT	NULL	更新人

约束建议

family_id 关联 families.id
user_id 关联 users.id
同一家庭内同一用户只能有一条成员记录

唯一约束建议

唯一索引：uk_family_members_family_user (family_id, user_id)

单家庭模式额外建议

如果系统采用“一个用户只能加入一个家庭”：

可增加唯一索引：uk_family_members_user_id (user_id)

索引建议

idx_family_members_family_id
idx_family_members_user_id

4.4 family_invitations

表说明

存储家庭邀请记录。

建议字段

字段名	类型	约束	说明
id	BIGINT	PK, AUTO_INCREMENT	主键
family_id	BIGINT	NOT NULL	家庭 ID
inviter_user_id	BIGINT	NOT NULL	邀请人 ID
invited_user_id	BIGINT	NOT NULL	被邀请人 ID
status	VARCHAR(32)	NOT NULL	邀请状态，如 PENDING / ACCEPTED / REJECTED
expired_at	DATETIME	NULL	过期时间
handled_at	DATETIME	NULL	处理时间
created_at	DATETIME	NOT NULL	创建时间
updated_at	DATETIME	NOT NULL	更新时间
created_by	BIGINT	NULL	创建人
updated_by	BIGINT	NULL	更新人

约束建议

family_id 关联 families.id
inviter_user_id 关联 users.id
invited_user_id 关联 users.id

业务约束建议

同一家庭对同一用户不允许存在多个 PENDING 状态邀请
数据库层不易直接表达“仅 PENDING 唯一”，可在业务层校验
如数据库支持部分索引，可考虑增加条件唯一约束

索引建议

idx_family_invitations_family_id
idx_family_invitations_invited_user_id
idx_family_invitations_inviter_user_id
idx_family_invitations_status

4.5 categories

表说明

存储家庭下的分类信息。

建议字段

字段名	类型	约束	说明
id	BIGINT	PK, AUTO_INCREMENT	主键
family_id	BIGINT	NOT NULL	所属家庭 ID
name	VARCHAR(128)	NOT NULL	分类名称
description	VARCHAR(255)	NULL	分类描述
sort_order	INT	NULL	排序值
status	VARCHAR(32)	NOT NULL	状态，如 ACTIVE
created_at	DATETIME	NOT NULL	创建时间
updated_at	DATETIME	NOT NULL	更新时间
created_by	BIGINT	NULL	创建人
updated_by	BIGINT	NULL	更新人

约束建议

family_id 关联 families.id
同一家庭下分类名称唯一

唯一约束建议

uk_categories_family_name (family_id, name)

索引建议

idx_categories_family_id
idx_categories_name

4.6 items

表说明

存储家庭库存物品信息。

建议字段

字段名	类型	约束	说明
id	BIGINT	PK, AUTO_INCREMENT	主键
family_id	BIGINT	NOT NULL	所属家庭 ID
category_id	BIGINT	NULL	所属分类 ID
name	VARCHAR(128)	NOT NULL	物品名称
stock	INT	NOT NULL	当前库存
unit	VARCHAR(32)	NULL	单位，如个/袋/瓶
remark	VARCHAR(255)	NULL	备注
min_stock	INT	NULL	最低库存阈值
image_url	VARCHAR(255)	NULL	图片地址
expire_date	DATE	NULL	过期日期
status	VARCHAR(32)	NOT NULL	状态，如 ACTIVE
created_at	DATETIME	NOT NULL	创建时间
updated_at	DATETIME	NOT NULL	更新时间
created_by	BIGINT	NULL	创建人
updated_by	BIGINT	NULL	更新人

约束建议

family_id 关联 families.id
category_id 关联 categories.id
stock >= 0
min_stock >= 0（如启用）

注意事项

需要在业务层保证 category_id 属于同一 family_id
如允许无分类，则 category_id 可为空

索引建议

idx_items_family_id
idx_items_category_id
idx_items_name
idx_items_expire_date
idx_items_stock

4.7 inventory_logs

表说明

记录所有库存变化日志，用于审计和查询。

建议字段

字段名	类型	约束	说明
id	BIGINT	PK, AUTO_INCREMENT	主键
family_id	BIGINT	NOT NULL	家庭 ID
item_id	BIGINT	NOT NULL	物品 ID
operator_user_id	BIGINT	NOT NULL	操作人用户 ID
change_type	VARCHAR(32)	NOT NULL	IN / OUT / ADJUST
quantity	INT	NOT NULL	变化数量
before_stock	INT	NOT NULL	变更前库存
after_stock	INT	NOT NULL	变更后库存
remark	VARCHAR(255)	NULL	备注
created_at	DATETIME	NOT NULL	创建时间

约束建议

family_id 关联 families.id
item_id 关联 items.id
operator_user_id 关联 users.id

规则建议

日志数据通常只增不改
不建议提供普通业务接口删除日志

索引建议

idx_inventory_logs_item_id
idx_inventory_logs_family_id
idx_inventory_logs_operator_user_id
idx_inventory_logs_created_at
idx_inventory_logs_change_type

4.8 notifications

表说明

存储用户通知信息。

建议字段

字段名	类型	约束	说明
id	BIGINT	PK, AUTO_INCREMENT	主键
user_id	BIGINT	NOT NULL	通知所属用户
type	VARCHAR(32)	NOT NULL	通知类型
title	VARCHAR(128)	NOT NULL	标题
content	VARCHAR(500)	NOT NULL	内容
biz_type	VARCHAR(64)	NULL	业务类型
biz_id	BIGINT	NULL	业务 ID
is_read	BOOLEAN	NOT NULL	是否已读
read_at	DATETIME	NULL	已读时间
created_at	DATETIME	NOT NULL	创建时间
updated_at	DATETIME	NOT NULL	更新时间

约束建议

user_id 关联 users.id
通知归属用户必须明确

索引建议

idx_notifications_user_id
idx_notifications_is_read
idx_notifications_type
idx_notifications_created_at

5. 表关系说明

5.1 用户与家庭

families.owner_id -> users.id
family_members.user_id -> users.id
family_members.family_id -> families.id

5.2 邀请关系

family_invitations.family_id -> families.id
family_invitations.inviter_user_id -> users.id
family_invitations.invited_user_id -> users.id

5.3 分类与家庭

categories.family_id -> families.id

5.4 物品与分类、家庭

items.family_id -> families.id
items.category_id -> categories.id

5.5 库存日志与物品、用户

inventory_logs.family_id -> families.id
inventory_logs.item_id -> items.id
inventory_logs.operator_user_id -> users.id

5.6 通知与用户

notifications.user_id -> users.id

6. ER 关系简述

可以用以下逻辑理解：

一个 User 可以创建一个 Family
一个 Family 有多个 FamilyMember
一个 Family 有多个 Category
一个 Family 有多个 Item
一个 Item 属于一个 Category
一个 Item 有多条 InventoryLog
一个 User 有多条 Notification
一个 Family 有多条 FamilyInvitation

7. 状态与枚举建议

users.status

ACTIVE
DISABLED

families.status

ACTIVE
DISABLED

family_members.role

OWNER
ADMIN
MEMBER

family_invitations.status

PENDING
ACCEPTED
REJECTED
CANCELLED
EXPIRED

categories.status

ACTIVE
DISABLED

items.status

ACTIVE
DISABLED

inventory_logs.change_type

IN
OUT
ADJUST

notifications.type

INVITATION
INVITATION_ACCEPTED
LOW_STOCK
SYSTEM
ITEM_EXPIRE

8. SQL 建表示例草案

以下仅为参考示例，实际以项目使用数据库类型为准。

8.1 users

CREATE TABLE users (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(64) NOT NULL,
    password VARCHAR(255) NOT NULL,
    nickname VARCHAR(64),
    email VARCHAR(128),
    phone VARCHAR(32),
    status VARCHAR(32) NOT NULL DEFAULT 'ACTIVE',
    created_at DATETIME NOT NULL,
    updated_at DATETIME NOT NULL,
    created_by BIGINT NULL,
    updated_by BIGINT NULL,
    CONSTRAINT uk_users_username UNIQUE (username),
    CONSTRAINT uk_users_email UNIQUE (email)
);

8.2 families

CREATE TABLE families (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(128) NOT NULL,
    description VARCHAR(255),
    owner_id BIGINT NOT NULL,
    status VARCHAR(32) NOT NULL DEFAULT 'ACTIVE',
    created_at DATETIME NOT NULL,
    updated_at DATETIME NOT NULL,
    created_by BIGINT NULL,
    updated_by BIGINT NULL,
    CONSTRAINT fk_families_owner FOREIGN KEY (owner_id) REFERENCES users(id)
);

8.3 family_members

CREATE TABLE family_members (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    family_id BIGINT NOT NULL,
    user_id BIGINT NOT NULL,
    role VARCHAR(32) NOT NULL,
    joined_at DATETIME NOT NULL,
    created_at DATETIME NOT NULL,
    updated_at DATETIME NOT NULL,
    created_by BIGINT NULL,
    updated_by BIGINT NULL,
    CONSTRAINT fk_family_members_family FOREIGN KEY (family_id) REFERENCES families(id),
    CONSTRAINT fk_family_members_user FOREIGN KEY (user_id) REFERENCES users(id),
    CONSTRAINT uk_family_members_family_user UNIQUE (family_id, user_id)
);

8.4 family_invitations

CREATE TABLE family_invitations (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    family_id BIGINT NOT NULL,
    inviter_user_id BIGINT NOT NULL,
    invited_user_id BIGINT NOT NULL,
    status VARCHAR(32) NOT NULL,
    expired_at DATETIME NULL,
    handled_at DATETIME NULL,
    created_at DATETIME NOT NULL,
    updated_at DATETIME NOT NULL,
    created_by BIGINT NULL,
    updated_by BIGINT NULL,
    CONSTRAINT fk_family_invitations_family FOREIGN KEY (family_id) REFERENCES families(id),
    CONSTRAINT fk_family_invitations_inviter FOREIGN KEY (inviter_user_id) REFERENCES users(id),
    CONSTRAINT fk_family_invitations_invited FOREIGN KEY (invited_user_id) REFERENCES users(id)
);

8.5 categories

CREATE TABLE categories (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    family_id BIGINT NOT NULL,
    name VARCHAR(128) NOT NULL,
    description VARCHAR(255),
    sort_order INT NULL,
    status VARCHAR(32) NOT NULL DEFAULT 'ACTIVE',
    created_at DATETIME NOT NULL,
    updated_at DATETIME NOT NULL,
    created_by BIGINT NULL,
    updated_by BIGINT NULL,
    CONSTRAINT fk_categories_family FOREIGN KEY (family_id) REFERENCES families(id),
    CONSTRAINT uk_categories_family_name UNIQUE (family_id, name)
);

8.6 items

CREATE TABLE items (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    family_id BIGINT NOT NULL,
    category_id BIGINT NULL,
    name VARCHAR(128) NOT NULL,
    stock INT NOT NULL DEFAULT 0,
    unit VARCHAR(32),
    remark VARCHAR(255),
    min_stock INT NULL,
    image_url VARCHAR(255),
    expire_date DATE NULL,
    status VARCHAR(32) NOT NULL DEFAULT 'ACTIVE',
    created_at DATETIME NOT NULL,
    updated_at DATETIME NOT NULL,
    created_by BIGINT NULL,
    updated_by BIGINT NULL,
    CONSTRAINT fk_items_family FOREIGN KEY (family_id) REFERENCES families(id),
    CONSTRAINT fk_items_category FOREIGN KEY (category_id) REFERENCES categories(id),
    CONSTRAINT chk_items_stock CHECK (stock >= 0)
);

8.7 inventory_logs

CREATE TABLE inventory_logs (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    family_id BIGINT NOT NULL,
    item_id BIGINT NOT NULL,
    operator_user_id BIGINT NOT NULL,
    change_type VARCHAR(32) NOT NULL,
    quantity INT NOT NULL,
    before_stock INT NOT NULL,
    after_stock INT NOT NULL,
    remark VARCHAR(255),
    created_at DATETIME NOT NULL,
    CONSTRAINT fk_inventory_logs_family FOREIGN KEY (family_id) REFERENCES families(id),
    CONSTRAINT fk_inventory_logs_item FOREIGN KEY (item_id) REFERENCES items(id),
    CONSTRAINT fk_inventory_logs_operator FOREIGN KEY (operator_user_id) REFERENCES users(id)
);

8.8 notifications

CREATE TABLE notifications (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    user_id BIGINT NOT NULL,
    type VARCHAR(32) NOT NULL,
    title VARCHAR(128) NOT NULL,
    content VARCHAR(500) NOT NULL,
    biz_type VARCHAR(64) NULL,
    biz_id BIGINT NULL,
    is_read BOOLEAN NOT NULL DEFAULT FALSE,
    read_at DATETIME NULL,
    created_at DATETIME NOT NULL,
    updated_at DATETIME NOT NULL,
    CONSTRAINT fk_notifications_user FOREIGN KEY (user_id) REFERENCES users(id)
);

9. 初始化数据建议

建议 data.sql 至少包含以下测试数据：

默认测试用户
默认家庭
默认家庭成员关系
默认分类
默认物品
默认通知

示例场景：

用户 owner
家庭 我的家庭
分类 食品、日用品
物品 大米、纸巾

10. 设计校验清单

后续可按以下清单检查数据库设计是否落地：

10.1 表完整性

users
families
family_members
family_invitations
categories
items
inventory_logs
notifications

10.2 主外键

owner_id 正确关联 users
family_members 正确关联 families 和 users
family_invitations 正确关联 families 和 users
categories 正确关联 families
items 正确关联 families 和 categories
inventory_logs 正确关联 items / users / families
notifications 正确关联 users

10.3 唯一性

username 唯一
email 唯一（如启用）
family_members(family_id, user_id) 唯一
categories(family_id, name) 唯一

10.4 业务约束

stock 不为负数
邀请状态合法
成员角色合法
分类删除前检查关联物品
日志表只增不改

11. 后续优化建议

后续可根据需要扩展以下设计：

11.1 文件上传

增加 file_records 或对象存储字段管理图片资源。

11.2 操作日志

增加 operation_logs 表记录关键操作。

11.3 Token 黑名单

如使用 JWT，可增加 token_blacklist 表。

11.4 系统配置

增加 system_configs 表存储低库存阈值、通知策略等配置。

11.5 逻辑删除

如后续采用逻辑删除，可在相关表增加：

deleted
deleted_at
deleted_by

12. 当前建议结论

当前 HomeInventory 项目数据库设计建议以以下原则为主：

用户是认证主体
家庭是协作主体
分类属于家庭
物品属于家庭并挂载分类
库存变化必须记录日志
通知属于用户
审计字段尽量统一
通过主键、唯一键、外键和索引保证数据一致性与查询效率

【AI生成】HomeInventory 项目DB_DESIGN

DB_DESIGN

1. 文档说明

2. 设计目标

3. 表设计总览

4. 表设计详情

4.1 users

表说明

建议字段

约束建议

索引建议

4.2 families

表说明

建议字段

约束建议

索引建议

4.3 family_members

表说明

建议字段

约束建议

唯一约束建议

单家庭模式额外建议

索引建议

4.4 family_invitations

表说明

建议字段

约束建议

业务约束建议

索引建议

4.5 categories

表说明

建议字段

约束建议

唯一约束建议

索引建议

4.6 items

表说明

建议字段

约束建议

注意事项

索引建议

4.7 inventory_logs

表说明

建议字段

约束建议

规则建议

索引建议

4.8 notifications

表说明

建议字段

约束建议

索引建议

5. 表关系说明

5.1 用户与家庭

5.2 邀请关系

5.3 分类与家庭

5.4 物品与分类、家庭

5.5 库存日志与物品、用户

5.6 通知与用户

6. ER 关系简述

7. 状态与枚举建议

users.status

families.status

family_members.role

family_invitations.status

categories.status

items.status

inventory_logs.change_type

notifications.type

8. SQL 建表示例草案

8.1 users

8.2 families

8.3 family_members

8.4 family_invitations

8.5 categories

8.6 items

8.7 inventory_logs

8.8 notifications

9. 初始化数据建议

10. 设计校验清单