Креирање на базата на податоци

DDL скрипти

User, Admin, Regular_User

CREATE TABLE "User" (
    user_id BIGSERIAL PRIMARY KEY,
    username VARCHAR(255) NOT NULL UNIQUE,
    email VARCHAR(255) NOT NULL UNIQUE,
    password VARCHAR(255) NOT NULL,
    registration_timestamp TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    is_active BOOLEAN NOT NULL DEFAULT TRUE,

    CONSTRAINT user_email_check CHECK (
        email ~ '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$'
    ),
    CONSTRAINT user_password_check CHECK (
        length(password) >= 8 AND password ~ '[A-Z]' AND password ~ '[a-z]' AND password ~ '[0-9]' AND password ~ '[^a-zA-Z0-9]'
    )
);

CREATE TABLE "Admin" (
    user_id BIGINT PRIMARY KEY,

    CONSTRAINT fk_admin_user FOREIGN KEY (user_id) REFERENCES "User" (user_id)
        ON DELETE CASCADE
        ON UPDATE CASCADE
);

CREATE TABLE "Regular_User" (
    user_id BIGINT PRIMARY KEY,
    first_name VARCHAR(255) NOT NULL,
    last_name VARCHAR(255) NOT NULL,
    date_of_birth DATE NOT NULL,
    is_active BOOLEAN NOT NULL DEFAULT TRUE,

    CONSTRAINT fk_regular_user FOREIGN KEY (user_id) REFERENCES "User" (user_id)
        ON DELETE CASCADE
        ON UPDATE CASCADE
);

User_Card

CREATE TABLE "User_Card" (
    card_id BIGSERIAL PRIMARY KEY,
    user_id BIGINT NOT NULL,
    provider_token TEXT NOT NULL,
    card_brand VARCHAR(255),
    last_four VARCHAR(4),

    CONSTRAINT uq_user_provider_token UNIQUE (user_id, provider_token),
    CONSTRAINT fk_card_user FOREIGN KEY (user_id) REFERENCES "Regular_User" (user_id)
        ON DELETE CASCADE
        ON UPDATE CASCADE
);

Performer

CREATE TABLE "Performer" (
    performer_id BIGSERIAL PRIMARY KEY,
    name VARCHAR(255) NOT NULL
);

Event_Type, Event

CREATE TABLE "Event_Type" (
    type_id BIGSERIAL PRIMARY KEY,
    name VARCHAR(255) NOT NULL
);

CREATE TABLE "Event" (
    event_id BIGSERIAL PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    type_id BIGINT NOT NULL,
    description TEXT NOT NULL,
    min_age INTEGER NOT NULL,
    created_by BIGINT NOT NULL,
    is_active BOOLEAN NOT NULL DEFAULT TRUE,

    CONSTRAINT fk_event_type FOREIGN KEY (type_id) REFERENCES "Event_Type" (type_id)
        ON DELETE RESTRICT
        ON UPDATE RESTRICT,
    CONSTRAINT fk_event_admin FOREIGN KEY (created_by) REFERENCES "Admin" (user_id)
        ON DELETE RESTRICT
        ON UPDATE RESTRICT
);

Venue, Section, Seat

CREATE TABLE "Venue" (
    venue_id BIGSERIAL PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    number_of_sections INTEGER NOT NULL,
    number_of_seats INTEGER NOT NULL,
    address_street VARCHAR(255) NOT NULL,
    address_city VARCHAR(255) NOT NULL,
    address_country VARCHAR(255) NOT NULL
);

CREATE TABLE "Section" (
    section_id BIGSERIAL PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    number_of_seats INTEGER NOT NULL,
    venue_id BIGINT NOT NULL,

    CONSTRAINT uq_section_venue_name UNIQUE (venue_id, name),
    CONSTRAINT fk_section_venue FOREIGN KEY (venue_id) REFERENCES "Venue" (venue_id)
        ON DELETE CASCADE
        ON UPDATE CASCADE
);

CREATE TABLE "Seat" (
    seat_id BIGSERIAL PRIMARY KEY,
    seat_number INTEGER NOT NULL,
    row_number INTEGER NOT NULL,
    section_id BIGINT NOT NULL,

    CONSTRAINT uq_seat_section_number UNIQUE (section_id, seat_number),
    CONSTRAINT fk_seat_section FOREIGN KEY (section_id) REFERENCES "Section" (section_id)
        ON DELETE CASCADE
        ON UPDATE CASCADE
);

Event_Happening, Event_Period

CREATE TABLE "Event_Happening" (
    event_happening_id BIGSERIAL PRIMARY KEY,
    event_id BIGINT NOT NULL,
    event_time TIMESTAMP NOT NULL,
    venue_id BIGINT NOT NULL,
    duration_minutes INTEGER NOT NULL,
    organizers TEXT NOT NULL,
    sponsors TEXT,

    CONSTRAINT uq_happening_time_venue UNIQUE (event_time, venue_id),
    CONSTRAINT fk_happening_event FOREIGN KEY (event_id) REFERENCES "Event" (event_id)
        ON DELETE RESTRICT
        ON UPDATE CASCADE,
    CONSTRAINT fk_happening_venue FOREIGN KEY (venue_id) REFERENCES "Venue" (venue_id)
        ON DELETE RESTRICT
        ON UPDATE CASCADE
);

CREATE TABLE "Event_Period" (
    period_id BIGSERIAL PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    start_date DATE NOT NULL,
    end_date DATE NOT NULL,
    price_discount_percent INTEGER NOT NULL,
    event_happening_id BIGINT NOT NULL,

    CONSTRAINT uq_period_happening_name UNIQUE (event_happening_id, name),
    CONSTRAINT event_period_dates_check CHECK (start_date <= end_date),
    CONSTRAINT fk_period_happening FOREIGN KEY (event_happening_id) REFERENCES "Event_Happening" (event_happening_id)
        ON DELETE CASCADE
        ON UPDATE CASCADE
);

Event_Happening_Performer

CREATE TABLE "Event_Happening_Performer" (
    event_happening_id BIGINT NOT NULL,
    performer_id BIGINT NOT NULL,

    CONSTRAINT pk_ehp PRIMARY KEY (event_happening_id, performer_id),
    CONSTRAINT fk_ehp_happening FOREIGN KEY (event_happening_id) REFERENCES "Event_Happening" (event_happening_id)
        ON DELETE CASCADE
        ON UPDATE CASCADE,
    CONSTRAINT fk_ehp_performer FOREIGN KEY (performer_id) REFERENCES "Performer" (performer_id)
        ON DELETE CASCADE
        ON UPDATE CASCADE
);

Event_Happening_Rating

CREATE TABLE "Event_Happening_Rating" (
    rating_id BIGSERIAL PRIMARY KEY,
    rating INTEGER NOT NULL CHECK (rating BETWEEN 1 AND 10),
    comment TEXT,
    timestamp TIMESTAMP NOT NULL,
    event_happening_id BIGINT NOT NULL,
    user_id BIGINT NOT NULL DEFAULT 0,

    CONSTRAINT uq_rating_happening_user UNIQUE (event_happening_id, user_id),
    CONSTRAINT fk_rating_happening FOREIGN KEY (event_happening_id) REFERENCES "Event_Happening" (event_happening_id)
        ON DELETE CASCADE
        ON UPDATE CASCADE,
    CONSTRAINT fk_rating_regular_user FOREIGN KEY (user_id) REFERENCES "Regular_User" (user_id)
        ON DELETE SET DEFAULT
        ON UPDATE CASCADE
);

Ticket

CREATE TABLE "Ticket" (
    ticket_id BIGSERIAL PRIMARY KEY,
    base_price DECIMAL(10,2) NOT NULL,
    is_available BOOLEAN NOT NULL DEFAULT TRUE,
    event_happening_id BIGINT NOT NULL,
    seat_id BIGINT NOT NULL,

    CONSTRAINT uq_ticket_happening_seat UNIQUE (event_happening_id, seat_id),
    CONSTRAINT fk_ticket_seat FOREIGN KEY (seat_id) REFERENCES "Seat" (seat_id)
        ON DELETE RESTRICT
        ON UPDATE CASCADE,
    CONSTRAINT fk_ticket_happening FOREIGN KEY (event_happening_id) REFERENCES "Event_Happening" (event_happening_id)
        ON DELETE RESTRICT
        ON UPDATE CASCADE
);

Ticket_Order, Ticket_Order_Item

CREATE TABLE "Ticket_Order" (
    order_id BIGSERIAL PRIMARY KEY,
    order_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    order_amount DECIMAL(10,2) NOT NULL DEFAULT 0.0,
    user_id BIGINT NOT NULL,
    card_id BIGINT,

    CONSTRAINT fk_order_regular_user FOREIGN KEY (user_id) REFERENCES "Regular_User" (user_id)
        ON DELETE RESTRICT
        ON UPDATE CASCADE,
    CONSTRAINT fk_order_card FOREIGN KEY (card_id) REFERENCES "User_Card" (card_id)
        ON DELETE RESTRICT
        ON UPDATE CASCADE
);

CREATE TABLE "Ticket_Order_Item" (
    order_item_id BIGSERIAL PRIMARY KEY,
    qr_code VARCHAR(255),
    item_price DECIMAL(10,2) NOT NULL,
    order_id BIGINT NOT NULL,
    ticket_id BIGINT NOT NULL,

    CONSTRAINT fk_item_order FOREIGN KEY (order_id) REFERENCES "Ticket_Order" (order_id)
        ON DELETE CASCADE
        ON UPDATE CASCADE,
    CONSTRAINT fk_item_ticket FOREIGN KEY (ticket_id) REFERENCES "Ticket" (ticket_id)
        ON DELETE RESTRICT
        ON UPDATE CASCADE
);

Ticket_Refund, Ticket_Refund_Item

CREATE TABLE "Ticket_Refund" (
    refund_id BIGSERIAL PRIMARY KEY,
    refund_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    refund_amount DECIMAL(10,2) NOT NULL DEFAULT 0.0,
    reasons TEXT,
    order_id BIGINT NOT NULL,

    CONSTRAINT fk_refund_order FOREIGN KEY (order_id) REFERENCES "Ticket_Order" (order_id)
        ON DELETE RESTRICT
        ON UPDATE CASCADE
);

CREATE TABLE "Ticket_Refund_Item" (
    refund_item_id BIGSERIAL PRIMARY KEY,
    item_price DECIMAL(10,2) NOT NULL,
    refund_id BIGINT NOT NULL,
    order_item_id BIGINT NOT NULL UNIQUE,

    CONSTRAINT fk_ri_refund FOREIGN KEY (refund_id) REFERENCES "Ticket_Refund" (refund_id)
        ON DELETE CASCADE
        ON UPDATE CASCADE,
    CONSTRAINT fk_ri_order_item FOREIGN KEY (order_item_id) REFERENCES "Ticket_Order_Item" (order_item_id)
        ON DELETE RESTRICT
        ON UPDATE CASCADE
);

DML скрипти - Погледи (Views)

Venue_Layout

Овој поглед ја прикажува деталната физичка структура на секој објект, декомпонирајќи го просторот од ниво на локација до конкретно седиште. Со линеарно поврзување на хиерархијата Venue -> Section -> Seat, погледот овозможува брза верификација на капацитетот и распоредот, што е неопходно за логистичко планирање на настаните.

CREATE OR REPLACE VIEW "Venue_Layout" AS
SELECT v.venue_id, v.name AS venue_name, s.section_id, s.name AS section_name, st.seat_id, st.row_number, st.seat_number
FROM "Venue" v
    JOIN "Section" s ON v.venue_id = s.venue_id
    JOIN "Seat" st ON s.section_id = st.section_id;

User_Tickets

Овој поглед обезбедува сеопфатен хронолошки приказ на сите трансакции по корисник. Покрај основните информации како настан, цена и QR-код, погледот инкорпорира податоци за рефундации (преку LEFT JOIN со табелите за рефундација), овозможувајќи моментна идентификација на статусот на секој билет. Погледот е филтриран само за активни корисници, со што се елиминираат податоците од неактивни профили.

CREATE OR REPLACE VIEW "User_Tickets" AS
SELECT u.user_id, u.username, toi.order_item_id, t.ticket_id, e.event_id, e.name AS event_name, eh.event_time,
       toi.qr_code, toi.item_price AS price_paid, tri.refund_item_id, tr.refund_time
FROM "User" u
    JOIN "Regular_User" ru ON u.user_id = ru.user_id
    JOIN "Ticket_Order" o ON ru.user_id = o.user_id
    JOIN "Ticket_Order_Item" toi ON o.order_id = toi.order_id
    JOIN "Ticket" t ON toi.ticket_id = t.ticket_id
    JOIN "Event_Happening" eh ON t.event_happening_id = eh.event_happening_id
    JOIN "Event" e ON eh.event_id = e.event_id
    LEFT JOIN "Ticket_Refund_Item" tri ON toi.order_item_id = tri.order_item_id
    LEFT JOIN "Ticket_Refund" tr ON tri.refund_id = tr.refund_id
WHERE u.is_active = TRUE;

Future_Events

Овој поглед служи како динамичен извор за репертоарот на платформата. Ги изолира само идните настани (eh.event_time > CURRENT_TIMESTAMP) кои се означени како активни, прикажувајќи ги со нивната целосна географска локација. Оптимизиран е за брзо пребарување од страна на крајните корисници при избор на термин.

CREATE OR REPLACE VIEW "Future_Events" AS
SELECT e.event_id, e.name AS event_name, eh.event_happening_id, eh.event_time, v.venue_id, v.name AS venue_name,
       v.address_street AS street, v.address_city AS city, v.address_country AS country
FROM "Event" e
    JOIN "Event_Happening" eh ON e.event_id = eh.event_id
    JOIN "Venue" v ON eh.venue_id = v.venue_id
WHERE eh.event_time > CURRENT_TIMESTAMP AND e.is_active = TRUE;

Available_Tickets

Овој поглед ја нуди моменталната „понуда“ на слободни седишта. Имплементира бизнис логика за динамично ценообразовање: во реално време ја пресметува крајната цена на билетот, применувајќи го соодветниот попуст (price_discount_percent) врз база на тековниот временски период (Event_Period). Со ова, корисникот секогаш добива ажурирана цена при резервација.

CREATE OR REPLACE VIEW "Available_Tickets" AS
SELECT t.ticket_id, ROUND(t.base_price * (1 - COALESCE(ep.price_discount_percent, 0) / 100.0), 2) AS price,
       e.event_id, e.name AS event_name, eh.event_happening_id, eh.event_time, v.name AS venue_name,
       s.name AS section_name, st.row_number, st.seat_number
FROM "Ticket" t
    JOIN "Event_Happening" eh ON t.event_happening_id = eh.event_happening_id
    JOIN "Event" e ON eh.event_id = e.event_id
    JOIN "Venue" v ON eh.venue_id = v.venue_id
    JOIN "Seat" st ON t.seat_id = st.seat_id
    JOIN "Section" s ON st.section_id = s.section_id
    LEFT JOIN "Event_Period" ep ON eh.event_happening_id = ep.event_happening_id
                              AND CURRENT_DATE BETWEEN ep.start_date AND ep.end_date
WHERE t.is_available = TRUE AND e.is_active = TRUE;

Event_Overall_Ratings

Овој материјализиран поглед врши агрегациска анализа на задоволството на публиката. Со пресметување на просечните оценки (AVG) и бројот на рецензии (COUNT) по настан, тој обезбедува клучни перформанс индикатори (KPIs) за организаторите. Материјализирањето е избрано за да се обезбеди висока брзина на вчитување при прикажување на статистички извештаи, избегнувајќи комплексни пресметки при секој прашалник.

CREATE MATERIALIZED VIEW "Event_Overall_Ratings" AS
SELECT e.event_id, e.name AS event_name, eh.event_happening_id, eh.event_time,
       COUNT(ehr.rating_id) AS total_reviews, COALESCE(ROUND(AVG(ehr.rating), 2), 0.00) AS average_rating
FROM "Event" e
    JOIN "Event_Happening" eh ON e.event_id = eh.event_id
    LEFT JOIN "Event_Happening_Rating" ehr ON eh.event_happening_id = ehr.event_happening_id
WHERE e.is_active = TRUE
GROUP BY e.event_id, e.name, eh.event_happening_id, eh.event_time;

User_Order_History

Овој поглед генерира финансиски профил за секој корисник, сумирајќи ги направените нарачки и повратните средства. Оптимизиран е за сметководствени цели, нудејќи јасен увид во обемот на купени и рефундирани ставки. Материјализираната природа на погледот значително го растеретува серверот при генерирање на кориснички „Dashboard“ извештаи.

CREATE MATERIALIZED VIEW "User_Order_History" AS
SELECT u.user_id, u.username, o.order_id, o.order_time, o.order_amount,
       COALESCE(items.total_items_ordered, 0) AS total_items_ordered,
       COALESCE(items.total_items_refunded, 0) AS total_items_refunded,
       COALESCE(items.total_amount_refunded, 0.00) AS total_amount_refunded
FROM "User" u
    JOIN "Regular_User" ru ON u.user_id = ru.user_id
    JOIN "Ticket_Order" o ON ru.user_id = o.user_id
    LEFT JOIN (
        SELECT toi.order_id, COUNT(toi.order_item_id) AS total_items_ordered,
               COUNT(tri.refund_item_id) AS total_items_refunded,
               SUM(COALESCE(tri.item_price, 0.00)) AS total_amount_refunded
        FROM "Ticket_Order_Item" toi
        LEFT JOIN "Ticket_Refund_Item" tri ON toi.order_item_id = tri.order_item_id
        GROUP BY toi.order_id
    ) items ON o.order_id = items.order_id
WHERE u.is_active = TRUE;

Venue_Occupancy_Report

Овој аналитички поглед го мери „ефектот на продажба“ преку пресметка на процентот на пополнетост (occupancy_percentage) на секој термин. Тој е примарна алатка за менаџерите кои треба да го следат успехот на настанот во реално време. Имплементиран е како материјализиран поглед за да поддржи брзо аналитичко известување без дополнително оптоварување на базата со тешки JOIN операции врз табелите за нарачки.

CREATE MATERIALIZED VIEW "Venue_Occupancy_Report" AS
SELECT e.event_id, e.name AS event_name, eh.event_happening_id, eh.event_time, v.name AS venue_name,
       v.number_of_seats AS total_venue_capacity, COALESCE(stats.sold_count, 0) AS tickets_sold,
       ROUND((COALESCE(stats.sold_count, 0)::NUMERIC / v.number_of_seats::NUMERIC) * 100, 2) AS occupancy_percentage
FROM "Event" e
    JOIN "Event_Happening" eh ON e.event_id = eh.event_id
    JOIN "Venue" v ON eh.venue_id = v.venue_id
    LEFT JOIN (
        SELECT t.event_happening_id, COUNT(toi.order_item_id) AS sold_count
        FROM "Ticket_Order_Item" toi
        JOIN "Ticket" t ON toi.ticket_id = t.ticket_id
        GROUP BY t.event_happening_id
    ) stats ON eh.event_happening_id = stats.event_happening_id
WHERE e.is_active = TRUE
GROUP BY e.event_id, e.name, eh.event_happening_id, eh.event_time, v.name, v.number_of_seats, stats.sold_count;