58 lines
2.0 KiB
SQL
58 lines
2.0 KiB
SQL
-- +goose Up
|
|
-- Create insurance_patient_data table
|
|
|
|
CREATE TABLE IF NOT EXISTS insurance_patient_data (
|
|
id BINARY(16) NOT NULL PRIMARY KEY,
|
|
health_insurance_companies_id BINARY(16) NULL,
|
|
federal_state_id BINARY(16) NULL,
|
|
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
created_by BINARY(16) NULL,
|
|
updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
|
updated_by BINARY(16) NULL,
|
|
deleted_at DATETIME NULL,
|
|
deleted_by BINARY(16) NULL,
|
|
|
|
KEY idx_insurance_patient_data_health_insurance_companies_id (health_insurance_companies_id),
|
|
KEY idx_insurance_patient_data_federal_state_id (federal_state_id),
|
|
KEY idx_insurance_patient_data_deleted_at (deleted_at)
|
|
);
|
|
|
|
-- Create patient_data table
|
|
|
|
CREATE TABLE IF NOT EXISTS patient_data (
|
|
id BINARY(16) NOT NULL PRIMARY KEY,
|
|
family_name VARCHAR(128) NOT NULL,
|
|
first_name VARCHAR(128) NOT NULL,
|
|
opc_id BINARY(16) NULL,
|
|
birth_date DATE NULL,
|
|
gender ENUM('male','female') NOT NULL,
|
|
street VARCHAR(255) NULL,
|
|
svnr VARCHAR(64) NULL,
|
|
email VARCHAR(191) NULL,
|
|
phone VARCHAR(64) NULL,
|
|
co_insured TINYINT(1) NOT NULL DEFAULT 0,
|
|
place_id BINARY(16) NULL,
|
|
nationality_id BINARY(16) NULL,
|
|
insurance_patient_data_id BINARY(16) NULL,
|
|
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
created_by BINARY(16) NULL,
|
|
updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
|
updated_by BINARY(16) NULL,
|
|
deleted_at DATETIME NULL,
|
|
deleted_by BINARY(16) NULL,
|
|
|
|
KEY idx_patient_data_opc_id (opc_id),
|
|
KEY idx_patient_data_place_id (place_id),
|
|
KEY idx_patient_data_nationality_id (nationality_id),
|
|
KEY idx_patient_data_insurance_patient_data_id (insurance_patient_data_id),
|
|
KEY idx_patient_data_deleted_at (deleted_at),
|
|
|
|
CONSTRAINT fk_patient_data_insurance_patient_data
|
|
FOREIGN KEY (insurance_patient_data_id) REFERENCES insurance_patient_data (id)
|
|
ON UPDATE CASCADE ON DELETE SET NULL
|
|
);
|
|
|
|
-- +goose Down
|
|
DROP TABLE IF EXISTS patient_data;
|
|
DROP TABLE IF EXISTS insurance_patient_data;
|