记账本APP的核心功能是记录每一笔收入与支出，并提供按时间、分类、账户等维度的统计。由于数据完全存储在本地，SQLite成为移动端持久化的首选方案。但很多项目在初期只设计一张流水表，随着需求增加，字段越来越多、查询越来越慢，最终不得不重构。合理的数据结构设计需要从实体关系出发，利用SQLite的约束、索引和视图机制来保证数据一致性与查询性能。
下面从实体建模、建表语句、索引优化和版本迁移四个角度展开，讨论如何在记账本APP中构建一套清晰、可扩展的SQLite数据结构。
一、需求分析与核心实体建模
记账本中最基础的操作是记录一笔交易：在某个账户上花了一笔钱或收入一笔钱，并归属于某个分类，可能附带备注和时间。由此可以抽象出三个核心实体：账户、分类和交易记录。账户用于区分现金、银行卡、支付宝、微信等资金渠道；分类用于区分餐饮、交通、工资、理财收益等收支类型；交易记录则把账户、分类、金额、时间关联起来。
除了这三个核心表，为了支持更灵活的统计与检索，还可以引入标签表作为多对多关联，例如一笔交易可以同时打上出差和餐饮两个标签。如果APP包含预算功能，则需要预算表记录每个分类或账户的月度限额。设计时应坚持原子性原则：金额不要存储为浮点数，而应使用整数表示最小货币单位（如分）；时间字段统一使用ISO 8601格式的TEXT类型，便于字符串比较和SQLite日期函数处理。
账户表通常包含名称、类型、初始余额、图标等字段。分类表需要区分收入或支出方向，因为同一分类名称在不同方向下统计口径不同。交易记录表则通过外键关联账户和分类，并使用CHECK约束限制金额不为零。SQLite默认不启用外键约束，需要在每次连接后执行PRAGMA foreign_keys=ON，否则删除账户时不会阻止关联交易的存在，容易产生脏数据。
二、表结构设计与SQL实现
下面是完整的建表语句，可直接在Android或iOS的SQLiteOpenHelper中执行。所有金额字段使用INTEGER存储分，避免浮点误差。时间字段统一使用TEXT，并默认写入本地时间。
-- 启用外键约束
PRAGMA foreign_keys = ON;
-- 账户表
CREATE TABLE accounts (
id INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT NOT NULL,
type TEXT NOT NULL DEFAULT 'cash',
initial_balance INTEGER NOT NULL DEFAULT 0,
icon TEXT,
created_at TEXT NOT NULL DEFAULT (datetime('now','localtime'))
);
-- 分类表
CREATE TABLE categories (
id INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT NOT NULL,
direction INTEGER NOT NULL CHECK(direction IN (0, 1)),
parent_id INTEGER,
icon TEXT,
created_at TEXT NOT NULL DEFAULT (datetime('now','localtime')),
FOREIGN KEY(parent_id) REFERENCES categories(id)
);
-- 交易记录表
CREATE TABLE transactions (
id INTEGER PRIMARY KEY AUTOINCREMENT,
account_id INTEGER NOT NULL,
category_id INTEGER NOT NULL,
amount INTEGER NOT NULL CHECK(amount != 0),
transaction_date TEXT NOT NULL,
note TEXT,
created_at TEXT NOT NULL DEFAULT (datetime('now','localtime')),
FOREIGN KEY(account_id) REFERENCES accounts(id),
FOREIGN KEY(category_id) REFERENCES categories(id)
);
-- 标签表
CREATE TABLE tags (
id INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT NOT NULL UNIQUE
);
-- 交易标签关联表
CREATE TABLE transaction_tags (
transaction_id INTEGER NOT NULL,
tag_id INTEGER NOT NULL,
PRIMARY KEY(transaction_id, tag_id),
FOREIGN KEY(transaction_id) REFERENCES transactions(id) ON DELETE CASCADE,
FOREIGN KEY(tag_id) REFERENCES tags(id) ON DELETE CASCADE
);
在分类表中，direction字段用0表示支出、1表示收入，这样可以复用同一套分类体系，同时统计时按方向分别汇总。parent_id允许构建二级分类，例如餐饮下细分早餐、午餐、晚餐。如果不使用parent_id，可以去掉该字段。交易记录表的amount字段使用CHECK约束确保不为零，避免无意义的空交易写入。
对于有预算需求的APP，可以增加预算表，按分类或账户设置月度预算金额。例如：
CREATE TABLE budgets (
id INTEGER PRIMARY KEY AUTOINCREMENT,
category_id INTEGER,
account_id INTEGER,
month TEXT NOT NULL,
amount INTEGER NOT NULL,
created_at TEXT NOT NULL DEFAULT (datetime('now','localtime')),
FOREIGN KEY(category_id) REFERENCES categories(id),
FOREIGN KEY(account_id) REFERENCES accounts(id)
);
预算表的month字段建议使用YYYY-MM格式存储，便于按月份过滤。如果只按分类做预算，account_id可以为空。这样设计既保留了扩展性，又不影响现有查询。
三、索引与查询统计优化
记账本APP最频繁的操作是按时间倒序查询流水列表，以及按月份、分类、账户进行汇总统计。如果transactions表数据量达到数万条，没有索引的查询会明显变慢。应当为高频查询条件建立复合索引。
例如，首页流水列表通常按account_id和transaction_date过滤，可以建立索引：
CREATE INDEX idx_trans_account_date ON transactions(account_id, transaction_date); CREATE INDEX idx_trans_category_date ON transactions(category_id, transaction_date); CREATE INDEX idx_trans_date ON transactions(transaction_date);
其中idx_trans_date用于全局时间范围查询，复合索引则覆盖按账户或分类筛选的场景。需要注意的是，复合索引的列顺序应与查询条件中的顺序一致。如果查询条件是WHERE category_id=? AND transaction_date BETWEEN ? AND ?，那么idx_trans_category_date比单列索引效率更高。
针对统计场景，可以创建视图来简化SQL。例如按月汇总每个分类的支出：
CREATE VIEW category_monthly_stats AS
SELECT
c.id AS category_id,
c.name AS category_name,
strftime('%Y-%m', t.transaction_date) AS month,
SUM(CASE WHEN t.amount < 0 THEN -t.amount ELSE 0 END) AS expense,
SUM(CASE WHEN t.amount > 0 THEN t.amount ELSE 0 END) AS income
FROM transactions t
JOIN categories c ON t.category_id = c.id
GROUP BY c.id, strftime('%Y-%m', t.transaction_date);
这个视图将收入和支出分开汇总，前端可直接查询月度报表。金额为负表示支出、为正表示收入，这是个人记账中常见的约定。也可以统一在应用层处理符号，但视图中的CASE表达式可以方便地区分收支。
四、数据迁移与版本管理
SQLite的ALTER TABLE能力有限，只支持重命名表和添加列，修改列类型、删除列都需要重建表。因此当APP升级需要调整数据结构时，必须在SQLiteOpenHelper的onUpgrade方法中编写迁移逻辑。常见做法是判断旧版本号，按版本顺序执行迁移脚本。
例如，从版本1升级到版本2需要给transactions表增加一个同步状态字段sync_state，可以使用ALTER TABLE添加列：
ALTER TABLE transactions ADD COLUMN sync_state INTEGER NOT NULL DEFAULT 0;
如果升级涉及删除某列或修改约束，则需要创建新表、复制数据、删除旧表、重命名新表，并重建索引。整个过程最好放在事务中执行，避免中途失败导致数据不一致。
迁移脚本示例：
BEGIN TRANSACTION;
CREATE TABLE transactions_new (
id INTEGER PRIMARY KEY AUTOINCREMENT,
account_id INTEGER NOT NULL,
category_id INTEGER NOT NULL,
amount INTEGER NOT NULL CHECK(amount != 0),
transaction_date TEXT NOT NULL,
note TEXT,
sync_state INTEGER NOT NULL DEFAULT 0,
created_at TEXT NOT NULL DEFAULT (datetime('now','localtime')),
FOREIGN KEY(account_id) REFERENCES accounts(id),
FOREIGN KEY(category_id) REFERENCES categories(id)
);
INSERT INTO transactions_new (id, account_id, category_id, amount, transaction_date, note, created_at)
SELECT id, account_id, category_id, amount, transaction_date, note, created_at FROM transactions;
DROP TABLE transactions;
ALTER TABLE transactions_new RENAME TO transactions;
CREATE INDEX idx_trans_account_date ON transactions(account_id, transaction_date);
CREATE INDEX idx_trans_category_date ON transactions(category_id, transaction_date);
CREATE INDEX idx_trans_date ON transactions(transaction_date);
COMMIT;
这个迁移过程保留了原表数据，并重新创建了索引。实际开发中应确保在onUpgrade中根据oldVersion分支执行不同脚本，避免重复执行导致错误。
总结来说，记账本APP的SQLite数据结构设计应当围绕账户、分类和交易三个核心实体展开，通过金额整数化、外键约束、复合索引和视图来兼顾数据完整性与查询性能。在需求变化时，合理利用SQLite的迁移机制可以平滑升级表结构，避免因重构而丢失历史数据。