998 lines
36 KiB
Markdown
998 lines
36 KiB
Markdown
# 配件查询系统 - 数据库设计文档（核心业务表）
|
||
|
||
**版本**: 1.0
|
||
**更新时间**: 2025-10-01
|
||
**数据库**: MySQL 8.0
|
||
**字符集**: utf8mb4 / utf8mb4_0900_ai_ci
|
||
|
||
---
|
||
|
||
## 一、租户与用户体系
|
||
|
||
### 1.1 shops（店铺/租户）
|
||
|
||
| 字段 | 类型 | 空值 | 默认值 | 说明 |
|
||
|------|------|------|--------|------|
|
||
| id | BIGINT UNSIGNED | NO | AUTO_INCREMENT | 主键 |
|
||
| name | VARCHAR(100) | NO | | 店铺名称 |
|
||
| status | TINYINT UNSIGNED | NO | 1 | 1=启用 0=停用 |
|
||
| created_at | TIMESTAMP | NO | CURRENT_TIMESTAMP | 创建时间 |
|
||
| updated_at | TIMESTAMP | NO | CURRENT_TIMESTAMP | 更新时间 |
|
||
| deleted_at | DATETIME | YES | NULL | 软删除标记 |
|
||
|
||
**索引**：
|
||
- PRIMARY KEY: `id`
|
||
- KEY: `idx_shops_status` (`status`)
|
||
|
||
**说明**：多租户隔离的核心表，所有业务数据必须关联shop_id。
|
||
|
||
---
|
||
|
||
### 1.2 users（用户）
|
||
|
||
| 字段 | 类型 | 空值 | 默认值 | 说明 |
|
||
|------|------|------|--------|------|
|
||
| id | BIGINT UNSIGNED | NO | AUTO_INCREMENT | 主键 |
|
||
| shop_id | BIGINT UNSIGNED | NO | | 所属店铺 |
|
||
| phone | VARCHAR(32) | YES | NULL | 手机号（全局唯一） |
|
||
| email | VARCHAR(128) | YES | NULL | 邮箱（全局唯一） |
|
||
| avatar_url | VARCHAR(512) | YES | NULL | 头像URL |
|
||
| name | VARCHAR(64) | NO | | 用户姓名 |
|
||
| role | VARCHAR(32) | NO | 'staff' | 角色：owner/staff/normal_admin |
|
||
| password_hash | VARCHAR(255) | YES | NULL | 密码哈希 |
|
||
| status | TINYINT UNSIGNED | NO | 1 | 1=启用 0=停用 |
|
||
| is_owner | TINYINT(1) | NO | 0 | 是否店主 |
|
||
| created_at | TIMESTAMP | NO | CURRENT_TIMESTAMP | 创建时间 |
|
||
| updated_at | TIMESTAMP | NO | CURRENT_TIMESTAMP | 更新时间 |
|
||
| deleted_at | DATETIME | YES | NULL | 软删除标记 |
|
||
|
||
**索引**：
|
||
- PRIMARY KEY: `id`
|
||
- KEY: `idx_users_shop` (`shop_id`)
|
||
- UNIQUE: `uk_users_phone` (`phone`)
|
||
- UNIQUE: `ux_users_email` (`email`)
|
||
|
||
**外键**：
|
||
- `fk_users_shop`: `shop_id` → `shops(id)`
|
||
|
||
**说明**：支持手机号、邮箱双登录方式；role字段支持普通管理员权限标识。
|
||
|
||
---
|
||
|
||
### 1.3 admins（平台管理员）
|
||
|
||
| 字段 | 类型 | 空值 | 默认值 | 说明 |
|
||
|------|------|------|--------|------|
|
||
| id | BIGINT UNSIGNED | NO | AUTO_INCREMENT | 主键 |
|
||
| username | VARCHAR(64) | NO | | 登录名 |
|
||
| phone | VARCHAR(32) | YES | NULL | 手机号 |
|
||
| password_hash | VARCHAR(255) | YES | NULL | 密码哈希 |
|
||
| status | TINYINT UNSIGNED | NO | 1 | 1=启用 0=停用 |
|
||
| created_at | TIMESTAMP | NO | CURRENT_TIMESTAMP | 创建时间 |
|
||
| updated_at | TIMESTAMP | NO | CURRENT_TIMESTAMP | 更新时间 |
|
||
| deleted_at | DATETIME | YES | NULL | 软删除标记 |
|
||
|
||
**索引**：
|
||
- PRIMARY KEY: `id`
|
||
- UNIQUE: `ux_admins_username` (`username`)
|
||
- UNIQUE: `ux_admins_phone` (`phone`)
|
||
|
||
**说明**：平台管理员不归属任何店铺，可跨租户管理数据。
|
||
|
||
---
|
||
|
||
## 二、商品与库存体系
|
||
|
||
### 2.1 products（商品）
|
||
|
||
| 字段 | 类型 | 空值 | 默认值 | 说明 |
|
||
|------|------|------|--------|------|
|
||
| id | BIGINT UNSIGNED | NO | AUTO_INCREMENT | 主键 |
|
||
| shop_id | BIGINT UNSIGNED | NO | | 所属店铺 |
|
||
| user_id | BIGINT UNSIGNED | NO | | 创建人 |
|
||
| name | VARCHAR(120) | NO | | 商品名称 |
|
||
| category_id | BIGINT UNSIGNED | YES | NULL | 类别ID |
|
||
| template_id | BIGINT UNSIGNED | YES | NULL | 配件模板ID |
|
||
| brand | VARCHAR(64) | YES | NULL | 品牌 |
|
||
| model | VARCHAR(64) | YES | NULL | 型号 |
|
||
| spec | VARCHAR(128) | YES | NULL | 规格 |
|
||
| origin | VARCHAR(64) | YES | NULL | 产地 |
|
||
| barcode | VARCHAR(32) | YES | NULL | 条码 |
|
||
| dedupe_key | VARCHAR(512) | YES | NULL | 去重键 |
|
||
| alias | VARCHAR(120) | YES | NULL | 别名 |
|
||
| is_blacklisted | TINYINT(1) | NO | 0 | 黑名单标记 |
|
||
| description | TEXT | YES | NULL | 描述 |
|
||
| global_sku_id | BIGINT UNSIGNED | YES | NULL | 全局SKU ID |
|
||
| source_submission_id | BIGINT UNSIGNED | YES | NULL | 来源提交ID |
|
||
| attributes_json | JSON | YES | NULL | 扩展属性JSON |
|
||
| safe_min | DECIMAL(18,3) | YES | NULL | 安全库存下限 |
|
||
| safe_max | DECIMAL(18,3) | YES | NULL | 安全库存上限 |
|
||
| search_text | TEXT | YES | NULL | 全文检索聚合字段 |
|
||
| created_at | TIMESTAMP | NO | CURRENT_TIMESTAMP | 创建时间 |
|
||
| updated_at | TIMESTAMP | NO | CURRENT_TIMESTAMP | 更新时间 |
|
||
| deleted_at | DATETIME | YES | NULL | 软删除标记 |
|
||
| is_active | TINYINT | YES | | 计算字段：deleted_at IS NULL |
|
||
|
||
**索引**：
|
||
- PRIMARY KEY: `id`
|
||
- KEY: `idx_products_shop` (`shop_id`)
|
||
- KEY: `idx_products_category` (`category_id`)
|
||
- KEY: `idx_products_template` (`template_id`)
|
||
- KEY: `idx_products_dedupe` (`dedupe_key`)
|
||
- KEY: `idx_products_shop_blacklist` (`shop_id`, `is_blacklisted`)
|
||
- FULLTEXT: `ft_products_search` (`name`, `brand`, `model`, `spec`, `search_text`)
|
||
- UNIQUE: `ux_products_shop_barcode` (`shop_id`, `barcode`)
|
||
- UNIQUE: `ux_products_template_name_model` (`template_id`, `name`, `model`)
|
||
|
||
**外键**：
|
||
- `fk_products_shop`: `shop_id` → `shops(id)`
|
||
- `fk_products_user`: `user_id` → `users(id)`
|
||
- `fk_products_category`: `category_id` → `product_categories(id)`
|
||
- `fk_products_template`: `template_id` → `part_templates(id)`
|
||
- `fk_products_globalsku`: `global_sku_id` → `global_skus(id)`
|
||
|
||
**说明**：核心商品表，支持模板化参数、全文检索、去重和黑名单管理。
|
||
|
||
---
|
||
|
||
### 2.2 product_prices（商品价格）
|
||
|
||
| 字段 | 类型 | 空值 | 默认值 | 说明 |
|
||
|------|------|------|--------|------|
|
||
| product_id | BIGINT UNSIGNED | NO | | 商品ID（主键） |
|
||
| shop_id | BIGINT UNSIGNED | NO | | 店铺ID |
|
||
| user_id | BIGINT UNSIGNED | NO | | 最后修改人 |
|
||
| purchase_price | DECIMAL(18,2) | NO | 0.00 | 进货价 |
|
||
| retail_price | DECIMAL(18,2) | NO | 0.00 | 零售价 |
|
||
| wholesale_price | DECIMAL(18,2) | NO | 0.00 | 批发价 |
|
||
| big_client_price | DECIMAL(18,2) | NO | 0.00 | 大客户价 |
|
||
| updated_at | TIMESTAMP | NO | CURRENT_TIMESTAMP | 更新时间 |
|
||
|
||
**索引**：
|
||
- PRIMARY KEY: `product_id`
|
||
- KEY: `idx_prices_shop` (`shop_id`)
|
||
|
||
**外键**：
|
||
- `fk_prices_product`: `product_id` → `products(id)` ON DELETE CASCADE
|
||
- `fk_prices_shop`: `shop_id` → `shops(id)`
|
||
- `fk_prices_user`: `user_id` → `users(id)`
|
||
|
||
**约束**：所有价格字段 >= 0
|
||
|
||
**说明**：四列销售价格支持不同客户等级定价策略。
|
||
|
||
---
|
||
|
||
### 2.3 inventories（库存）
|
||
|
||
| 字段 | 类型 | 空值 | 默认值 | 说明 |
|
||
|------|------|------|--------|------|
|
||
| product_id | BIGINT UNSIGNED | NO | | 商品ID（主键） |
|
||
| shop_id | BIGINT UNSIGNED | NO | | 店铺ID |
|
||
| user_id | BIGINT UNSIGNED | NO | | 最后修改人 |
|
||
| quantity | DECIMAL(18,3) | NO | 0.000 | 库存数量 |
|
||
| updated_at | TIMESTAMP | NO | CURRENT_TIMESTAMP | 更新时间 |
|
||
|
||
**索引**：
|
||
- PRIMARY KEY: `product_id`
|
||
- KEY: `idx_inventories_shop` (`shop_id`)
|
||
|
||
**外键**：
|
||
- `fk_inv_product`: `product_id` → `products(id)` ON DELETE CASCADE
|
||
- `fk_inv_shop`: `shop_id` → `shops(id)`
|
||
- `fk_inv_user`: `user_id` → `users(id)`
|
||
|
||
**约束**：quantity >= 0
|
||
|
||
**说明**：支持小数精度(3位)，适配多种计量单位。
|
||
|
||
---
|
||
|
||
### 2.4 product_categories（商品类别）
|
||
|
||
| 字段 | 类型 | 空值 | 默认值 | 说明 |
|
||
|------|------|------|--------|------|
|
||
| id | BIGINT UNSIGNED | NO | AUTO_INCREMENT | 主键 |
|
||
| shop_id | BIGINT UNSIGNED | NO | | 店铺ID（0=全局） |
|
||
| user_id | BIGINT UNSIGNED | NO | | 创建人 |
|
||
| name | VARCHAR(64) | NO | | 类别名称 |
|
||
| parent_id | BIGINT UNSIGNED | YES | NULL | 父类别ID |
|
||
| sort_order | INT | NO | 0 | 排序 |
|
||
| created_at | TIMESTAMP | NO | CURRENT_TIMESTAMP | 创建时间 |
|
||
| updated_at | TIMESTAMP | NO | CURRENT_TIMESTAMP | 更新时间 |
|
||
| deleted_at | DATETIME | YES | NULL | 软删除标记 |
|
||
|
||
**索引**：
|
||
- PRIMARY KEY: `id`
|
||
- KEY: `idx_categories_shop` (`shop_id`)
|
||
- KEY: `idx_categories_parent` (`parent_id`)
|
||
- UNIQUE: `ux_categories_shop_name` (`shop_id`, `name`)
|
||
|
||
**外键**：
|
||
- `fk_categories_shop`: `shop_id` → `shops(id)`
|
||
- `fk_categories_user`: `user_id` → `users(id)`
|
||
- `fk_categories_parent`: `parent_id` → `product_categories(id)`
|
||
|
||
**说明**：shop_id=0为全局字典，所有租户共享。
|
||
|
||
---
|
||
|
||
### 2.5 product_units（商品单位）
|
||
|
||
| 字段 | 类型 | 空值 | 默认值 | 说明 |
|
||
|------|------|------|--------|------|
|
||
| id | BIGINT UNSIGNED | NO | AUTO_INCREMENT | 主键 |
|
||
| shop_id | BIGINT UNSIGNED | NO | | 店铺ID（0=全局） |
|
||
| user_id | BIGINT UNSIGNED | NO | | 创建人 |
|
||
| name | VARCHAR(16) | NO | | 单位名称 |
|
||
| created_at | TIMESTAMP | NO | CURRENT_TIMESTAMP | 创建时间 |
|
||
| updated_at | TIMESTAMP | NO | CURRENT_TIMESTAMP | 更新时间 |
|
||
| deleted_at | DATETIME | YES | NULL | 软删除标记 |
|
||
|
||
**索引**：
|
||
- PRIMARY KEY: `id`
|
||
- KEY: `idx_units_shop` (`shop_id`)
|
||
- UNIQUE: `ux_units_shop_name` (`shop_id`, `name`)
|
||
|
||
**外键**：
|
||
- `fk_units_shop`: `shop_id` → `shops(id)`
|
||
- `fk_units_user`: `user_id` → `users(id)`
|
||
|
||
**说明**：shop_id=0为全局字典，所有租户共享。
|
||
|
||
---
|
||
|
||
### 2.6 product_images（商品图片）
|
||
|
||
| 字段 | 类型 | 空值 | 默认值 | 说明 |
|
||
|------|------|------|--------|------|
|
||
| id | BIGINT UNSIGNED | NO | AUTO_INCREMENT | 主键 |
|
||
| shop_id | BIGINT UNSIGNED | NO | | 店铺ID |
|
||
| user_id | BIGINT UNSIGNED | NO | | 上传人 |
|
||
| product_id | BIGINT UNSIGNED | NO | | 商品ID |
|
||
| url | VARCHAR(512) | NO | | 图片URL |
|
||
| hash | VARCHAR(64) | YES | NULL | 内容哈希（去重） |
|
||
| sort_order | INT | NO | 0 | 排序 |
|
||
| created_at | TIMESTAMP | NO | CURRENT_TIMESTAMP | 创建时间 |
|
||
|
||
**索引**：
|
||
- PRIMARY KEY: `id`
|
||
- KEY: `idx_product_images_product` (`product_id`)
|
||
- UNIQUE: `ux_product_image_hash` (`product_id`, `hash`)
|
||
|
||
**外键**：
|
||
- `fk_pimg_shop`: `shop_id` → `shops(id)`
|
||
- `fk_pimg_user`: `user_id` → `users(id)`
|
||
- `fk_pimg_product`: `product_id` → `products(id)` ON DELETE CASCADE
|
||
|
||
**说明**：通过hash实现图片去重。
|
||
|
||
---
|
||
|
||
### 2.7 product_aliases（商品别名）
|
||
|
||
| 字段 | 类型 | 空值 | 默认值 | 说明 |
|
||
|------|------|------|--------|------|
|
||
| id | BIGINT UNSIGNED | NO | AUTO_INCREMENT | 主键 |
|
||
| shop_id | BIGINT UNSIGNED | NO | | 店铺ID |
|
||
| user_id | BIGINT UNSIGNED | NO | | 创建人 |
|
||
| product_id | BIGINT UNSIGNED | NO | | 商品ID |
|
||
| alias | VARCHAR(120) | NO | | 别名 |
|
||
| created_at | TIMESTAMP | NO | CURRENT_TIMESTAMP | 创建时间 |
|
||
| updated_at | TIMESTAMP | NO | CURRENT_TIMESTAMP | 更新时间 |
|
||
| deleted_at | DATETIME | YES | NULL | 软删除标记 |
|
||
|
||
**索引**：
|
||
- PRIMARY KEY: `id`
|
||
- KEY: `idx_product_alias_product` (`product_id`)
|
||
- UNIQUE: `ux_product_alias` (`product_id`, `alias`)
|
||
|
||
**外键**：
|
||
- `fk_alias_shop`: `shop_id` → `shops(id)`
|
||
- `fk_alias_user`: `user_id` → `users(id)`
|
||
- `fk_alias_product`: `product_id` → `products(id)`
|
||
|
||
**说明**：支持商品多别名检索，触发器自动同步到search_text。
|
||
|
||
---
|
||
|
||
## 三、配件审核与模板体系
|
||
|
||
### 3.1 part_submissions（配件提交）
|
||
|
||
| 字段 | 类型 | 空值 | 默认值 | 说明 |
|
||
|------|------|------|--------|------|
|
||
| id | BIGINT UNSIGNED | NO | AUTO_INCREMENT | 主键 |
|
||
| shop_id | BIGINT UNSIGNED | NO | | 提交店铺 |
|
||
| user_id | BIGINT UNSIGNED | NO | | 提交用户 |
|
||
| name | VARCHAR(120) | YES | NULL | 配件名称 |
|
||
| external_code | VARCHAR(64) | YES | NULL | 外部编码 |
|
||
| model_unique | VARCHAR(128) | NO | | 规范化型号（唯一） |
|
||
| brand | VARCHAR(64) | YES | NULL | 品牌 |
|
||
| spec | VARCHAR(128) | YES | NULL | 规格 |
|
||
| unit_id | BIGINT UNSIGNED | YES | NULL | 单位 |
|
||
| category_id | BIGINT UNSIGNED | YES | NULL | 类别 |
|
||
| template_id | BIGINT UNSIGNED | YES | NULL | 模板 |
|
||
| tags | JSON | YES | NULL | 标签 |
|
||
| attributes | JSON | YES | NULL | 参数JSON |
|
||
| images | JSON | YES | NULL | 图片URL数组 |
|
||
| size | VARCHAR(64) | YES | NULL | 尺寸（兼容） |
|
||
| aperture | VARCHAR(64) | YES | NULL | 孔径（兼容） |
|
||
| compatible | TEXT | YES | NULL | 适配信息 |
|
||
| barcode | VARCHAR(64) | YES | NULL | 条码 |
|
||
| dedupe_key | VARCHAR(512) | YES | NULL | 去重键 |
|
||
| remark | TEXT | YES | NULL | 备注 |
|
||
| status | ENUM('pending','approved','rejected') | NO | 'pending' | 审核状态 |
|
||
| reviewer_id | BIGINT UNSIGNED | YES | NULL | 审核人 |
|
||
| product_id | BIGINT UNSIGNED | YES | NULL | 关联商品ID |
|
||
| global_sku_id | BIGINT UNSIGNED | YES | NULL | 关联全局SKU |
|
||
| reviewed_at | DATETIME | YES | NULL | 审核时间 |
|
||
| review_remark | VARCHAR(255) | YES | NULL | 审核备注 |
|
||
| created_at | TIMESTAMP | NO | CURRENT_TIMESTAMP | 创建时间 |
|
||
| updated_at | TIMESTAMP | NO | CURRENT_TIMESTAMP | 更新时间 |
|
||
| deleted_at | DATETIME | YES | NULL | 软删除标记 |
|
||
|
||
**索引**：
|
||
- PRIMARY KEY: `id`
|
||
- KEY: `idx_ps_shop` (`shop_id`)
|
||
- KEY: `idx_ps_user` (`user_id`)
|
||
- KEY: `idx_ps_brand` (`brand`)
|
||
- KEY: `idx_ps_status` (`status`)
|
||
- KEY: `idx_ps_template` (`template_id`)
|
||
- KEY: `idx_ps_dedupe` (`dedupe_key`)
|
||
- KEY: `idx_ps_created_at` (`created_at`)
|
||
- UNIQUE: `ux_part_model_unique` (`model_unique`)
|
||
- UNIQUE: `ux_ps_template_name_model` (`template_id`, `name`, `model_unique`)
|
||
|
||
**外键**：
|
||
- `fk_ps_shop`: `shop_id` → `shops(id)`
|
||
- `fk_ps_user`: `user_id` → `users(id)`
|
||
- `fk_ps_reviewer`: `reviewer_id` → `admins(id)`
|
||
- `fk_ps_product`: `product_id` → `products(id)`
|
||
- `fk_ps_global_sku`: `global_sku_id` → `global_skus(id)`
|
||
- `fk_ps_template`: `template_id` → `part_templates(id)`
|
||
|
||
**说明**：用户提交配件数据，审核通过后生成products记录。model_unique全局唯一。
|
||
|
||
---
|
||
|
||
### 3.2 part_templates（配件模板）
|
||
|
||
| 字段 | 类型 | 空值 | 默认值 | 说明 |
|
||
|------|------|------|--------|------|
|
||
| id | BIGINT UNSIGNED | NO | AUTO_INCREMENT | 主键 |
|
||
| category_id | BIGINT UNSIGNED | NO | | 绑定类别 |
|
||
| name | VARCHAR(120) | NO | | 配件名 |
|
||
| model_rule | VARCHAR(255) | YES | NULL | 型号规则说明 |
|
||
| status | TINYINT UNSIGNED | NO | 1 | 1=启用 0=停用 |
|
||
| created_by_admin_id | BIGINT UNSIGNED | YES | NULL | 创建管理员 |
|
||
| created_at | TIMESTAMP | NO | CURRENT_TIMESTAMP | 创建时间 |
|
||
| updated_at | TIMESTAMP | NO | CURRENT_TIMESTAMP | 更新时间 |
|
||
| deleted_at | DATETIME | YES | NULL | 软删除标记 |
|
||
|
||
**索引**：
|
||
- PRIMARY KEY: `id`
|
||
- KEY: `idx_pt_category` (`category_id`)
|
||
- KEY: `idx_pt_status` (`status`)
|
||
- KEY: `idx_pt_admin` (`created_by_admin_id`)
|
||
- KEY: `idx_part_templates_deleted_at` (`deleted_at`)
|
||
|
||
**外键**：
|
||
- `fk_pt_category`: `category_id` → `product_categories(id)`
|
||
- `fk_pt_admin`: `created_by_admin_id` → `admins(id)`
|
||
|
||
**说明**：配件模板定义，关联多个参数字段。
|
||
|
||
---
|
||
|
||
### 3.3 part_template_params（模板参数）
|
||
|
||
| 字段 | 类型 | 空值 | 默认值 | 说明 |
|
||
|------|------|------|--------|------|
|
||
| id | BIGINT UNSIGNED | NO | AUTO_INCREMENT | 主键 |
|
||
| template_id | BIGINT UNSIGNED | NO | | 所属模板 |
|
||
| field_key | VARCHAR(64) | NO | | 参数键 |
|
||
| field_label | VARCHAR(120) | NO | | 参数名（展示） |
|
||
| type | ENUM('string','number','boolean','enum','date') | NO | | 参数类型 |
|
||
| required | TINYINT(1) | NO | 0 | 是否必填 |
|
||
| unit | VARCHAR(32) | YES | NULL | 单位 |
|
||
| enum_options | JSON | YES | NULL | 枚举选项 |
|
||
| searchable | TINYINT(1) | NO | 0 | 参与检索 |
|
||
| fuzzy_searchable | TINYINT(1) | NO | 0 | 可模糊查询（数值） |
|
||
| fuzzy_tolerance | DECIMAL(18,6) | YES | NULL | 容差（NULL=平台默认） |
|
||
| dedupe_participate | TINYINT(1) | NO | 0 | 参与去重 |
|
||
| sort_order | INT | NO | 0 | 排序 |
|
||
| created_at | TIMESTAMP | NO | CURRENT_TIMESTAMP | 创建时间 |
|
||
| updated_at | TIMESTAMP | NO | CURRENT_TIMESTAMP | 更新时间 |
|
||
|
||
**索引**：
|
||
- PRIMARY KEY: `id`
|
||
- KEY: `idx_ptp_template` (`template_id`)
|
||
- KEY: `idx_ptp_sort` (`template_id`, `sort_order`)
|
||
- UNIQUE: `ux_ptp_field_key` (`template_id`, `field_key`)
|
||
|
||
**外键**：
|
||
- `fk_ptp_template`: `template_id` → `part_templates(id)` ON DELETE CASCADE
|
||
|
||
**说明**：定义模板的参数字段，支持类型化、验证和模糊搜索。
|
||
|
||
---
|
||
|
||
### 3.4 global_skus（全局SKU）
|
||
|
||
| 字段 | 类型 | 空值 | 默认值 | 说明 |
|
||
|------|------|------|--------|------|
|
||
| id | BIGINT UNSIGNED | NO | AUTO_INCREMENT | 主键 |
|
||
| name | VARCHAR(120) | NO | | SKU名称 |
|
||
| brand | VARCHAR(64) | YES | NULL | 品牌 |
|
||
| model | VARCHAR(64) | YES | NULL | 型号 |
|
||
| spec | VARCHAR(128) | YES | NULL | 规格 |
|
||
| barcode | VARCHAR(32) | YES | NULL | 条码 |
|
||
| unit_id | BIGINT UNSIGNED | YES | NULL | 单位 |
|
||
| tags | JSON | YES | NULL | 标签 |
|
||
| status | ENUM('published','offline') | NO | 'published' | 状态 |
|
||
| created_at | TIMESTAMP | NO | CURRENT_TIMESTAMP | 创建时间 |
|
||
| updated_at | TIMESTAMP | NO | CURRENT_TIMESTAMP | 更新时间 |
|
||
| deleted_at | DATETIME | YES | NULL | 软删除标记 |
|
||
|
||
**索引**：
|
||
- PRIMARY KEY: `id`
|
||
- KEY: `idx_global_skus_brand_model` (`brand`, `model`)
|
||
- UNIQUE: `ux_global_skus_barcode` (`barcode`)
|
||
|
||
**外键**：
|
||
- `fk_globalsku_unit`: `unit_id` → `product_units(id)`
|
||
|
||
**说明**：全局共享的商品库，供各租户引用。
|
||
|
||
---
|
||
|
||
## 四、客户与供应商
|
||
|
||
### 4.1 customers（客户）
|
||
|
||
| 字段 | 类型 | 空值 | 默认值 | 说明 |
|
||
|------|------|------|--------|------|
|
||
| id | BIGINT UNSIGNED | NO | AUTO_INCREMENT | 主键 |
|
||
| shop_id | BIGINT UNSIGNED | NO | | 店铺ID |
|
||
| user_id | BIGINT UNSIGNED | NO | | 创建人 |
|
||
| name | VARCHAR(120) | NO | | 客户名称 |
|
||
| contact_name | VARCHAR(64) | YES | NULL | 联系人 |
|
||
| mobile | VARCHAR(32) | YES | NULL | 手机 |
|
||
| phone | VARCHAR(32) | YES | NULL | 座机 |
|
||
| address | VARCHAR(255) | YES | NULL | 送货地址 |
|
||
| price_level | ENUM('零售价','批发价','大单报价') | NO | '零售价' | 默认价格等级 |
|
||
| status | TINYINT UNSIGNED | NO | 1 | 1=启用 0=停用 |
|
||
| ar_opening | DECIMAL(18,2) | NO | 0.00 | 期初应收 |
|
||
| remark | VARCHAR(255) | YES | NULL | 备注 |
|
||
| created_at | TIMESTAMP | NO | CURRENT_TIMESTAMP | 创建时间 |
|
||
| updated_at | TIMESTAMP | NO | CURRENT_TIMESTAMP | 更新时间 |
|
||
| deleted_at | DATETIME | YES | NULL | 软删除标记 |
|
||
|
||
**索引**：
|
||
- PRIMARY KEY: `id`
|
||
- KEY: `idx_customers_shop` (`shop_id`)
|
||
- KEY: `idx_customers_phone` (`phone`)
|
||
- KEY: `idx_customers_mobile` (`mobile`)
|
||
|
||
**外键**：
|
||
- `fk_customers_shop`: `shop_id` → `shops(id)`
|
||
- `fk_customers_user`: `user_id` → `users(id)`
|
||
|
||
**说明**：price_level关联product_prices的四列价格之一。
|
||
|
||
---
|
||
|
||
### 4.2 suppliers（供应商）
|
||
|
||
| 字段 | 类型 | 空值 | 默认值 | 说明 |
|
||
|------|------|------|--------|------|
|
||
| id | BIGINT UNSIGNED | NO | AUTO_INCREMENT | 主键 |
|
||
| shop_id | BIGINT UNSIGNED | NO | | 店铺ID |
|
||
| user_id | BIGINT UNSIGNED | NO | | 创建人 |
|
||
| name | VARCHAR(120) | NO | | 供应商名称 |
|
||
| contact_name | VARCHAR(64) | YES | NULL | 联系人 |
|
||
| mobile | VARCHAR(32) | YES | NULL | 手机 |
|
||
| phone | VARCHAR(32) | YES | NULL | 电话 |
|
||
| address | VARCHAR(255) | YES | NULL | 经营地址 |
|
||
| status | TINYINT UNSIGNED | NO | 1 | 1=启用 0=停用 |
|
||
| ap_opening | DECIMAL(18,2) | NO | 0.00 | 期初应付 |
|
||
| ap_payable | DECIMAL(18,2) | NO | 0.00 | 当前应付（实时） |
|
||
| remark | VARCHAR(255) | YES | NULL | 备注 |
|
||
| created_at | TIMESTAMP | NO | CURRENT_TIMESTAMP | 创建时间 |
|
||
| updated_at | TIMESTAMP | NO | CURRENT_TIMESTAMP | 更新时间 |
|
||
| deleted_at | DATETIME | YES | NULL | 软删除标记 |
|
||
|
||
**索引**：
|
||
- PRIMARY KEY: `id`
|
||
- KEY: `idx_suppliers_shop` (`shop_id`)
|
||
- KEY: `idx_suppliers_phone` (`phone`)
|
||
- KEY: `idx_suppliers_mobile` (`mobile`)
|
||
|
||
**外键**：
|
||
- `fk_suppliers_shop`: `shop_id` → `shops(id)`
|
||
- `fk_suppliers_user`: `user_id` → `users(id)`
|
||
|
||
**说明**：ap_payable由订单和付款记录联动维护。
|
||
|
||
---
|
||
|
||
### 4.3 accounts（结算账户）
|
||
|
||
| 字段 | 类型 | 空值 | 默认值 | 说明 |
|
||
|------|------|------|--------|------|
|
||
| id | BIGINT UNSIGNED | NO | AUTO_INCREMENT | 主键 |
|
||
| shop_id | BIGINT UNSIGNED | NO | | 店铺ID |
|
||
| user_id | BIGINT UNSIGNED | NO | | 创建人 |
|
||
| name | VARCHAR(64) | NO | | 账户名称 |
|
||
| type | ENUM('cash','bank','alipay','wechat','other') | NO | 'cash' | 账户类型 |
|
||
| bank_name | VARCHAR(64) | YES | NULL | 银行名称 |
|
||
| bank_account | VARCHAR(64) | YES | NULL | 银行账号 |
|
||
| balance | DECIMAL(18,2) | NO | 0.00 | 账户余额 |
|
||
| status | TINYINT UNSIGNED | NO | 1 | 1=启用 0=停用 |
|
||
| created_at | TIMESTAMP | NO | CURRENT_TIMESTAMP | 创建时间 |
|
||
| updated_at | TIMESTAMP | NO | CURRENT_TIMESTAMP | 更新时间 |
|
||
| deleted_at | DATETIME | YES | NULL | 软删除标记 |
|
||
|
||
**索引**：
|
||
- PRIMARY KEY: `id`
|
||
- KEY: `idx_accounts_shop` (`shop_id`)
|
||
- UNIQUE: `ux_accounts_shop_name` (`shop_id`, `name`)
|
||
|
||
**外键**：
|
||
- `fk_accounts_shop`: `shop_id` → `shops(id)`
|
||
- `fk_accounts_user`: `user_id` → `users(id)`
|
||
|
||
**说明**：支持现金、银行、支付宝、微信等多种结算方式。
|
||
|
||
---
|
||
|
||
## 五、订单与财务
|
||
|
||
### 5.1 sales_orders（销售单）
|
||
|
||
| 字段 | 类型 | 空值 | 默认值 | 说明 |
|
||
|------|------|------|--------|------|
|
||
| id | BIGINT UNSIGNED | NO | AUTO_INCREMENT | 主键 |
|
||
| shop_id | BIGINT UNSIGNED | NO | | 店铺ID |
|
||
| user_id | BIGINT UNSIGNED | NO | | 创建人 |
|
||
| customer_id | BIGINT UNSIGNED | YES | NULL | 客户ID |
|
||
| order_no | VARCHAR(32) | NO | | 订单号 |
|
||
| order_time | DATETIME | NO | | 订单时间 |
|
||
| status | ENUM('draft','approved','returned','void') | NO | 'draft' | 单据状态 |
|
||
| amount | DECIMAL(18,2) | NO | 0.00 | 应收合计 |
|
||
| paid_amount | DECIMAL(18,2) | NO | 0.00 | 已收合计 |
|
||
| remark | VARCHAR(255) | YES | NULL | 备注 |
|
||
| created_at | TIMESTAMP | NO | CURRENT_TIMESTAMP | 创建时间 |
|
||
| updated_at | TIMESTAMP | NO | CURRENT_TIMESTAMP | 更新时间 |
|
||
| deleted_at | DATETIME | YES | NULL | 软删除标记 |
|
||
|
||
**索引**：
|
||
- PRIMARY KEY: `id`
|
||
- KEY: `idx_sales_shop_time` (`shop_id`, `order_time`)
|
||
- KEY: `idx_sales_customer` (`customer_id`)
|
||
- UNIQUE: `ux_sales_order_no` (`shop_id`, `order_no`)
|
||
|
||
**外键**：
|
||
- `fk_sales_shop`: `shop_id` → `shops(id)`
|
||
- `fk_sales_user`: `user_id` → `users(id)`
|
||
- `fk_sales_customer`: `customer_id` → `customers(id)`
|
||
|
||
**说明**：approved后自动扣减库存；付款记录关联payments表。
|
||
|
||
---
|
||
|
||
### 5.2 sales_order_items（销售单明细）
|
||
|
||
| 字段 | 类型 | 空值 | 默认值 | 说明 |
|
||
|------|------|------|--------|------|
|
||
| id | BIGINT UNSIGNED | NO | AUTO_INCREMENT | 主键 |
|
||
| order_id | BIGINT UNSIGNED | NO | | 订单ID |
|
||
| product_id | BIGINT UNSIGNED | NO | | 商品ID |
|
||
| quantity | DECIMAL(18,3) | NO | | 数量 |
|
||
| unit_price | DECIMAL(18,2) | NO | | 单价 |
|
||
| discount_rate | DECIMAL(5,2) | NO | 0.00 | 折扣率(0-100) |
|
||
| cost_price | DECIMAL(18,2) | NO | 0.00 | 成本单价 |
|
||
| cost_amount | DECIMAL(18,2) | NO | 0.00 | 成本金额 |
|
||
| amount | DECIMAL(18,2) | NO | | 行金额 |
|
||
|
||
**索引**：
|
||
- PRIMARY KEY: `id`
|
||
- KEY: `idx_soi_order` (`order_id`)
|
||
- KEY: `idx_soi_product` (`product_id`)
|
||
|
||
**外键**：
|
||
- `fk_soi_order`: `order_id` → `sales_orders(id)` ON DELETE CASCADE
|
||
- `fk_soi_product`: `product_id` → `products(id)`
|
||
|
||
**约束**：
|
||
- quantity > 0
|
||
- unit_price >= 0
|
||
- discount_rate >= 0 AND <= 100
|
||
|
||
**说明**：记录开单时的成本价，用于利润分析。
|
||
|
||
---
|
||
|
||
### 5.3 purchase_orders（进货单）
|
||
|
||
| 字段 | 类型 | 空值 | 默认值 | 说明 |
|
||
|------|------|------|--------|------|
|
||
| id | BIGINT UNSIGNED | NO | AUTO_INCREMENT | 主键 |
|
||
| shop_id | BIGINT UNSIGNED | NO | | 店铺ID |
|
||
| user_id | BIGINT UNSIGNED | NO | | 创建人 |
|
||
| supplier_id | BIGINT UNSIGNED | YES | NULL | 供应商ID |
|
||
| order_no | VARCHAR(32) | NO | | 订单号 |
|
||
| order_time | DATETIME | NO | | 订单时间 |
|
||
| status | ENUM('draft','approved','void','returned') | NO | 'draft' | 单据状态 |
|
||
| amount | DECIMAL(18,2) | NO | 0.00 | 应付合计 |
|
||
| paid_amount | DECIMAL(18,2) | NO | 0.00 | 已付合计 |
|
||
| remark | VARCHAR(255) | YES | NULL | 备注 |
|
||
| created_at | TIMESTAMP | NO | CURRENT_TIMESTAMP | 创建时间 |
|
||
| updated_at | TIMESTAMP | NO | CURRENT_TIMESTAMP | 更新时间 |
|
||
| deleted_at | DATETIME | YES | NULL | 软删除标记 |
|
||
|
||
**索引**：
|
||
- PRIMARY KEY: `id`
|
||
- KEY: `idx_purchase_shop_time` (`shop_id`, `order_time`)
|
||
- KEY: `idx_purchase_supplier` (`supplier_id`)
|
||
- UNIQUE: `ux_purchase_order_no` (`shop_id`, `order_no`)
|
||
|
||
**外键**：
|
||
- `fk_purchase_shop`: `shop_id` → `shops(id)`
|
||
- `fk_purchase_user`: `user_id` → `users(id)`
|
||
- `fk_purchase_supplier`: `supplier_id` → `suppliers(id)`
|
||
|
||
**说明**：approved后自动增加库存。
|
||
|
||
---
|
||
|
||
### 5.4 purchase_order_items（进货单明细）
|
||
|
||
| 字段 | 类型 | 空值 | 默认值 | 说明 |
|
||
|------|------|------|--------|------|
|
||
| id | BIGINT UNSIGNED | NO | AUTO_INCREMENT | 主键 |
|
||
| order_id | BIGINT UNSIGNED | NO | | 订单ID |
|
||
| product_id | BIGINT UNSIGNED | NO | | 商品ID |
|
||
| quantity | DECIMAL(18,3) | NO | | 数量 |
|
||
| unit_price | DECIMAL(18,2) | NO | | 单价 |
|
||
| amount | DECIMAL(18,2) | NO | | 行金额 |
|
||
|
||
**索引**：
|
||
- PRIMARY KEY: `id`
|
||
- KEY: `idx_poi_order` (`order_id`)
|
||
- KEY: `idx_poi_product` (`product_id`)
|
||
|
||
**外键**：
|
||
- `fk_poi_order`: `order_id` → `purchase_orders(id)` ON DELETE CASCADE
|
||
- `fk_poi_product`: `product_id` → `products(id)`
|
||
|
||
**约束**：
|
||
- quantity > 0
|
||
- unit_price >= 0
|
||
|
||
---
|
||
|
||
### 5.5 payments（收付款记录）
|
||
|
||
| 字段 | 类型 | 空值 | 默认值 | 说明 |
|
||
|------|------|------|--------|------|
|
||
| id | BIGINT UNSIGNED | NO | AUTO_INCREMENT | 主键 |
|
||
| shop_id | BIGINT UNSIGNED | NO | | 店铺ID |
|
||
| user_id | BIGINT UNSIGNED | NO | | 操作人 |
|
||
| biz_type | ENUM('sale','purchase','other') | NO | | 业务类型 |
|
||
| biz_id | BIGINT UNSIGNED | YES | NULL | 业务单据ID |
|
||
| account_id | BIGINT UNSIGNED | NO | | 结算账户 |
|
||
| direction | ENUM('in','out') | NO | | 收款/付款 |
|
||
| amount | DECIMAL(18,2) | NO | | 金额 |
|
||
| pay_time | DATETIME | NO | | 付款时间 |
|
||
| category | VARCHAR(64) | YES | NULL | 分类key（其他收支） |
|
||
| remark | VARCHAR(255) | YES | NULL | 备注 |
|
||
| created_at | TIMESTAMP | NO | CURRENT_TIMESTAMP | 创建时间 |
|
||
|
||
**索引**：
|
||
- PRIMARY KEY: `id`
|
||
- KEY: `idx_payments_shop_time` (`shop_id`, `pay_time`)
|
||
- KEY: `idx_payments_biz` (`biz_type`, `biz_id`)
|
||
|
||
**外键**：
|
||
- `fk_payments_shop`: `shop_id` → `shops(id)`
|
||
- `fk_payments_user`: `user_id` → `users(id)`
|
||
- `fk_payments_account`: `account_id` → `accounts(id)`
|
||
|
||
**约束**：amount > 0
|
||
|
||
**说明**：统一管理销售收款、进货付款和其他收支。
|
||
|
||
---
|
||
|
||
### 5.6 other_transactions（其他收支）
|
||
|
||
| 字段 | 类型 | 空值 | 默认值 | 说明 |
|
||
|------|------|------|--------|------|
|
||
| id | BIGINT UNSIGNED | NO | AUTO_INCREMENT | 主键 |
|
||
| shop_id | BIGINT UNSIGNED | NO | | 店铺ID |
|
||
| user_id | BIGINT UNSIGNED | NO | | 创建人 |
|
||
| type | ENUM('income','expense') | NO | | 收入/支出 |
|
||
| category | VARCHAR(64) | NO | | 分类key |
|
||
| counterparty_type | VARCHAR(32) | YES | NULL | customer/supplier/other |
|
||
| counterparty_id | BIGINT UNSIGNED | YES | NULL | 往来单位ID |
|
||
| account_id | BIGINT UNSIGNED | NO | | 结算账户 |
|
||
| amount | DECIMAL(18,2) | NO | | 金额 |
|
||
| tx_time | DATETIME | NO | | 交易时间 |
|
||
| remark | VARCHAR(255) | YES | NULL | 备注 |
|
||
| created_at | TIMESTAMP | NO | CURRENT_TIMESTAMP | 创建时间 |
|
||
| updated_at | TIMESTAMP | NO | CURRENT_TIMESTAMP | 更新时间 |
|
||
| deleted_at | DATETIME | YES | NULL | 软删除标记 |
|
||
|
||
**索引**：
|
||
- PRIMARY KEY: `id`
|
||
- KEY: `idx_ot_shop_time` (`shop_id`, `tx_time`)
|
||
- KEY: `idx_ot_account` (`account_id`)
|
||
|
||
**外键**：
|
||
- `fk_ot_shop`: `shop_id` → `shops(id)`
|
||
- `fk_ot_user`: `user_id` → `users(id)`
|
||
- `fk_ot_account`: `account_id` → `accounts(id)`
|
||
|
||
**约束**：amount > 0
|
||
|
||
**说明**：记录非进销业务的其他收入和支出。
|
||
|
||
---
|
||
|
||
### 5.7 inventory_movements（库存流水）
|
||
|
||
| 字段 | 类型 | 空值 | 默认值 | 说明 |
|
||
|------|------|------|--------|------|
|
||
| id | BIGINT UNSIGNED | NO | AUTO_INCREMENT | 主键 |
|
||
| shop_id | BIGINT UNSIGNED | NO | | 店铺ID |
|
||
| user_id | BIGINT UNSIGNED | NO | | 操作人 |
|
||
| product_id | BIGINT UNSIGNED | NO | | 商品ID |
|
||
| source_type | VARCHAR(32) | NO | | 来源：sale/purchase/return/adjust/audit |
|
||
| source_id | BIGINT UNSIGNED | YES | NULL | 来源单据ID |
|
||
| qty_delta | DECIMAL(18,3) | NO | | 数量增减（+/-） |
|
||
| amount_delta | DECIMAL(18,2) | YES | NULL | 金额增减 |
|
||
| cost_price | DECIMAL(18,2) | YES | NULL | 成本单价 |
|
||
| cost_amount | DECIMAL(18,2) | YES | NULL | 成本金额 |
|
||
| reason | VARCHAR(64) | YES | NULL | 原因/类别 |
|
||
| tx_time | DATETIME | NO | | 业务时间 |
|
||
| remark | VARCHAR(255) | YES | NULL | 备注 |
|
||
| created_at | TIMESTAMP | NO | CURRENT_TIMESTAMP | 创建时间 |
|
||
|
||
**索引**：
|
||
- PRIMARY KEY: `id`
|
||
- KEY: `idx_im_shop_time` (`shop_id`, `tx_time`)
|
||
- KEY: `idx_im_product` (`product_id`)
|
||
|
||
**说明**：所有库存变动的审计日志，出库为负，入库为正。
|
||
|
||
---
|
||
|
||
## 六、销售退货
|
||
|
||
### 6.1 sales_return_orders（销售退货单）
|
||
|
||
| 字段 | 类型 | 空值 | 默认值 | 说明 |
|
||
|------|------|------|--------|------|
|
||
| id | BIGINT UNSIGNED | NO | AUTO_INCREMENT | 主键 |
|
||
| shop_id | BIGINT UNSIGNED | NO | | 店铺ID |
|
||
| user_id | BIGINT UNSIGNED | NO | | 创建人 |
|
||
| customer_id | BIGINT UNSIGNED | YES | NULL | 客户ID |
|
||
| order_no | VARCHAR(32) | NO | | 订单号 |
|
||
| order_time | DATETIME | NO | | 订单时间 |
|
||
| status | ENUM('approved','void') | NO | 'approved' | 单据状态 |
|
||
| amount | DECIMAL(18,2) | NO | 0.00 | 退货金额合计 |
|
||
| paid_amount | DECIMAL(18,2) | NO | 0.00 | 已退/已收合计 |
|
||
| remark | VARCHAR(255) | YES | NULL | 备注 |
|
||
| created_at | TIMESTAMP | NO | CURRENT_TIMESTAMP | 创建时间 |
|
||
| updated_at | TIMESTAMP | NO | CURRENT_TIMESTAMP | 更新时间 |
|
||
| deleted_at | DATETIME | YES | NULL | 软删除标记 |
|
||
|
||
**索引**：
|
||
- PRIMARY KEY: `id`
|
||
- KEY: `idx_sr_shop_time` (`shop_id`, `order_time`)
|
||
- UNIQUE: `ux_sr_order_no` (`shop_id`, `order_no`)
|
||
|
||
**外键**：
|
||
- `fk_sr_shop`: `shop_id` → `shops(id)`
|
||
- `fk_sr_user`: `user_id` → `users(id)`
|
||
- `fk_sr_customer`: `customer_id` → `customers(id)`
|
||
|
||
**说明**：approved后增加库存。
|
||
|
||
---
|
||
|
||
### 6.2 sales_return_order_items（销售退货明细）
|
||
|
||
| 字段 | 类型 | 空值 | 默认值 | 说明 |
|
||
|------|------|------|--------|------|
|
||
| id | BIGINT UNSIGNED | NO | AUTO_INCREMENT | 主键 |
|
||
| order_id | BIGINT UNSIGNED | NO | | 订单ID |
|
||
| product_id | BIGINT UNSIGNED | NO | | 商品ID |
|
||
| quantity | DECIMAL(18,3) | NO | | 数量 |
|
||
| unit_price | DECIMAL(18,2) | NO | | 单价 |
|
||
| discount_rate | DECIMAL(5,2) | NO | 0.00 | 折扣率 |
|
||
| cost_price | DECIMAL(18,2) | NO | 0.00 | 成本单价 |
|
||
| cost_amount | DECIMAL(18,2) | NO | 0.00 | 成本金额 |
|
||
| amount | DECIMAL(18,2) | NO | | 行金额 |
|
||
|
||
**索引**：
|
||
- PRIMARY KEY: `id`
|
||
- KEY: `idx_sroi_order` (`order_id`)
|
||
- KEY: `idx_sroi_product` (`product_id`)
|
||
|
||
**外键**：
|
||
- `fk_sroi_order`: `order_id` → `sales_return_orders(id)` ON DELETE CASCADE
|
||
- `fk_sroi_product`: `product_id` → `products(id)`
|
||
|
||
---
|
||
|
||
### 6.3 purchase_return_orders（进货退货单）
|
||
|
||
| 字段 | 类型 | 空值 | 默认值 | 说明 |
|
||
|------|------|------|--------|------|
|
||
| id | BIGINT UNSIGNED | NO | AUTO_INCREMENT | 主键 |
|
||
| shop_id | BIGINT UNSIGNED | NO | | 店铺ID |
|
||
| user_id | BIGINT UNSIGNED | NO | | 创建人 |
|
||
| supplier_id | BIGINT UNSIGNED | YES | NULL | 供应商ID |
|
||
| order_no | VARCHAR(32) | NO | | 订单号 |
|
||
| order_time | DATETIME | NO | | 订单时间 |
|
||
| status | ENUM('approved','void') | NO | 'approved' | 单据状态 |
|
||
| amount | DECIMAL(18,2) | NO | 0.00 | 退货金额合计 |
|
||
| paid_amount | DECIMAL(18,2) | NO | 0.00 | 已付合计 |
|
||
| remark | VARCHAR(255) | YES | NULL | 备注 |
|
||
| created_at | TIMESTAMP | NO | CURRENT_TIMESTAMP | 创建时间 |
|
||
| updated_at | TIMESTAMP | NO | CURRENT_TIMESTAMP | 更新时间 |
|
||
| deleted_at | DATETIME | YES | NULL | 软删除标记 |
|
||
|
||
**索引**：
|
||
- PRIMARY KEY: `id`
|
||
- KEY: `idx_pr_shop_time` (`shop_id`, `order_time`)
|
||
- UNIQUE: `ux_pr_order_no` (`shop_id`, `order_no`)
|
||
|
||
**外键**：
|
||
- `fk_pr_shop`: `shop_id` → `shops(id)`
|
||
- `fk_pr_user`: `user_id` → `users(id)`
|
||
- `fk_pr_supplier`: `supplier_id` → `suppliers(id)`
|
||
|
||
**说明**：approved后减少库存。
|
||
|
||
---
|
||
|
||
### 6.4 purchase_return_order_items（进货退货明细）
|
||
|
||
| 字段 | 类型 | 空值 | 默认值 | 说明 |
|
||
|------|------|------|--------|------|
|
||
| id | BIGINT UNSIGNED | NO | AUTO_INCREMENT | 主键 |
|
||
| order_id | BIGINT UNSIGNED | NO | | 订单ID |
|
||
| product_id | BIGINT UNSIGNED | NO | | 商品ID |
|
||
| quantity | DECIMAL(18,3) | NO | | 数量 |
|
||
| unit_price | DECIMAL(18,2) | NO | | 单价 |
|
||
| amount | DECIMAL(18,2) | NO | | 行金额 |
|
||
|
||
**索引**：
|
||
- PRIMARY KEY: `id`
|
||
- KEY: `idx_proi_order` (`order_id`)
|
||
- KEY: `idx_proi_product` (`product_id`)
|
||
|
||
**外键**：
|
||
- `fk_proi_order`: `order_id` → `purchase_return_orders(id)` ON DELETE CASCADE
|
||
- `fk_proi_product`: `product_id` → `products(id)`
|
||
|
||
---
|
||
|
||
## 七、触发器
|
||
|
||
### 7.1 products.search_text 维护触发器
|
||
|
||
**trg_products_ai** (AFTER INSERT)
|
||
```sql
|
||
UPDATE products
|
||
SET search_text = CONCAT_WS(' ', NEW.name, NEW.brand, NEW.model, NEW.spec)
|
||
WHERE id = NEW.id;
|
||
```
|
||
|
||
**trg_products_au** (BEFORE UPDATE)
|
||
```sql
|
||
SET NEW.search_text = CONCAT_WS(' ', NEW.name, NEW.brand, NEW.model, NEW.spec);
|
||
```
|
||
|
||
### 7.2 product_aliases 同步触发器
|
||
|
||
**trg_palias_ai** (AFTER INSERT)
|
||
```sql
|
||
-- 聚合所有别名到products.search_text
|
||
UPDATE products p
|
||
JOIN (
|
||
SELECT pa.product_id, GROUP_CONCAT(pa.alias SEPARATOR ' ') AS aliases
|
||
FROM product_aliases pa
|
||
WHERE pa.product_id = NEW.product_id AND pa.deleted_at IS NULL
|
||
GROUP BY pa.product_id
|
||
) a ON a.product_id = p.id
|
||
SET p.search_text = CONCAT_WS(' ', p.name, p.brand, p.model, p.spec, a.aliases)
|
||
WHERE p.id = NEW.product_id;
|
||
```
|
||
|
||
**trg_palias_au** (AFTER UPDATE)、**trg_palias_ad** (AFTER DELETE) - 类似逻辑
|
||
|
||
### 7.3 consults 回复状态触发器
|
||
|
||
**trg_consult_replies_ai** (AFTER INSERT)
|
||
```sql
|
||
UPDATE consults
|
||
SET status = 'resolved', updated_at = NOW()
|
||
WHERE id = NEW.consult_id;
|
||
```
|
||
|
||
---
|
||
|
||
## 八、数据完整性约束
|
||
|
||
### 8.1 CHECK约束
|
||
|
||
- `products`: `safe_min <= safe_max`
|
||
- `product_prices`: 所有价格 >= 0
|
||
- `inventories`: `quantity >= 0`
|
||
- `sales_order_items`: `quantity > 0, unit_price >= 0, discount_rate BETWEEN 0 AND 100`
|
||
- `purchase_order_items`: `quantity > 0, unit_price >= 0`
|
||
- `payments`: `amount > 0`
|
||
- `other_transactions`: `amount > 0`
|
||
|
||
### 8.2 唯一性约束
|
||
|
||
- `shops`: 无业务层面唯一约束
|
||
- `users`: `phone` 全局唯一、`email` 全局唯一
|
||
- `products`: `(shop_id, barcode)` 唯一、`(template_id, name, model)` 唯一
|
||
- `part_submissions`: `model_unique` 全局唯一、`(template_id, name, model_unique)` 唯一
|
||
- `customers/suppliers`: 无强制唯一约束（允许同名）
|
||
- `accounts`: `(shop_id, name)` 唯一
|
||
- `sales_orders/purchase_orders`: `(shop_id, order_no)` 唯一
|
||
|
||
---
|
||
|
||
## 九、设计说明
|
||
|
||
### 9.1 多租户隔离
|
||
|
||
- 所有业务表必须包含 `shop_id`
|
||
- 全局字典表（单位、类别）使用 `shop_id=0` 表示平台共享
|
||
- 查询必须强制按 `shop_id` 过滤
|
||
- VIP数据可见性：非VIP用户仅显示最近60天（可配置）数据
|
||
|
||
### 9.2 软删除策略
|
||
|
||
- 主要业务表使用 `deleted_at` 字段标记软删除
|
||
- 查询默认过滤 `deleted_at IS NULL`
|
||
- 部分关联表（如订单明细）采用级联删除
|
||
|
||
### 9.3 审计与追踪
|
||
|
||
- 所有表包含 `created_at`, `updated_at` 时间戳
|
||
- 审核表（part_submissions、vip_users）记录审核人和审核时间
|
||
- 库存变动通过 `inventory_movements` 完整留痕
|
||
- 普通管理员申请通过 `normal_admin_audits` 留痕
|
||
|
||
### 9.4 性能优化
|
||
|
||
- 高频查询字段建立复合索引（如 `shop_id + order_time`）
|
||
- `products.search_text` 使用FULLTEXT索引支持全文检索
|
||
- 价格和库存表采用主键为业务主键（product_id）的设计
|
||
- 合理使用外键约束，但不过度使用以避免性能损失
|
||
|
||
### 9.5 扩展性设计
|
||
|
||
- 商品属性通过 `attributes_json` 支持动态扩展
|
||
- 配件模板系统支持类型化参数定义
|
||
- 财务分类通过配置表 `finance_categories` 动态管理
|
||
- 预留 `global_sku_id` 支持平台级商品库
|
||
|
||
---
|
||
|
||
**文档维护说明**：
|
||
- 任何数据库结构变更必须同步更新本文档
|
||
- 执行DDL操作后需标注更新时间
|
||
- 新增表需补充完整的字段说明和索引信息
|
||
- 变更需同步更新 `backend/db/db.sql` 脚本
|
||
|