You can not select more than 25 topics
Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
86 lines
3.4 KiB
86 lines
3.4 KiB
1 month ago
|
-- 涉案财物业务表（PostgreSQL / Kingbase）
|
||
|
|
|
||
|
|
CREATE TABLE IF NOT EXISTS t_property (
|
||
|
|
property_id BIGSERIAL PRIMARY KEY,
|
||
|
|
property_no VARCHAR(64) NOT NULL,
|
||
|
|
property_name VARCHAR(200) NOT NULL,
|
||
|
|
property_category VARCHAR(32) NOT NULL,
|
||
|
|
measure VARCHAR(32) NOT NULL,
|
||
|
|
quantity INTEGER NOT NULL DEFAULT 1,
|
||
|
|
unit VARCHAR(32) DEFAULT '件',
|
||
|
|
valuation NUMERIC(18, 2) DEFAULT 0,
|
||
|
|
location VARCHAR(200),
|
||
|
|
case_no VARCHAR(64),
|
||
|
|
case_name VARCHAR(200),
|
||
|
|
legal_doc_no VARCHAR(64),
|
||
|
|
features TEXT,
|
||
|
|
owner VARCHAR(64),
|
||
|
|
submitter VARCHAR(64),
|
||
|
|
custodian VARCHAR(64),
|
||
|
|
barcode VARCHAR(64),
|
||
|
|
receive_time TIMESTAMP,
|
||
|
|
status VARCHAR(32) NOT NULL,
|
||
|
|
scope_dept VARCHAR(100),
|
||
|
|
create_user_id BIGINT,
|
||
|
|
create_time TIMESTAMP DEFAULT NOW(),
|
||
|
|
update_time TIMESTAMP DEFAULT NOW(),
|
||
|
|
deleted_flag BOOLEAN DEFAULT FALSE
|
||
|
|
);
|
||
|
|
CREATE UNIQUE INDEX IF NOT EXISTS uk_t_property_no ON t_property(property_no) WHERE deleted_flag = FALSE;
|
||
|
|
|
||
|
|
CREATE TABLE IF NOT EXISTS t_property_attachment (
|
||
|
|
attachment_id BIGSERIAL PRIMARY KEY,
|
||
|
|
property_id BIGINT NOT NULL,
|
||
|
|
file_name VARCHAR(255),
|
||
|
|
file_key VARCHAR(255),
|
||
|
|
file_size BIGINT,
|
||
|
|
deleted_flag BOOLEAN DEFAULT FALSE,
|
||
|
|
create_time TIMESTAMP DEFAULT NOW()
|
||
|
|
);
|
||
|
|
CREATE INDEX IF NOT EXISTS idx_t_property_attachment_pid ON t_property_attachment(property_id);
|
||
|
|
|
||
|
|
CREATE TABLE IF NOT EXISTS t_property_transfer (
|
||
|
|
transfer_id BIGSERIAL PRIMARY KEY,
|
||
|
|
property_id BIGINT NOT NULL,
|
||
|
|
transfer_no VARCHAR(64) NOT NULL,
|
||
|
|
invoker VARCHAR(64),
|
||
|
|
invoke_time TIMESTAMP,
|
||
|
|
invoke_reason TEXT,
|
||
|
|
invoke_deadline_days INTEGER,
|
||
|
|
invoke_deadline_end TIMESTAMP,
|
||
|
|
audit_scope VARCHAR(32),
|
||
|
|
audit_role_label VARCHAR(64),
|
||
|
|
audit_dept VARCHAR(100),
|
||
|
|
audit_display VARCHAR(100),
|
||
|
|
audit_time TIMESTAMP,
|
||
|
|
auditor_name VARCHAR(64),
|
||
|
|
status VARCHAR(32) NOT NULL,
|
||
|
|
create_time TIMESTAMP DEFAULT NOW(),
|
||
|
|
update_time TIMESTAMP DEFAULT NOW()
|
||
|
|
);
|
||
|
|
CREATE UNIQUE INDEX IF NOT EXISTS uk_t_property_transfer_no ON t_property_transfer(transfer_no);
|
||
|
|
CREATE INDEX IF NOT EXISTS idx_t_property_transfer_pid ON t_property_transfer(property_id);
|
||
|
|
|
||
|
|
CREATE TABLE IF NOT EXISTS t_property_return (
|
||
|
|
return_id BIGSERIAL PRIMARY KEY,
|
||
|
|
transfer_id BIGINT NOT NULL,
|
||
|
|
property_id BIGINT NOT NULL,
|
||
|
|
return_time TIMESTAMP,
|
||
|
|
returner VARCHAR(64),
|
||
|
|
return_remark TEXT,
|
||
|
|
create_time TIMESTAMP DEFAULT NOW()
|
||
|
|
);
|
||
|
|
CREATE INDEX IF NOT EXISTS idx_t_property_return_pid ON t_property_return(property_id);
|
||
|
|
|
||
|
|
CREATE TABLE IF NOT EXISTS t_property_checkout (
|
||
|
|
checkout_id BIGSERIAL PRIMARY KEY,
|
||
|
|
property_id BIGINT NOT NULL,
|
||
|
|
checkout_no VARCHAR(64) NOT NULL,
|
||
|
|
checkout_time TIMESTAMP,
|
||
|
|
checkout_reason TEXT,
|
||
|
|
checkout_operator VARCHAR(64),
|
||
|
|
create_time TIMESTAMP DEFAULT NOW()
|
||
|
|
);
|
||
|
|
CREATE UNIQUE INDEX IF NOT EXISTS uk_t_property_checkout_no ON t_property_checkout(checkout_no);
|
||
|
|
CREATE INDEX IF NOT EXISTS idx_t_property_checkout_pid ON t_property_checkout(property_id);
|