kknongchang_2506/app/Module/Item/Docs/Database.md

Item 模块数据库设计

1. 核心数据表

1.1 warehouses 仓库表

字段	类型	说明	索引
id	BIGINT	仓库ID，主键	PK
user_id	BIGINT	所属用户ID	IDX_user
type	ENUM('available','frozen','storage')	仓库类型：可用/冻结/储藏
capacity	INT	容量上限
created_at	TIMESTAMP	创建时间
updated_at	TIMESTAMP	更新时间

1.2 items 物品表

字段	类型	说明	索引
id	BIGINT	物品ID，主键	PK
name	VARCHAR(100)	物品名称
type	ENUM('consumable','equipment','material')	物品类型
max_stack	SMALLINT	最大堆叠数量
tradable	BOOLEAN	是否可交易
attributes	JSON	扩展属性
global_expire_at	TIMESTAMP	全局过期时间	IDX_expire
created_at	TIMESTAMP	创建时间
updated_at	TIMESTAMP	更新时间

1.3 item_instances 物品实例表

字段	类型	说明	索引
id	BIGINT	实例ID，主键	PK
item_id	BIGINT	物品ID	IDX_item
attributes	JSON	实例属性数据
created_at	TIMESTAMP	创建时间
updated_at	TIMESTAMP	更新时间

1.4 user_items 用户物品表

字段	类型	说明	索引
id	BIGINT	记录ID，主键	PK
user_id	BIGINT	用户ID	IDX_user
item_id	BIGINT	物品ID	IDX_item
instance_id	BIGINT	物品实例ID	IDX_instance
warehouse_id	BIGINT	仓库ID	IDX_warehouse
quantity	INT	数量（仅堆叠物品）
expire_at	TIMESTAMP	用户物品过期时间	IDX_expire
created_at	TIMESTAMP	创建时间
updated_at	TIMESTAMP	更新时间

1.5 item_groups 物品组表

字段	类型	说明	索引
id	BIGINT	物品组ID，主键	PK
name	VARCHAR(100)	物品组名称
type	ENUM('daily','weekly','event')	物品组类型
created_at	TIMESTAMP	创建时间
updated_at	TIMESTAMP	更新时间

1.6 item_group_items 物品组内容表

字段	类型	说明	索引
id	BIGINT	记录ID，主键	PK
group_id	BIGINT	物品组ID	IDX_group
item_id	BIGINT	物品ID	IDX_item
weight	SMALLINT	权重
created_at	TIMESTAMP	创建时间
updated_at	TIMESTAMP	更新时间

1.7 transaction_logs 物品变更日志表

字段	类型	说明	索引
id	BIGINT	日志ID，主键	PK
user_id	BIGINT	用户ID	IDX_user
item_id	BIGINT	物品ID	IDX_item
instance_id	BIGINT	物品实例ID	IDX_instance
warehouse_id	BIGINT	仓库ID	IDX_warehouse
quantity	INT	变动数量
operation	ENUM('add','remove','transfer')	操作类型
source	VARCHAR(50)	来源系统
details	JSON	操作详情
created_at	TIMESTAMP	创建时间

2. 表关系设计

erDiagram
    users ||--o{ warehouses : "1:N"
    warehouses ||--o{ user_items : "1:N"
    items ||--o{ item_instances : "1:N"
    items ||--o{ user_items : "1:N"
    items ||--o{ item_group_items : "1:N"
    item_groups ||--o{ item_group_items : "1:N"
    item_instances ||--o{ user_items : "1:1"
    user_items ||--o{ transaction_logs : "1:N"
    
    users {
        BIGINT id PK
    }
    warehouses {
        BIGINT id PK
        BIGINT user_id FK
    }
    items {
        BIGINT id PK
    }
    item_instances {
        BIGINT id PK
        BIGINT item_id FK
    }
    item_groups {
        BIGINT id PK
    }
    item_group_items {
        BIGINT id PK
        BIGINT group_id FK
        BIGINT item_id FK
    }
    user_items {
        BIGINT id PK
        BIGINT user_id FK
        BIGINT item_id FK
        BIGINT instance_id FK
        BIGINT warehouse_id FK
    }
    transaction_logs {
        BIGINT id PK
        BIGINT user_id FK
        BIGINT item_id FK
        BIGINT instance_id FK
        BIGINT warehouse_id FK
    }

3. 索引设计

3.1 仓库表索引

-- 用户仓库查询
CREATE INDEX idx_warehouse_user ON warehouses(user_id);

-- 仓库类型查询
CREATE INDEX idx_warehouse_type ON warehouses(type);

3.2 物品表索引

-- 过期物品查询
CREATE INDEX idx_item_expire ON items(global_expire_at);

-- 物品类型查询
CREATE INDEX idx_item_type ON items(type);

3.3 物品实例表索引

-- 物品实例查询
CREATE INDEX idx_instance_item ON item_instances(item_id);

3.4 用户物品表索引

-- 用户物品查询
CREATE INDEX idx_user_item_user ON user_items(user_id);

-- 仓库物品查询
CREATE INDEX idx_user_item_warehouse ON user_items(warehouse_id);

-- 物品实例查询
CREATE INDEX idx_user_item_instance ON user_items(instance_id);

-- 过期物品管理
CREATE INDEX idx_user_item_expire ON user_items(expire_at);

3.5 物品组表索引

-- 物品组查询
CREATE INDEX idx_group_type ON item_groups(type);

-- 物品组内容查询
CREATE INDEX idx_group_item_group ON item_group_items(group_id);
CREATE INDEX idx_group_item_item ON item_group_items(item_id);

3.6 事务日志表索引

-- 用户操作历史
CREATE INDEX idx_log_user_operation ON transaction_logs(user_id, operation);

-- 物品操作记录
CREATE INDEX idx_log_item ON transaction_logs(item_id);

-- 物品实例操作记录
CREATE INDEX idx_log_instance ON transaction_logs(instance_id);

-- 时间范围查询
CREATE INDEX idx_log_created ON transaction_logs(created_at);

4. 关键查询示例

4.1 获取用户仓库物品

SELECT 
    i.name, 
    ui.quantity,
    ui.expire_at
FROM user_items ui
JOIN items i ON ui.item_id = i.id
WHERE ui.user_id = 123
  AND ui.warehouse_id = 456;

4.2 查找过期物品

-- 全局过期物品
SELECT * FROM items 
WHERE global_expire_at < NOW();

-- 用户过期物品
SELECT * FROM user_items 
WHERE expire_at < NOW();

4.3 仓库容量检查

SELECT 
    w.capacity,
    COUNT(ui.id) AS used
FROM warehouses w
LEFT JOIN user_items ui ON w.id = ui.warehouse_id
WHERE w.user_id = 123
GROUP BY w.id;

4.4 查询物品变更历史

```sql SELECT

tl.operation,
tl.quantity,
tl.created_at,
i.name AS item_name,
ii.attributes->>'$.quality' AS quality

FROM transaction_logs tl JOIN items i ON tl.item_id = i.id LEFT JOIN item_instances ii ON tl.instance_id = ii.id WHERE tl.user_id = 123 AND tl.created_at BETWEEN '2023-01-01' AND '2023-12-31' ORDER BY tl.created_at DESC;