DDL

1. Вовед

DDL-от беше генериран од страна на Visual Paradigm, испробан локално и дополнет со constraints. Потоа, направивме pg_dump и го импортиравме на нашата remote база за предметот.

2. Поважни делови од кодот

Create table

CREATE TABLE user_app (
    user_id bigint NOT NULL,
    username character varying(20) NOT NULL,
    email character varying(50) NOT NULL,
    contact_phone character varying(50),
    CONSTRAINT chk_email_format CHECK (((email)::text ~~ '%@%.%'::text))
);

CREATE TABLE order_cart (

user_id bigint NOT NULL, discount_id integer, status_id integer NOT NULL, order_id bigint NOT NULL, created_at timestamp without time zone NOT NULL, total_price numeric(12,2) NOT NULL, CONSTRAINT chk_order_total CHECK ((total_price >= (0)::numeric))

);

CREATE TABLE attribute (
    name text NOT NULL,
    attribute_id integer NOT NULL,
    data_type character varying(20) NOT NULL,
    description text,
    is_required boolean NOT NULL
);

CREATE TABLE payment (
    order_id bigint NOT NULL,
    method_id integer NOT NULL,
    payment_id integer NOT NULL,
    amount_paid numeric(12,2),
    processed_at timestamp without time zone NOT NULL,
    transaction_id character varying(255) NOT NULL,
    is_refunded boolean,
    CONSTRAINT chk_payment_amount CHECK ((amount_paid >= (0)::numeric))
);

Constraints

ALTER TABLE ONLY waitlist_entry
    ADD CONSTRAINT fk_waitlist_user FOREIGN KEY (user_id) REFERENCES public.user_app(user_id) ON UPDATE CASCADE ON DELETE CASCADE;

ALTER TABLE ONLY ticket
    ADD CONSTRAINT fk_ticket_seat FOREIGN KEY (seat_id) REFERENCES public.seat(seat_id) ON UPDATE CASCADE ON DELETE SET NULL;

ALTER TABLE ONLY payment_method
    ADD CONSTRAINT payment_method_method_name_key UNIQUE (method_name);

3. Резултат

Моментално имаме табели со точни типови на атрибути и со сите потребни (за сега) ограничувања.