下载中心/建表设计稿(不直接执行)

建表设计稿(不直接执行)

下载原文件
-- 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);