实例：家庭物品清单（Household）｜技术：双表借还、事务操作、联表查询

一、业务需求分析：物品清单的数据形态

家庭物品清单是**「物品管理 + 借还跟踪」应用——记录每件物品（位置/数量/状态），并跟踪借出/归还闭环。数据层核心是双表结构**（物品表 + 借用记录表）与事务操作（借出 = 写记录 + 改状态，必须原子）。

核心业务需求：

1. 物品记录：名称、分类（厨房/家用/工具/数码/书籍/运动）、位置、emoji、数量、状态（在家/借出）；
2. 借还跟踪：借出登记（谁借的/何时借/备注）、归还（更新归还时间 + 状态回在家）——事务保证原子性；
3. 联表查询：借用记录带物品名——LEFT JOIN；
4. 多条件筛选：分类 + 位置 + 状态组合；
5. 统计：总数/借出数/在家数、分类统计。

二、字段设计：物品 + 借还双表

物品表 household_item

字段名	类型	约束	说明
id	INTEGER	PRIMARY KEY AUTOINCREMENT	自增主键
name	TEXT	NOT NULL	物品名
category	TEXT	NOT NULL	分类（厨房/家用/工具/数码/书籍/运动）
location	TEXT	DEFAULT ‘’	收纳位置
emoji	TEXT	DEFAULT ‘📦’	物品图标
quantity	INTEGER	NOT NULL DEFAULT 1	数量
status	INTEGER	NOT NULL DEFAULT 0	0 在家 / 1 借出
note	TEXT	DEFAULT ‘’	备注
created_time	INTEGER	NOT NULL	录入时间戳

借用记录表 borrow_record

字段名	类型	约束	说明
id	INTEGER	PRIMARY KEY AUTOINCREMENT	自增主键
item_id	INTEGER	NOT NULL	物品外键
borrower	TEXT	NOT NULL	借用人
borrow_time	INTEGER	NOT NULL	借用时间戳
return_time	INTEGER	NOT NULL DEFAULT 0	归还时间戳（0 未归还）
note	TEXT	DEFAULT ‘’	备注

设计要点拆解：

1. 双表结构：物品与借还分离。物品表是「一件物品一行」（静态资产），借用表是「一次借用一行」（动态流水）——一物品多借还（同一物品可多次借出归还）。**「资产表 + 流水表」**是借还/租赁/库存类应用的通用结构（与预算的「计划 + 流水」同构）。

2. item_id 外键关联。借用记录通过 item_id 指向物品——真实外键（整数主键引用，与预算的文本业务键不同——因为物品的 id 稳定存在，不重建）。「主键引用用外键，业务键引用用文本」。

3. return_time = 0 表示未归还。归还时间 0 是「未归还」哨兵值——「0 = 未发生」的时间戳惯例（与健康/错题的 review_time=0 同模式）。页面 b.returnTime === 0 判断「未还」显示归还按钮。

4. status 二态：0 在家 / 1 借出——与借用表联动（借出 → status=1，归还 → status=0）。「状态冗余存储」：物品表存当前状态（快速筛选），借用表存历史（完整追溯）——**「当前态冗余 + 历史流水」**的权衡（避免每次查流水才知道状态）。

5. quantity 数量：扳手套装 2、《三体》3 本——数量字段（多件物品）。

6. emoji 物品图标：🍚 电饭煲、🤖 扫地机器人——零图片资源的物品视觉（与影音海报同思路）。

三、建表 SQL 与索引

CREATE TABLE IF NOT EXISTS household_item (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  name TEXT NOT NULL,
  category TEXT NOT NULL,
  location TEXT DEFAULT '',
  emoji TEXT DEFAULT '📦',
  quantity INTEGER NOT NULL DEFAULT 1,
  status INTEGER NOT NULL DEFAULT 0,
  note TEXT DEFAULT '',
  created_time INTEGER NOT NULL
);
CREATE TABLE IF NOT EXISTS borrow_record (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  item_id INTEGER NOT NULL,
  borrower TEXT NOT NULL,
  borrow_time INTEGER NOT NULL,
  return_time INTEGER NOT NULL DEFAULT 0,
  note TEXT DEFAULT ''
);
CREATE INDEX IF NOT EXISTS idx_item_category ON household_item (category);
CREATE INDEX IF NOT EXISTS idx_borrow_item ON borrow_record (item_id);

idx_item_category：分类筛选（WHERE category = '厨房'）走索引。idx_borrow_item：按物品查借用记录（WHERE item_id = ?）与 JOIN 的关联列走索引。

四、HouseholdDao 封装：事务与联表是亮点

数据层核心 HouseholdDao，本实例最独特的是borrowItem/returnItem 的事务操作与 queryBorrows 的 LEFT JOIN：

borrowItem（借出，事务）：

static async borrowItem(context: common.Context, itemId: number, borrower: string, note: string): Promise<boolean> {
  const store = await HouseholdDao.getStore(context);
  try {
    await store.beginTransaction();
    const borrowValues: relationalStore.ValuesBucket = {
      item_id: itemId, borrower: borrower,
      borrow_time: Date.now(), return_time: 0, note: note,
    };
    await store.insert(HouseholdDao.BORROW_TABLE, borrowValues);
    const values: relationalStore.ValuesBucket = { status: 1 };
    const predicates = new relationalStore.RdbPredicates(HouseholdDao.TABLE);
    predicates.equalTo('id', itemId);
    await store.update(values, predicates);
    await store.commit();
    return true;
  } catch (e) {
    try {
      await store.rollBack();
    } catch (e2) {
      // 忽略
    }
    hilog.error(DOMAIN, TAG, `借用登记事务失败: ${e}`);
    return false;
  }
}

逐行拆解：

1. 事务三件套：beginTransaction() 开启 → 操作 → commit() 提交；异常 → rollBack() 回滚——「借出 = 写借用记录 + 改物品状态」两步必须原子（写记录成功但改状态失败 = 状态不一致：记录说借出了但物品显示在家）。**「多表写操作必须事务」**是数据一致性铁律。

2. 事务的返回值：成功 return true、失败（捕获异常 + 回滚）return false——页面据此提示「登记失败」（而不是让异常冒泡）。

3. rollBack 的二次 try-catch：回滚自身也可能失败（catch (e2) {} 忽略）——「回滚失败只能忽略」（已尽力）。

4. 为什么本实例才出现事务？ 前面实例的单表写操作天然原子（一条 INSERT/UPDATE）；借出是跨两表的联合写——**「单表写天然原子，多表写必须显式事务」**是事务的出现时机。

returnItem（归还，事务）：更新借用记录 return_time + 物品状态回 0——同样事务包裹（对称结构）。

queryBorrows（LEFT JOIN 联表）：

static async queryBorrows(context: common.Context): Promise<BorrowRecord[]> {
  const store = await HouseholdDao.getStore(context);
  const result = await store.querySql(
    `SELECT b.*, i.name AS item_name
     FROM ${HouseholdDao.BORROW_TABLE} b
     LEFT JOIN ${HouseholdDao.TABLE} i ON i.id = b.item_id
     ORDER BY b.borrow_time DESC`
  );
  return HouseholdDao.collectBorrows(result);
}

SELECT b.*, i.name AS item_name
FROM borrow_record b
LEFT JOIN household_item i ON i.id = b.item_id
ORDER BY b.borrow_time DESC;

LEFT JOIN 联表：借用记录表 b 关联物品表 i（i.id = b.item_id），取出物品名（i.name AS item_name）——「借还流水要显示物品名，不能只显示 item_id」。LEFT JOIN vs INNER JOIN：LEFT 保留左表全部行（即使物品被删也显示记录，item_name 为 NULL）——「历史记录不因物品删除而消失」。

BorrowRecord 接口带 itemName：{ id, itemId, itemName, borrower, borrowTime, returnTime, note }——联表查询把物品名投影进结果（页面直接显示，不用再查物品表）。

这是本实例的联表首秀：前面实例都是单表查询，借用记录首次需要跨表取信息——「单表够用就单表，需要跨表字段就 JOIN」。

五、技术要点对照表

技术点	实现方式	生产价值
双表借还	物品表 + 借用流水表	资产/流水分离
事务	begin/commit/rollBack	多表写原子
外键关联	item_id 整数引用	稳定关联
LEFT JOIN	联表取物品名	流水可读
状态冗余	status + return_time	快速筛选/完整历史
哨兵值	return_time=0 未还	状态判断

六、文章小结

家庭物品清单的数据层核心是**「双表借还 + 事务 + 联表」**：物品表（资产）与借用记录表（流水）分离——一物品多借还；borrowItem/returnItem 用事务三件套（begin/commit/rollBack）保证「写流水 + 改状态」的原子性（本实例首次引入事务——多表联合写的必然要求）；queryBorrows 用 LEFT JOIN 联表取物品名（借还流水可读）；status 冗余当前态 + return_time=0 哨兵值。这是「资产管理 + 借还跟踪」类应用的完整数据模型。

下一篇（20-2）展示收纳格卡片风 UI——分类筛选 + 物品卡 + 借用记录 + 借还登记。

七、双表设计深入：资产与流水为何必须分离

1. 资产/流水分离的数据形态

如果只用一张表记录物品和借还，会发生什么？每借出一次就 UPDATE 状态、再借还要覆盖上一次的借用人——历史借还记录全部丢失。单表能表达「物品当前在哪」，却表达不了「它被借过几次、谁借的、什么时候还的」。

双表把数据切成两个维度：

表	记录粒度	数据性质	变化频率
household_item	一件物品一行	资产（静态）	低——录入/删除
borrow_record	一次借用一行	流水（动态）	高——每次借还

「一件物品一行 + 一次借用一行」= 一物品多借还：破壁机借给王阿姨、还回来、再借给小李——物品表始终只有一行，借用表累积多条流水。**「资产表管现状、流水表管历史」**是借还/租赁/库存类应用的通用结构。

2. item_id 外键：为什么这里是真外键

借用表用 item_id INTEGER 指向物品表主键。与预算实例「文本业务键」不同——物品的 id 从建表起就稳定存在，从不重建，可以放心用整数外键关联。原则：「主键引用用外键（整数），业务键引用用文本」——外键能走索引、JOIN 快，还保证引用完整性。

3. return_time = 0：时间戳哨兵值

return_time INTEGER NOT NULL DEFAULT 0  -- 0 = 未归还

归还时间是「事件发生时间」，未发生的事件用 0 占位——**「0 = 未发生」**的时间戳惯例（前面实例的 review_time=0 同模式）。页面用 b.returnTime === 0 判断未还、显示归还按钮；已还记录 return_time > 0，可计算借出时长 (returnTime - borrowTime) / 86400000 天。

4. status 冗余：当前态与历史流水并存

status 是「冗余存储」——理论上查最新一条 borrow_record 就能推出状态，但那样每次筛选都要 JOIN + 排序取最新。把当前态冗余在物品表：

• 筛选「借出中」：WHERE status = 1——单表条件，走索引；
• 追溯历史：borrow_record 全量流水——不丢任何一次借还。

**「当前态冗余 + 历史流水」**是空间换时间的经典权衡：冗余一个整数，换来筛选查询的简单与快速。

八、事务思路预览：两步写必须原子

借出操作拆成两步：INSERT borrow_record + UPDATE household_item SET status = 1。两步之间崩溃怎么办？记录写了但状态没改——**「记录说借出了、物品显示在家」**的不一致。

事务三件套解决：beginTransaction() → 两步写 → commit()；任一步异常 → rollBack() 全部撤销。「单表写天然原子，多表写必须显式事务」——20-3 将给出 borrowItem/returnItem 的完整实现与失败处理。

九、LEFT JOIN 思路预览：流水要显示物品名

借用记录只存 item_id，页面却要显示「破壁机 借给 王阿姨」——物品名在另一张表。LEFT JOIN 把物品名投影进查询结果：

SELECT b.*, i.name AS item_name
FROM borrow_record b
LEFT JOIN household_item i ON i.id = b.item_id;

用 LEFT 而非 INNER 的原因：历史记录不因物品删除而消失——物品删了，流水仍在，item_name 为 NULL 页面兜底显示「已删除物品」。20-3 将详解 JOIN 语法与 BorrowRecord 接口的投影设计。

十、FAQ

Q1：为什么不把借用信息直接放在物品表里？
因为一物品可多次借还——放物品表只能记「最后一次」，历史全丢。借还是流水数据，必须独立成表。

Q2：borrow_record 为什么不用 TEXT 存物品名，而是存 item_id？
存名字会冗余且不同步（改名后流水显示旧名）；存 item_id 是引用，改名自动生效，查时 JOIN 取最新名字。

Q3：status 和 return_time 都能判断是否借出，为什么不只留一个？
status 是「当前快照」（查询快），return_time 是「历史事实」（追溯全）。两者服务不同场景——筛选用 status，展示流水用 return_time，互补不冲突。

Q4：return_time 默认 0 会不会和真实时间戳冲突？
时间戳是毫秒级正数，0 是哨兵值——只要业务约定「时间戳 > 0」，0 永远表示「未发生」，不会冲突。

Q5：删除一件有借还记录的物品会怎样？
物品表删行、borrow_record 无级联删除约束而保留——这正是 LEFT JOIN 的意义：流水还在，item_name 为 NULL，页面显示「已删除物品」。