292 lines
15 KiB
Markdown
292 lines
15 KiB
Markdown
数据库结构文档（生产同步版）
|
||
|
||
说明
|
||
- 本文档依据当前生产库 `partsinquiry` 实时结构生成，确保与线上一致。
|
||
- 结构脚本来源：`backend/db/db.sql`（已由 mysqldump 同步）。如需精确细节（触发器/索引选项/字符集等），以该 SQL 文件为准。
|
||
- 下文为核心业务表的字段、索引与外键说明；其余表已在 `backend/db/db.sql` 完整记录。
|
||
|
||
连接信息（只读）
|
||
- Host: mysql.tonaspace.com
|
||
- Database: partsinquiry
|
||
- Collation: utf8mb4_0900_ai_ci
|
||
|
||
目录（表清单）
|
||
- accounts
|
||
- admins
|
||
- attachments
|
||
- consults / consult_replies
|
||
- customers
|
||
- email_codes
|
||
- finance_categories
|
||
- global_skus
|
||
- inventories / inventory_movements
|
||
- normal_admin_audits
|
||
- notices
|
||
- other_transactions
|
||
- part_attribute_dictionary / part_attribute_templates / part_category_attributes / part_categories
|
||
- part_submissions / part_templates / part_template_params
|
||
- payments
|
||
- product_aliases / product_categories / product_images / product_prices / product_units / products
|
||
- purchase_orders / purchase_order_items / purchase_return_orders / purchase_return_order_items
|
||
- sales_orders / sales_order_items / sales_return_orders / sales_return_order_items
|
||
- shops / suppliers / users / user_identities / system_parameters / sms_codes / vip_price / vip_recharges / vip_users / wechat_sessions
|
||
|
||
—— 以下为核心表详情（节选） ——
|
||
|
||
### 表：accounts
|
||
|
||
字段
|
||
|
||
| Column Name | Data Type | Nullable | Default | Comment |
|
||
| ----------- | ---------------- | -------- | -------------- | ------- |
|
||
| id | bigint unsigned | NO | AUTO_INCREMENT | |
|
||
| shop_id | bigint unsigned | NO | | |
|
||
| user_id | bigint unsigned | NO | | |
|
||
| name | varchar(64) | NO | | |
|
||
| type | enum('cash','bank','alipay','wechat','other') | NO | cash | |
|
||
| bank_name | varchar(64) | YES | | |
|
||
| bank_account| varchar(64) | YES | | |
|
||
| balance | decimal(18,2) | NO | 0.00 | |
|
||
| status | tinyint unsigned | NO | 1 | |
|
||
| created_at | timestamp | NO | CURRENT_TIMESTAMP | |
|
||
| updated_at | timestamp | NO | CURRENT_TIMESTAMP ON UPDATE | |
|
||
| deleted_at | datetime | YES | | |
|
||
|
||
Indexes
|
||
- PRIMARY KEY: `id`
|
||
- UNIQUE KEY: `ux_accounts_shop_name` (`shop_id`,`name`)
|
||
- KEY: `idx_accounts_shop` (`shop_id`)
|
||
|
||
Foreign Keys
|
||
- `fk_accounts_shop`: `shop_id` → `shops(id)`
|
||
- `fk_accounts_user`: `user_id` → `users(id)`
|
||
|
||
---
|
||
|
||
### 表：customers
|
||
|
||
字段
|
||
|
||
| Column Name | Data Type | Nullable | Default | Comment |
|
||
| ------------ | ------------------------------------------- | -------- | ------- | ----------------------------- |
|
||
| id | bigint unsigned | NO | AI | |
|
||
| shop_id | bigint unsigned | NO | | |
|
||
| user_id | bigint unsigned | NO | | |
|
||
| name | varchar(120) | NO | | |
|
||
| phone | varchar(32) | YES | | |
|
||
| address | varchar(255) | YES | | |
|
||
| mobile | varchar(32) | YES | | |
|
||
| contact_name | varchar(64) | YES | | |
|
||
| price_level | enum('零售价','批发价','大单报价') | NO | 零售价 | 默认售价列 |
|
||
| status | tinyint unsigned | NO | 1 | |
|
||
| ar_opening | decimal(18,2) | NO | 0.00 | |
|
||
| remark | varchar(255) | YES | | |
|
||
| created_at | timestamp | NO | CURRENT_TIMESTAMP | |
|
||
| updated_at | timestamp | NO | CURRENT_TIMESTAMP ON UPDATE | |
|
||
| deleted_at | datetime | YES | | |
|
||
|
||
Indexes
|
||
- PRIMARY KEY: `id`
|
||
- KEY: `idx_customers_shop` (`shop_id`)
|
||
- KEY: `idx_customers_phone` (`phone`)
|
||
- KEY: `idx_customers_mobile` (`mobile`)
|
||
|
||
Foreign Keys
|
||
- `fk_customers_shop`: `shop_id` → `shops(id)`
|
||
- `fk_customers_user`: `user_id` → `users(id)`
|
||
|
||
---
|
||
|
||
### 表：products
|
||
|
||
字段
|
||
|
||
| Column Name | Data Type | Nullable | Default | Comment |
|
||
| ---------------------- | ------------------- | -------- | ------- | ---------------------------------------- |
|
||
| id | bigint unsigned | NO | AI | |
|
||
| shop_id | bigint unsigned | NO | | |
|
||
| user_id | bigint unsigned | NO | | |
|
||
| name | varchar(120) | NO | | |
|
||
| category_id | bigint unsigned | YES | | |
|
||
| template_id | bigint unsigned | YES | | |
|
||
| brand | varchar(64) | YES | | |
|
||
| model | varchar(64) | YES | | |
|
||
| spec | varchar(128) | YES | | |
|
||
| origin | varchar(64) | YES | | |
|
||
| barcode | varchar(32) | YES | | |
|
||
| dedupe_key | varchar(512) | YES | | |
|
||
| alias | varchar(120) | YES | | |
|
||
| is_blacklisted | tinyint(1) | NO | 0 | |
|
||
| description | text | YES | | |
|
||
| global_sku_id | bigint unsigned | YES | | |
|
||
| source_submission_id | bigint unsigned | YES | | |
|
||
| attributes_json | json | YES | | |
|
||
| safe_min | decimal(18,3) | YES | | |
|
||
| safe_max | decimal(18,3) | YES | | |
|
||
| search_text | text | YES | | 供全文检索的聚合字段 |
|
||
| created_at | timestamp | NO | CURRENT_TIMESTAMP | |
|
||
| updated_at | timestamp | NO | CURRENT_TIMESTAMP ON UPDATE | |
|
||
| deleted_at | datetime | YES | | |
|
||
| is_active | tinyint (generated) | YES | | 生成列：deleted_at 为空则 1，否则 0 |
|
||
|
||
Indexes
|
||
- PRIMARY KEY: `id`
|
||
- UNIQUE KEY: `ux_products_template_name_model` (`template_id`,`name`,`model`)
|
||
- UNIQUE KEY: `ux_products_shop_barcode_live` (`shop_id`,`barcode`,`is_active`)
|
||
- FULLTEXT: `ft_products_search` (`name`,`brand`,`model`,`spec`,`search_text`)
|
||
- KEY: `idx_products_shop` (`shop_id`), `idx_products_category` (`category_id`), `idx_products_template` (`template_id`), `idx_products_dedupe` (`dedupe_key`), `idx_products_deleted_at` (`deleted_at`), `idx_products_shop_blacklist` (`shop_id`,`is_blacklisted`)
|
||
|
||
Foreign Keys
|
||
- `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)`
|
||
- `fk_products_submission`: `source_submission_id` → `part_submissions(id)`
|
||
|
||
触发器
|
||
- BEFORE INSERT `trg_products_bi`: 设置 `search_text`
|
||
- BEFORE UPDATE `trg_products_au`: 设置 `search_text`
|
||
|
||
---
|
||
|
||
### 表：sales_orders
|
||
|
||
字段（摘录）
|
||
|
||
| Column Name | Data Type | Nullable | Default | Comment |
|
||
| ----------- | ---------------- | -------- | ------- | ------- |
|
||
| id | bigint unsigned | NO | AI | |
|
||
| shop_id | bigint unsigned | NO | | |
|
||
| user_id | bigint unsigned | NO | | 创建人 |
|
||
| customer_id | bigint unsigned | YES | | |
|
||
| 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 | | |
|
||
| created_at | timestamp | NO | CURRENT_TIMESTAMP | |
|
||
| updated_at | timestamp | NO | CURRENT_TIMESTAMP ON UPDATE | |
|
||
| deleted_at | datetime | YES | | |
|
||
|
||
Indexes
|
||
- PRIMARY KEY: `id`
|
||
- UNIQUE KEY: `ux_sales_order_no` (`shop_id`,`order_no`)
|
||
- KEY: `idx_sales_shop_time` (`shop_id`,`order_time`), `idx_sales_customer` (`customer_id`)
|
||
|
||
Foreign Keys
|
||
- `fk_sales_shop`: `shop_id` → `shops(id)`
|
||
- `fk_sales_user`: `user_id` → `users(id)`
|
||
- `fk_sales_customer`: `customer_id` → `customers(id)`
|
||
|
||
---
|
||
|
||
### 表：purchase_orders
|
||
|
||
字段（摘录）
|
||
|
||
| Column Name | Data Type | Nullable | Default | Comment |
|
||
| ----------- | ---------------- | -------- | ------- | ------- |
|
||
| id | bigint unsigned | NO | AI | |
|
||
| shop_id | bigint unsigned | NO | | |
|
||
| user_id | bigint unsigned | NO | | |
|
||
| supplier_id | bigint unsigned | YES | | |
|
||
| 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 | | |
|
||
| created_at | timestamp | NO | CURRENT_TIMESTAMP | |
|
||
| updated_at | timestamp | NO | CURRENT_TIMESTAMP ON UPDATE | |
|
||
| deleted_at | datetime | YES | | |
|
||
|
||
Indexes
|
||
- PRIMARY KEY: `id`
|
||
- UNIQUE KEY: `ux_purchase_order_no` (`shop_id`,`order_no`)
|
||
- KEY: `idx_purchase_shop_time` (`shop_id`,`order_time`), `idx_purchase_supplier` (`supplier_id`)
|
||
|
||
Foreign Keys
|
||
- `fk_purchase_shop`: `shop_id` → `shops(id)`
|
||
- `fk_purchase_user`: `user_id` → `users(id)`
|
||
- `fk_purchase_supplier`: `supplier_id` → `suppliers(id)`
|
||
|
||
---
|
||
|
||
### 表：part_submissions（配件提交）
|
||
|
||
字段（摘录）
|
||
|
||
| Column Name | Data Type | Nullable | Default | Comment |
|
||
| ----------------- | ---------------- | -------- | ------- | ------------------ |
|
||
| id | bigint unsigned | NO | AI | |
|
||
| shop_id | bigint unsigned | NO | | |
|
||
| user_id | bigint unsigned | NO | | |
|
||
| name | varchar(120) | YES | | |
|
||
| model_unique | varchar(128) | NO | | 型号（唯一） |
|
||
| brand | varchar(64) | YES | | |
|
||
| spec | varchar(128) | YES | | |
|
||
| template_id | bigint unsigned | YES | | |
|
||
| category_id | bigint unsigned | YES | | |
|
||
| status | enum('pending','approved','rejected') | NO | pending | |
|
||
| product_id | bigint unsigned | YES | | |
|
||
| global_sku_id | bigint unsigned | YES | | |
|
||
| barcode | varchar(64) | YES | | |
|
||
| dedupe_key | varchar(512) | YES | | |
|
||
| images/tags/attributes | json | YES | | |
|
||
| created_at/updated_at | timestamp | NO | CURRENT_TIMESTAMP | |
|
||
|
||
Indexes（摘录）
|
||
- PRIMARY KEY: `id`
|
||
- UNIQUE KEY: `ux_part_model_unique` (`model_unique`)
|
||
- UNIQUE KEY: `ux_ps_template_name_model` (`template_id`,`name`,`model_unique`)
|
||
- KEY: `idx_sub_shop_status` (`shop_id`,`status`,`updated_at`), `idx_part_brand_model` (`brand`,`model_unique`), `idx_ps_dedupe` (`dedupe_key`)
|
||
|
||
Foreign Keys（摘录）
|
||
- `fk_part_shop`: `shop_id` → `shops(id)`
|
||
- `fk_part_user`: `user_id` → `users(id)`
|
||
- `fk_part_submission_product`: `product_id` → `products(id)`
|
||
- `fk_part_submission_global_sku`: `global_sku_id` → `global_skus(id)`
|
||
- `fk_ps_template`: `template_id` → `part_templates(id)`
|
||
|
||
---
|
||
|
||
### 表：product_aliases（别名）
|
||
|
||
字段（摘录）
|
||
|
||
| Column Name | Data Type | Nullable | Default | Comment |
|
||
| ----------- | ---------------- | -------- | ------- | ------- |
|
||
| id | bigint unsigned | NO | AI | |
|
||
| product_id | bigint unsigned | NO | | |
|
||
| alias | varchar(120) | NO | | |
|
||
| shop_id | bigint unsigned | NO | | |
|
||
| user_id | bigint unsigned | NO | | |
|
||
| created_at | timestamp | NO | CURRENT_TIMESTAMP | |
|
||
| deleted_at | datetime | YES | | |
|
||
|
||
Indexes
|
||
- PRIMARY KEY: `id`
|
||
- UNIQUE KEY: `ux_product_alias` (`product_id`,`alias`)
|
||
- KEY: `idx_product_alias_product` (`product_id`)
|
||
|
||
Foreign Keys
|
||
- `fk_alias_product`: `product_id` → `products(id)`
|
||
- `fk_alias_shop`: `shop_id` → `shops(id)`
|
||
- `fk_alias_user`: `user_id` → `users(id)`
|
||
|
||
触发器
|
||
- AFTER INSERT/UPDATE/DELETE：`trg_palias_ai`/`trg_palias_au`/`trg_palias_ad` 用于回写 `products.search_text`
|
||
|
||
---
|
||
|
||
其余表
|
||
- 所有其它表（见“目录”）的字段/索引/外键与生产一致，完整 DDL 已收录在 `backend/db/db.sql`。若需要将本文件扩展为全量字段清单，可由该 SQL 一键生成（建议脚本化生成避免手工遗漏）。
|
||
|
||
生成时间
|
||
- 本文档基于线上结构同步，生成于本地更新时刻。
|
||
|
||
|
||
|