Examples

CREATE TABLE ap_car (
    cr_id               BIGINT PRIMARY KEY AUTO_INCREMENT,
    cr_license_plate    VARCHAR(20) NOT NULL UNIQUE,
    cr_brand            VARCHAR(50) NOT NULL,
    cr_model            VARCHAR(50) NOT NULL,
    cr_year             SMALLINT NOT NULL,
    cr_seats            TINYINT NOT NULL DEFAULT 4,
    cr_daily_rate       DECIMAL(4,2),
    cr_color            VARCHAR(30),
    cr_full_name        VARCHAR(128) GENERATED ALWAYS AS CONCAT(cr_brand, '-', cr_model, '-', cr_year),
    cr_status           ENUM('available', 'rented', 'maintenance') DEFAULT 'available',
    cr_dimensions       INTEGER ARRAY[4],
    cr_created_at       TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
                        NOT NULL,
    cr_updated_at       TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
                        ON UPDATE CURRENT_TIMESTAMP
                        NOT NULL
);

CREATE INDEX ix_ap_car__cr_model ON ap_car(cr_model);
CREATE INDEX ix_ap_car__cr_color ON ap_car(cr_color);

CREATE TABLe ap_car_feature (
    cf_cr_id            BIGINT NOT NULL
                        CONSTRAINT fk_ap_car_feature__ap_car REFERENCES ap_car(cr_id)
                            ON DELETE CASCADE
                            ON UPDATE RESTRICT,
    cf_feature_name     VARCHAR(32) NOT NULL,
    CONSTRAINT pk_ap_car_feature PRIMARY KEY(cf_cr_id, cf_feature_name),
    CONSTRAINT unq_ap_car_feature__cf_cr_id_cf_feature_name
        UNIQUE(cf_cr_id, cf_feature_name)
);

CREATE TABLE ap_member (
    mb_id               BIGINT PRIMARY KEY AUTO_INCREMENT,
    mb_first_name       VARCHAR(50) NOT NULL,
    mb_last_name        VARCHAR(50) NOT NULL,
    mb_email            VARCHAR(100) NOT NULL,
    mb_license_number   VARCHAR(50) NOT NULL UNIQUE,
    mb_phone            VARCHAR(20),
    mb_address          TEXT,
    mb_status           ENUM('active', 'suspended', 'inactive') DEFAULT 'active',
    mb_created_at       TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
                        NOT NULL,
    mb_updated_at       TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
                        ON UPDATE CURRENT_TIMESTAMP
                        NOT NULL
);

CREATE INDEX ix_ap_member__mb_first_name_mb_last_name ON ap_member(mb_first_name, mb_last_name);

CREATE TABLE ap_rent (
    rt_id               UUID DEFAULT RANDOM_UUID() PRIMARY KEY,
    rt_cr_id            BIGINT NOT NULL
                        CONSTRAINT fk_ap_rent__ap_car REFERENCES ap_car(cr_id)
                            ON DELETE RESTRICT
                            ON UPDATE RESTRICT,
    rt_mb_id            BIGINT NOT NULL
                        CONSTRAINT fk_ap_rent__ap_member REFERENCES ap_member(mb_id)
                            ON DELETE RESTRICT
                            ON UPDATE RESTRICT,
    rt_start_date       TIMESTAMP WITH TIME ZONE NOT NULL,
    rt_end_date         TIMESTAMP WITH TIME ZONE,
    rt_total_amount     DECIMAL(10,2),
    rt_status           ENUM('active', 'completed', 'cancelled') DEFAULT 'active',
    rt_created_at       TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
                        NOT NULL,
    rt_updated_at       TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
                        ON UPDATE CURRENT_TIMESTAMP
                        NOT NULL,
    CONSTRAINT unq_ap_rent__rt_cr_id_rt_mb_id_rt_start_date
        UNIQUE(rt_mb_id, rt_cr_id, rt_start_date)
);

CREATE INDEX ix_ap_rent__rt_mb_id ON ap_rent(rt_mb_id);

CREATE TABLE ap_car_archived (
    ca_id               BIGINT PRIMARY KEY AUTO_INCREMENT,
    ca_license_plate    VARCHAR(20) NOT NULL UNIQUE,
    ca_brand            VARCHAR(50) NOT NULL,
    ca_model            VARCHAR(50) NOT NULL,
    ca_year             SMALLINT NOT NULL,
    ca_seats            TINYINT NOT NULL DEFAULT 4,
    ca_color            VARCHAR(30),
    ca_created_at       TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
                        NOT NULL
);