35 lines
1.3 KiB
SQL
35 lines
1.3 KiB
SQL
-- +goose Up
|
|
-- +goose StatementBegin
|
|
-- Reusable, NON-STAFF person master (distinct from contacts). Lets an "other person" be
|
|
-- reused across takeovers without re-entering their data.
|
|
CREATE TABLE IF NOT EXISTS other_people (
|
|
id BINARY(16) NOT NULL,
|
|
name VARCHAR(191) NOT NULL DEFAULT '',
|
|
mobile_phone VARCHAR(64) NOT NULL DEFAULT '',
|
|
email VARCHAR(191) NOT NULL DEFAULT '',
|
|
created_at DATETIME(3) NULL,
|
|
created_by BINARY(16) NULL,
|
|
updated_at DATETIME(3) NULL,
|
|
updated_by BINARY(16) NULL,
|
|
deleted_at DATETIME(3) NULL,
|
|
deleted_by BINARY(16) NULL,
|
|
PRIMARY KEY (id),
|
|
UNIQUE KEY uk_other_people_identity (name, mobile_phone, email),
|
|
KEY idx_other_people_deleted_at (deleted_at)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
|
|
|
-- takeover_other_people becomes a pivot + snapshot: reference the master, keep the snapshot.
|
|
ALTER TABLE takeover_other_people
|
|
ADD COLUMN other_person_id BINARY(16) NULL AFTER takeover_id,
|
|
ADD KEY idx_takeover_other_people_other_person_id (other_person_id);
|
|
-- +goose StatementEnd
|
|
|
|
-- +goose Down
|
|
-- +goose StatementBegin
|
|
ALTER TABLE takeover_other_people
|
|
DROP KEY idx_takeover_other_people_other_person_id,
|
|
DROP COLUMN other_person_id;
|
|
|
|
DROP TABLE IF EXISTS other_people;
|
|
-- +goose StatementEnd
|