65 lines
2.8 KiB
SQL
65 lines
2.8 KiB
SQL
-- 1. 主表
|
||
CREATE TABLE inspection_basic_information (
|
||
basic_information_id VARCHAR(64) NOT NULL,
|
||
package_category VARCHAR(50) NOT NULL,
|
||
package_level VARCHAR(20) NOT NULL,
|
||
package_name VARCHAR(200) NOT NULL,
|
||
department VARCHAR(100),
|
||
department_id VARCHAR(64),
|
||
user_id VARCHAR(64),
|
||
discount DECIMAL(10,2) DEFAULT 0.00,
|
||
is_disabled BOOLEAN NOT NULL DEFAULT FALSE, -- 改为BOOLEAN类型
|
||
show_package_name BOOLEAN NOT NULL DEFAULT TRUE,
|
||
generate_service_fee BOOLEAN NOT NULL DEFAULT TRUE,
|
||
enable_package_price BOOLEAN NOT NULL DEFAULT TRUE,
|
||
package_amount DECIMAL(10,2) NOT NULL DEFAULT 0.00,
|
||
service_fee DECIMAL(10,2) NOT NULL DEFAULT 0.00,
|
||
lis_group VARCHAR(64),
|
||
blood_volume VARCHAR(50),
|
||
remarks VARCHAR(500),
|
||
org_name VARCHAR(200),
|
||
create_by VARCHAR(64),
|
||
create_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
update_time TIMESTAMP DEFAULT NULL,
|
||
del_flag BOOLEAN NOT NULL DEFAULT FALSE, -- 改为BOOLEAN类型
|
||
CONSTRAINT inspection_basic_information_pkey PRIMARY KEY (basic_information_id)
|
||
);
|
||
|
||
-- 2. 明细表
|
||
CREATE TABLE inspection_package_detail (
|
||
detail_id VARCHAR(64) NOT NULL,
|
||
basic_information_id VARCHAR(64) NOT NULL,
|
||
package_name VARCHAR(200),
|
||
item_name VARCHAR(200) NOT NULL,
|
||
dosage VARCHAR(50),
|
||
route VARCHAR(50),
|
||
frequency VARCHAR(50),
|
||
days INTEGER,
|
||
quantity INTEGER NOT NULL DEFAULT 1,
|
||
unit VARCHAR(20) NOT NULL,
|
||
unit_price DECIMAL(10,2) NOT NULL,
|
||
amount DECIMAL(10,2) NOT NULL,
|
||
service_fee DECIMAL(10,2) NOT NULL DEFAULT 0.00,
|
||
total_amount DECIMAL(10,2) NOT NULL,
|
||
origin VARCHAR(100),
|
||
create_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
update_time TIMESTAMP DEFAULT NULL,
|
||
del_flag BOOLEAN NOT NULL DEFAULT FALSE, -- 改为BOOLEAN类型
|
||
CONSTRAINT inspection_package_detail_pkey PRIMARY KEY (detail_id),
|
||
CONSTRAINT fk_package_detail_basic_information
|
||
FOREIGN KEY (basic_information_id)
|
||
REFERENCES inspection_basic_information(basic_information_id)
|
||
ON DELETE CASCADE -- 或 ON DELETE RESTRICT，根据业务逻辑选择
|
||
);
|
||
|
||
-- 3. 创建索引
|
||
CREATE INDEX idx_basic_information_category ON inspection_basic_information(package_category);
|
||
CREATE INDEX idx_basic_information_level ON inspection_basic_information(package_level);
|
||
CREATE INDEX idx_basic_information_disabled ON inspection_basic_information(is_disabled);
|
||
CREATE INDEX idx_basic_information_del_flag ON inspection_basic_information(del_flag);
|
||
CREATE INDEX idx_detail_basic_information_id ON inspection_package_detail(basic_information_id);
|
||
CREATE INDEX idx_detail_del_flag ON inspection_package_detail(del_flag);
|
||
|
||
-- 4. 添加表注释
|
||
COMMENT ON TABLE inspection_basic_information IS '检验套餐基本信息表';
|
||
COMMENT ON TABLE inspection_package_detail IS '检验套餐明细表'; |