人情往来站点数据库表结构

张沉 9/23/2025

# 人情往来站点数据库表结构完整说明及SQL

# 1. 账户表 (accounts)

用途：存储用户账户信息

字段说明：

id - 主键，自增ID
username - 用户名，唯一且必填，用于登录
email - 邮箱地址，唯一，可用于登录或找回密码
password - 密码哈希值，必填
avatar - 头像图片URL
nickname - 昵称，用于显示
phone - 手机号码
created_at - 账户创建时间
updated_at - 账户信息更新时间
is_active - 账户是否激活状态（1=激活，0=禁用）
deleted_at - 软删除时间（NULL表示未删除）
extra_data - 预留字段，JSON格式存储额外信息
last_login - 最后登录时间

CREATE TABLE accounts (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    username TEXT UNIQUE NOT NULL,
    email TEXT UNIQUE,
    password TEXT NOT NULL,
    avatar TEXT,
    nickname TEXT,
    phone TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    is_active BOOLEAN DEFAULT 1,
    deleted_at TIMESTAMP,
    -- 预留字段
    extra_data TEXT,
    last_login TIMESTAMP
);

# 2. 联系人表 (contacts)

用途：存储用户的人际关系网络（礼金往来对象）

字段说明：

id - 主键，自增ID
account_id - 外键，关联到accounts表，表示这是哪个用户的联系人
name - 联系人姓名，必填
phone - 联系人电话
relationship - 与用户的关系类型（对应config表中的relationships配置值）
notes - 备注信息
created_at - 联系人创建时间
updated_at - 联系人信息更新时间
is_active - 联系人是否激活状态
deleted_at - 软删除时间
extra_data - 预留字段，JSON格式存储额外信息
avatar - 联系人头像URL
email - 联系人邮箱
address - 联系人地址

CREATE TABLE contacts (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    account_id INTEGER NOT NULL,
    name TEXT NOT NULL,
    phone TEXT,
    relationship TEXT,
    notes TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    is_active BOOLEAN DEFAULT 1,
    deleted_at TIMESTAMP,
    -- 预留字段
    extra_data TEXT,
    avatar TEXT,
    email TEXT,
    address TEXT,
    FOREIGN KEY (account_id) REFERENCES accounts(id)
);

# 3. 事件表 (events)

用途：记录具体的人情往来事件

字段说明：

id - 主键，自增ID
account_id - 外键，关联到accounts表，表示事件所属用户
contact_id - 外键，关联到contacts表，表示涉及的联系人
title - 事件标题，必填（如："张三结婚礼金"）
event_type - 事件类型（对应config表中的event_types配置值）
event_date - 事件发生日期，必填
amount - 金额，非负数，默认0
description - 事件详细描述
direction - 资金流向，只能是'give'（给出）或'receive'（收到）
created_at - 事件创建时间
updated_at - 事件信息更新时间
deleted_at - 软删除时间
extra_data - 预留字段，JSON格式存储额外信息
location - 事件发生地点
photos - 照片URL列表，JSON格式存储
tags - 标签，JSON格式存储

CREATE TABLE events (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    account_id INTEGER NOT NULL,
    contact_id INTEGER NOT NULL,
    title TEXT NOT NULL,
    event_type TEXT NOT NULL,
    event_date DATE NOT NULL,
    amount REAL DEFAULT 0 CHECK(amount >= 0),
    description TEXT,
    direction TEXT NOT NULL CHECK(direction IN ('give', 'receive')),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    deleted_at TIMESTAMP,
    -- 预留字段
    extra_data TEXT,
    location TEXT,
    photos TEXT,
    tags TEXT,
    FOREIGN KEY (account_id) REFERENCES accounts(id),
    FOREIGN KEY (contact_id) REFERENCES contacts(id)
);

# 4. 统计表 (statistics)

用途：存储用户的统计信息，避免重复计算

字段说明：

id - 主键，自增ID
account_id - 外键，关联到accounts表，表示统计数据所属用户
stat_type - 统计类型（如："year"、"month"、"quarter"）
stat_key - 统计键值（如："2024"、"2024-01"、"2024-Q1"）
total_give - 给出总额
total_receive - 收到总额
event_count - 事件数量
created_at - 统计记录创建时间
updated_at - 统计记录更新时间
extra_data - 预留字段，JSON格式存储额外信息

唯一约束：(account_id, stat_type, stat_key) 确保同一用户同一时间段只有一条统计记录

CREATE TABLE statistics (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    account_id INTEGER NOT NULL,
    stat_type TEXT NOT NULL,
    stat_key TEXT NOT NULL,
    total_give REAL DEFAULT 0,
    total_receive REAL DEFAULT 0,
    event_count INTEGER DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    -- 预留字段
    extra_data TEXT,
    FOREIGN KEY (account_id) REFERENCES accounts(id),
    UNIQUE(account_id, stat_type, stat_key)
);

# 5. 系统配置表 (config)

用途：存储系统的配置信息

字段说明：

id - 主键，自增ID
config_key - 配置项键名，唯一且必填
config_value - 配置项值，文本格式（通常为JSON字符串）
description - 配置项描述说明
created_at - 配置项创建时间
updated_at - 配置项更新时间

CREATE TABLE config (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    config_key TEXT UNIQUE NOT NULL,
    config_value TEXT,
    description TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

# 初始化配置数据

-- 初始化事件类型配置
INSERT INTO config (config_key, config_value, description) VALUES
('event_types', '[{"label":"结婚","value":"wedding"},{"label":"生日","value":"birthday"},{"label":"升学","value":"graduation"},{"label":"宝宝","value":"baby"},{"label":"周岁","value":"first_birthday"},{"label":"节日","value":"festival"},{"label":"乔迁","value":"moving"},{"label":"探望","value":"visit"},{"label":"白事","value":"funeral"},{"label":"其它","value":"other"}]', 'Event Types'),
('relationships', '[{"label":"朋友","value":"friend"},{"label":"同事","value":"colleague"},{"label":"亲戚","value":"relative"},{"label":"同学","value":"classmate"},{"label":"家人","value":"family"},{"label":"其他","value":"other"}]', 'Relationship Types');

# 数据关系示例

用户"小明"使用系统记录人情往来：

accounts表：存储小明的账户信息（用户名、密码等）
contacts表：小明添加联系人"张三"（关系：朋友）
events表：小明记录在张三生日时给了200元礼金（direction: give, event_type: birthday）
statistics表：自动统计小明2024年1月给出礼金总额增加200元
config表：提供事件类型和关系类型的选项列表

这样的设计既简洁又能满足人情往来记录的核心需求，同时预留了扩展空间。

记沉

Choose mode

人情往来站点数据库表结构

人情往来站点数据库表结构

# 人情往来站点数据库表结构完整说明及SQL

# 1. 账户表 (accounts)

# 2. 联系人表 (contacts)

# 3. 事件表 (events)

# 4. 统计表 (statistics)

# 5. 系统配置表 (config)

# 初始化配置数据

# 数据关系示例