建表设计稿(不直接执行)
下载原文件-- PCBJIA Mall 商城 + RBAC 建表设计稿(PostgreSQL 方言)
-- 由 MySQL 8.0 原文改写:去引擎/字符集,自增改 identity,datetime 改 timestamptz,tinyint 布尔改 boolean,ON UPDATE 改触发器。
-- 仅作评审依据,不直接执行;真实建表由平台内置能力完成。
create table if not exists customer (
id bigint generated by default as identity primary key,
customer_no varchar(32) not null,
company_name varchar(128) not null,
contact_name varchar(64) not null,
mobile varchar(20),
email varchar(128),
customer_level smallint not null default 1 check (customer_level between 1 and 4),
credit_limit numeric(18,4) not null default 0,
credit_balance numeric(18,4) not null default 0,
tax_type smallint default 1 check (tax_type in (1,2,3)),
status smallint not null default 1 check (status in (0,1)),
is_deleted boolean not null default false,
created_at timestamptz not null default now(),
updated_at timestamptz not null default now(),
creator varchar(64),
updater varchar(64)
);
create unique index if not exists uk_customer_no on customer (customer_no) where is_deleted = false;
create index if not exists idx_company_name on customer (company_name) where is_deleted = false;
create index if not exists idx_customer_mobile on customer (mobile) where is_deleted = false;
create table if not exists supplier (
id bigint generated by default as identity primary key,
supplier_no varchar(32) not null,
supplier_name varchar(128) not null,
supplier_level smallint not null default 4 check (supplier_level between 1 and 4),
contact varchar(64),
phone varchar(20),
supplier_type smallint not null check (supplier_type between 1 and 4),
settlement_cycle integer default 30,
status smallint not null default 1 check (status in (0,1)),
qualification_json jsonb not null default '[]'::jsonb,
score numeric(5,2) not null default 0,
is_deleted boolean not null default false,
created_at timestamptz not null default now(),
updated_at timestamptz not null default now(),
creator varchar(64),
updater varchar(64)
);
create unique index if not exists uk_supplier_no on supplier (supplier_no) where is_deleted = false;
create index if not exists idx_supplier_level on supplier (supplier_level);
create index if not exists idx_supplier_type on supplier (supplier_type);
create table if not exists component (
id bigint generated by default as identity primary key,
sku_code varchar(64) not null,
mpn varchar(128) not null,
brand varchar(64),
package varchar(64),
spec text,
datasheet_url varchar(255),
stock_qty integer not null default 0,
moq integer not null default 1,
lead_time integer,
price_json jsonb not null default '[]'::jsonb,
supplier_id bigint not null references supplier (id),
is_rohs boolean not null default true,
is_active boolean not null default true,
is_deleted boolean not null default false,
created_at timestamptz not null default now(),
updated_at timestamptz not null default now()
);
create unique index if not exists uk_sku_code on component (sku_code) where is_deleted = false;
create index if not exists idx_mpn on component (mpn);
create index if not exists idx_brand_package on component (brand, package);
create index if not exists idx_component_supplier on component (supplier_id);
create table if not exists pcb_quote_template (
id bigint generated by default as identity primary key,
template_name varchar(128) not null,
layer_min smallint not null,
layer_max smallint not null,
thickness numeric(4,2),
surface_finish varchar(32),
min_line_width numeric(5,3),
min_line_spacing numeric(5,3),
min_hole numeric(5,3),
base_price numeric(18,4) not null default 0,
unit varchar(16) not null default 'pcs',
supplier_id bigint not null references supplier (id),
is_high_tech boolean not null default false,
status smallint not null default 1 check (status in (0,1)),
is_deleted boolean not null default false,
created_at timestamptz not null default now(),
updated_at timestamptz not null default now()
);
create index if not exists idx_template_supplier on pcb_quote_template (supplier_id);
create index if not exists idx_template_layer on pcb_quote_template (layer_min, layer_max);
create table if not exists bom_order (
id bigint generated by default as identity primary key,
bom_no varchar(32) not null,
customer_id bigint not null references customer (id),
title varchar(128) not null,
total_qty integer not null default 0,
total_amount numeric(18,4) not null default 0,
status smallint not null default 1 check (status between 1 and 5),
dfm_check_result jsonb,
remark text,
is_deleted boolean not null default false,
created_at timestamptz not null default now(),
updated_at timestamptz not null default now()
);
create unique index if not exists uk_bom_no on bom_order (bom_no) where is_deleted = false;
create index if not exists idx_bom_customer on bom_order (customer_id);
create index if not exists idx_bom_status on bom_order (status);
create table if not exists bom_item (
id bigint generated by default as identity primary key,
bom_id bigint not null references bom_order (id) on delete cascade,
component_id bigint references component (id),
mpn_input varchar(128) not null,
brand_input varchar(64),
qty integer not null,
unit_price numeric(18,4) not null default 0,
subtotal numeric(18,4) not null default 0,
match_status smallint not null default 0 check (match_status between 0 and 3),
remark varchar(255),
is_deleted boolean not null default false,
created_at timestamptz not null default now(),
updated_at timestamptz not null default now()
);
create index if not exists idx_bom_item_bom on bom_item (bom_id);
create index if not exists idx_bom_item_component on bom_item (component_id);
create table if not exists mall_order (
id bigint generated by default as identity primary key,
order_no varchar(32) not null,
customer_id bigint not null references customer (id),
order_type smallint not null check (order_type between 1 and 4),
bom_id bigint references bom_order (id),
pcb_quote_id bigint,
total_amount numeric(18,4) not null default 0,
pay_amount numeric(18,4) not null default 0,
credit_used numeric(18,4) not null default 0,
pay_status smallint not null default 0 check (pay_status between 0 and 3),
order_status smallint not null default 1 check (order_status between 1 and 6),
delivery_no varchar(64),
delivery_addr text,
is_deleted boolean not null default false,
created_at timestamptz not null default now(),
updated_at timestamptz not null default now()
);
create unique index if not exists uk_mall_order_no on mall_order (order_no) where is_deleted = false;
create index if not exists idx_mall_order_customer on mall_order (customer_id);
create index if not exists idx_mall_order_status on mall_order (order_status);
create index if not exists idx_mall_order_bom on mall_order (bom_id);
create table if not exists settle_bill (
id bigint generated by default as identity primary key,
bill_no varchar(32) not null,
order_id bigint not null references mall_order (id),
supplier_id bigint not null references supplier (id),
bill_amount numeric(18,4) not null,
platform_fee numeric(18,4) not null default 0,
supplier_receive numeric(18,4) not null default 0,
settle_status smallint not null default 0 check (settle_status between 0 and 2),
settle_date date,
invoice_no varchar(64),
is_deleted boolean not null default false,
created_at timestamptz not null default now(),
updated_at timestamptz not null default now()
);
create unique index if not exists uk_bill_no on settle_bill (bill_no) where is_deleted = false;
create index if not exists idx_settle_order on settle_bill (order_id);
create index if not exists idx_settle_supplier on settle_bill (supplier_id);
create table if not exists car_record (
id bigint generated by default as identity primary key,
car_no varchar(32) not null,
order_id bigint references mall_order (id),
supplier_id bigint not null references supplier (id),
customer_id bigint references customer (id),
failure_mode varchar(128) not null,
description text not null,
root_cause text,
correct_action text,
status smallint not null default 1 check (status between 1 and 5),
close_time timestamptz,
attach_json jsonb not null default '[]'::jsonb,
is_deleted boolean not null default false,
created_at timestamptz not null default now(),
updated_at timestamptz not null default now()
);
create unique index if not exists uk_car_no on car_record (car_no) where is_deleted = false;
create index if not exists idx_car_order on car_record (order_id);
create index if not exists idx_car_supplier on car_record (supplier_id);
create table if not exists message_center (
id bigint generated by default as identity primary key,
receiver_type smallint not null check (receiver_type between 1 and 3),
receiver_id bigint not null,
msg_type varchar(32) not null,
title varchar(128) not null,
content text not null,
link_url varchar(255),
is_read boolean not null default false,
is_deleted boolean not null default false,
created_at timestamptz not null default now()
);
create index if not exists idx_message_receiver on message_center (receiver_type, receiver_id);
create index if not exists idx_message_read on message_center (is_read);
create table if not exists sys_menu (
id bigint generated by default as identity primary key,
parent_id bigint not null default 0,
menu_name varchar(64) not null,
menu_type smallint not null check (menu_type in (1,2,3)),
route_path varchar(128),
component varchar(128),
permission_key varchar(128),
sort integer not null default 0,
icon varchar(64),
status smallint not null default 1 check (status in (0,1)),
is_deleted boolean not null default false,
created_at timestamptz not null default now(),
updated_at timestamptz not null default now()
);
create index if not exists idx_menu_parent on sys_menu (parent_id);
create index if not exists idx_menu_permission on sys_menu (permission_key);
create table if not exists sys_role (
id bigint generated by default as identity primary key,
role_name varchar(64) not null,
role_code varchar(64) not null,
remark varchar(255),
status smallint not null default 1 check (status in (0,1)),
is_deleted boolean not null default false,
created_at timestamptz not null default now(),
updated_at timestamptz not null default now()
);
create unique index if not exists uk_role_code on sys_role (role_code) where is_deleted = false;
create table if not exists sys_role_menu (
id bigint generated by default as identity primary key,
role_id bigint not null references sys_role (id) on delete cascade,
menu_id bigint not null references sys_menu (id) on delete cascade,
is_deleted boolean not null default false,
created_at timestamptz not null default now()
);
create unique index if not exists uk_role_menu on sys_role_menu (role_id, menu_id) where is_deleted = false;
create table if not exists sys_user (
id bigint generated by default as identity primary key,
username varchar(64) not null,
password varchar(128) not null,
nickname varchar(64) not null,
phone varchar(20),
email varchar(128),
user_type smallint not null check (user_type in (1,2,3)),
bind_ref_id bigint,
status smallint not null default 1 check (status in (0,1)),
is_deleted boolean not null default false,
created_at timestamptz not null default now(),
updated_at timestamptz not null default now()
);
create unique index if not exists uk_sys_username on sys_user (username) where is_deleted = false;
create index if not exists idx_sys_user_type on sys_user (user_type);
create index if not exists idx_sys_user_bind on sys_user (bind_ref_id);
create table if not exists sys_user_role (
id bigint generated by default as identity primary key,
user_id bigint not null references sys_user (id) on delete cascade,
role_id bigint not null references sys_role (id) on delete cascade,
is_deleted boolean not null default false,
created_at timestamptz not null default now()
);
create unique index if not exists uk_user_role on sys_user_role (user_id, role_id) where is_deleted = false;
create or replace function touch_updated_at() returns trigger as $$
begin
new.updated_at = now();
return new;
end;
$$ language plpgsql;
do $$
declare t text;
begin
foreach t in array array['customer','supplier','component','pcb_quote_template','bom_order','bom_item','mall_order','settle_bill','car_record','sys_menu','sys_role','sys_user']
loop
execute format('drop trigger if exists trg_%1$s_touch on %1$s', t);
execute format('create trigger trg_%1$s_touch before update on %1$s for each row execute function touch_updated_at()', t);
end loop;
end $$;
insert into sys_role (role_name, role_code, remark, status) values
('超级管理员','super_admin','拥有全部权限,不可删除',1),
('平台运营','mall_operator','商城首页、商品、BOM 审核、订单跟进',1),
('报价工程师','quote_engineer','PCB 工艺报价、BOM 核价、DFM 审核',1),
('品质 QE','qe_quality','CAR 开立、8D 跟踪、供应商品质考核',1),
('财务会计','finance','对账、分账、发票、授信台账',1),
('供应商管理员','supplier_admin','供应商后台:订单、交货、对账',1),
('客户管理员','customer_admin','客户子账号,查看自家 BOM、订单、CAR',1)
on conflict do nothing;
insert into sys_menu (parent_id, menu_name, menu_type, route_path, permission_key, sort, status) values
(0,'商城首页',1,'/mall',null,1,1),
(1,'物料管理',2,'/mall/component','mall:component:list',1,1),
(1,'BOM 配单',2,'/mall/bom','mall:bom:list',2,1),
(1,'PCB 报价',2,'/mall/pcbquote','mall:pcbquote:list',3,1),
(1,'订单管理',2,'/mall/order','mall:order:list',4,1),
(0,'供应链',1,'/supply',null,2,1),
(6,'供应商管理',2,'/supply/supplier','supply:supplier:list',1,1),
(0,'品质管理',1,'/quality',null,3,1),
(8,'CAR 异常单',2,'/quality/car','quality:car:list',1,1),
(0,'财务中心',1,'/finance',null,4,1),
(10,'结算对账',2,'/finance/settle','finance:settle:list',1,1),
(0,'系统设置',1,'/system',null,99,1),
(12,'角色权限',2,'/system/role','system:role:list',1,1);
