44 lines
1.3 KiB
SQL
44 lines
1.3 KiB
SQL
CREATE TABLE transport_type (
|
|
id BIGSERIAL PRIMARY KEY,
|
|
name TEXT NOT NULL UNIQUE
|
|
);
|
|
|
|
CREATE TABLE transport (
|
|
id BIGSERIAL PRIMARY KEY,
|
|
model TEXT NOT NULL,
|
|
capacity INTEGER NOT NULL CHECK (capacity > 0),
|
|
type_id BIGINT NOT NULL REFERENCES transport_type(id) ON DELETE CASCADE
|
|
);
|
|
|
|
CREATE TABLE route (
|
|
id BIGSERIAL PRIMARY KEY,
|
|
name TEXT NOT NULL UNIQUE,
|
|
price NUMERIC(10,2) NOT NULL CHECK (price >= 0)
|
|
);
|
|
|
|
CREATE TABLE client (
|
|
id BIGSERIAL PRIMARY KEY,
|
|
name TEXT NOT NULL,
|
|
is_regular BOOLEAN DEFAULT FALSE
|
|
);
|
|
|
|
CREATE TABLE cargo (
|
|
id BIGSERIAL PRIMARY KEY,
|
|
description TEXT NOT NULL,
|
|
weight NUMERIC(10,2) NOT NULL CHECK (weight > 0)
|
|
);
|
|
|
|
CREATE TABLE shipment (
|
|
id BIGSERIAL PRIMARY KEY,
|
|
route_id BIGINT NOT NULL REFERENCES route(id) ON DELETE CASCADE,
|
|
transport_id BIGINT NOT NULL REFERENCES transport(id) ON DELETE CASCADE,
|
|
cargo_id BIGINT NOT NULL REFERENCES cargo(id) ON DELETE CASCADE,
|
|
client_id BIGINT NOT NULL REFERENCES client(id) ON DELETE CASCADE,
|
|
cost NUMERIC(10,2) NOT NULL CHECK (cost >= 0)
|
|
);
|
|
|
|
-- Индексы для оптимизации
|
|
CREATE INDEX idx_shipment_route ON shipment(route_id);
|
|
CREATE INDEX idx_shipment_transport ON shipment(transport_id);
|
|
CREATE INDEX idx_shipment_client ON shipment(client_id);
|