Phase P7: Advanced Database Development (SQL DDL)

Overview

This phase focuses on implementing advanced database mechanisms that enforce business logic directly at the database level. Instead of relying only on application code, the database itself ensures correctness, prevents invalid states, and improves performance. In the Wedding Planner project, this is especially important because the system handles bookings, RSVPs and guest management where mistakes (double booking, invalid status values, capacity overflow) must be impossible.

In this phase we implemented:

• Indexes – to speed up searches and filtering
• Constraints – to prevent inconsistent or invalid data (including overlap prevention)
• Triggers – automation of derived fields and enforcing business rules
• Views – simplified reporting and aggregated queries

Indexes

Indexes significantly improve performance for common queries such as scheduling, availability checks and RSVP filtering.

Index 1: Event date & time optimization

This index improves performance when searching or filtering events by date and start/end time (e.g., event timeline and schedule overview).

*Table:* event *Type:* Composite B-tree index

CREATE INDEX idx_event_date_start ON event("date", start_time);
CREATE INDEX idx_event_date_end   ON event("date", end_time);

*Why this matters:* Without indexes, PostgreSQL must scan the entire event table (Seq Scan) to find matching records. With indexes, it can quickly retrieve only the relevant events for a given wedding date and time range.

Index 2: Booking status filtering (availability checks)

These indexes optimize filtering bookings by date and status for venues, photographers and bands (used in availability endpoints and admin dashboards).

*Tables:* venue_booking, photographer_booking, band_booking *Type:* Composite B-tree index

CREATE INDEX idx_venue_booking_status        ON venue_booking("date", status);
CREATE INDEX idx_photographer_booking_status ON photographer_booking("date", status);
CREATE INDEX idx_band_booking_status         ON band_booking("date", status);

*Why this matters:* Availability checks would otherwise require full scans across booking tables. With indexes the system stays scalable as data grows.

Index 3: RSVP filtering per event

This index improves performance when retrieving RSVP responses for an event grouped by status.

*Table:* event_rsvp *Type:* Composite B-tree index

CREATE INDEX idx_rsvp_event_status ON event_rsvp (event_id, status);

*Why this matters:* Queries such as “all accepted guests for event_id X” are much faster with this index.

Constraints

Constraints enforce correctness of the data and prevent invalid or inconsistent states.

Constraint 1: Prevent overlapping bookings (no double booking)

One of the most important business rules in Wedding Planner is that a resource cannot be booked twice in overlapping time intervals. This is enforced with EXCLUDE constraints using GiST indexes.

First, we enable required extension:

CREATE EXTENSION IF NOT EXISTS btree_gist;

Then we add generated timestamp columns which combine date + start_time/end_time:

ALTER TABLE venue_booking
ADD COLUMN start_ts TIMESTAMP NOT NULL GENERATED ALWAYS AS ("date" + start_time) STORED,
ADD COLUMN end_ts   TIMESTAMP NOT NULL GENERATED ALWAYS AS ("date" + end_time) STORED;

ALTER TABLE photographer_booking
ADD COLUMN start_ts TIMESTAMP NOT NULL GENERATED ALWAYS AS ("date" + start_time) STORED,
ADD COLUMN end_ts   TIMESTAMP NOT NULL GENERATED ALWAYS AS ("date" + end_time) STORED;

ALTER TABLE band_booking
ADD COLUMN start_ts TIMESTAMP NOT NULL GENERATED ALWAYS AS ("date" + start_time) STORED,
ADD COLUMN end_ts   TIMESTAMP NOT NULL GENERATED ALWAYS AS ("date" + end_time) STORED;

Finally, we enforce overlap prevention:

ALTER TABLE venue_booking
ADD CONSTRAINT exclude_overlap
EXCLUDE USING gist (
    venue_id WITH =,
    tsrange(start_ts, end_ts) WITH &&
);

ALTER TABLE photographer_booking
ADD CONSTRAINT exclude_overlap_pb
EXCLUDE USING gist (
    photographer_id WITH =,
    tsrange(start_ts, end_ts) WITH &&
);

ALTER TABLE band_booking
ADD CONSTRAINT excl_bb_no_overlap
EXCLUDE USING gist (
    band_id WITH =,
    tsrange(start_ts, end_ts) WITH &&
);

*Why this matters:* This guarantees that a venue/band/photographer cannot be double booked for overlapping time ranges, even if the application sends incorrect input.

Constraint 2: CHECK constraints for valid values

To prevent invalid statuses and roles, we added CHECK constraints.

ALTER TABLE event
ADD CONSTRAINT chk_event_status
CHECK (status IN ('scheduled', 'confirmed', 'cancelled'));

ALTER TABLE event_rsvp
ADD CONSTRAINT chk_event_rsvp_status
CHECK (status IN ('accepted', 'declined', 'pending'));

ALTER TABLE venue_booking
ADD CONSTRAINT chk_venue_booking_status
CHECK (status IN ('pending', 'confirmed', 'cancelled'));

ALTER TABLE photographer_booking
ADD CONSTRAINT chk_photographer_booking_status
CHECK (status IN ('pending', 'confirmed', 'cancelled'));

ALTER TABLE band_booking
ADD CONSTRAINT chk_band_booking_status
CHECK (status IN ('pending', 'confirmed', 'cancelled'));

ALTER TABLE attendance
ADD CONSTRAINT chk_attendance_status
CHECK (status IN ('attending', 'not_attending'));

ALTER TABLE wedding
ADD CONSTRAINT chk_budget
CHECK (budget > 0);

ALTER TABLE attendance
ADD CONSTRAINT chk_att_role
CHECK (role IN ('Guest', 'Bride', 'Groom', 'Best Man', 'Maid of Honor'));

*Why this matters:* The database rejects invalid values automatically, ensuring consistent and predictable data for the application.

Triggers

Triggers automate important derived logic and enforce real-world constraints.

Trigger 1: Automatic venue price calculation

This trigger automatically calculates venue booking price if it is not provided, based on:

• venue.price_per_guest
• number of guests for the wedding

CREATE OR REPLACE FUNCTION calc_venue_price()
RETURNS trigger AS $$
BEGIN
    IF NEW.price IS NULL THEN
        SELECT price_per_guest * COUNT(*)
        INTO NEW.price
        FROM venue v
        JOIN guest g ON g.wedding_id = NEW.wedding_id
        WHERE v.venue_id = NEW.venue_id;
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_calc_venue_price
BEFORE INSERT ON venue_booking
FOR EACH ROW
EXECUTE FUNCTION calc_venue_price();

*Why this matters:* Price is a derived field. This ensures it is always correct and not dependent only on application-level calculations.

Trigger 2: Venue capacity enforcement

This trigger prevents inserting/updating guests when the number of guests exceeds the confirmed venue capacity.

CREATE OR REPLACE FUNCTION check_guest_capacity()
RETURNS trigger AS $$
DECLARE
    guest_count INTEGER;
    venue_cap   INTEGER;
BEGIN
    SELECT v.capacity
    INTO venue_cap
    FROM venue_booking vb
    JOIN venue v ON v.venue_id = vb.venue_id
    WHERE vb.wedding_id = NEW.wedding_id
      AND vb.status = 'confirmed';

    IF venue_cap IS NULL THEN
        RETURN NEW;
    END IF;

    SELECT COUNT(*) INTO guest_count
    FROM guest
    WHERE wedding_id = NEW.wedding_id
      AND (TG_OP = 'INSERT' OR guest_id <> OLD.guest_id);

    IF guest_count + 1 > venue_cap THEN
        RAISE EXCEPTION
        'Cannot add guest: venue capacity (%) exceeded',
        venue_cap;
    END IF;

    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_guest_capacity
BEFORE INSERT OR UPDATE ON guest
FOR EACH ROW
EXECUTE FUNCTION check_guest_capacity();

*Why this matters:* This enforces a critical business rule: a wedding cannot have more guests than venue capacity.

Views

Views simplify reporting and provide useful aggregated results.

View 1: RSVP summary per event

This view returns counts of accepted/declined/pending RSVP answers for each event.

CREATE VIEW vw_event_rsvp_summary AS
SELECT
    event_id,
    SUM(CASE WHEN status = 'accepted' THEN 1 ELSE 0 END) AS yes_count,
    SUM(CASE WHEN status = 'declined' THEN 1 ELSE 0 END) AS no_count,
    SUM(CASE WHEN status = 'pending'  THEN 1 ELSE 0 END) AS maybe_count
FROM event_rsvp
GROUP BY event_id;

View 2: Confirmed bookings by date (all service providers)

This view unifies confirmed bookings (venue, photographer, band) into one report.

CREATE VIEW vw_confirmed_bookings_by_date AS
SELECT
    "date",
    'VENUE' AS booking_type,
    venue_id AS resource_id,
    start_time,
    end_time
FROM venue_booking
WHERE status = 'confirmed'

UNION ALL

SELECT
    "date",
    'PHOTOGRAPHER' AS booking_type,
    photographer_id AS resource_id,
    start_time,
    end_time
FROM photographer_booking
WHERE status = 'confirmed'

UNION ALL

SELECT
    "date",
    'BAND' AS booking_type,
    band_id AS resource_id,
    start_time,
    end_time
FROM band_booking
WHERE status = 'confirmed';

View 3: Guest count per wedding

This view counts guests grouped by wedding_id.

CREATE VIEW vw_wedding_guest_count AS
SELECT
    wedding_id,
    COUNT(*) AS total_guests
FROM guest
GROUP BY wedding_id;

Proof / Screenshots

To confirm that Phase P7 is implemented in PostgreSQL (DBeaver), we provide screenshots of successful execution and created objects:

• Indexes list created in schema:
• EXCLUDE constraint for overlap prevention:
• Trigger function execution in DBeaver:
• Created view in schema and executed script:

Conclusion

Phase P7 implements advanced database-level mechanisms for the Wedding Planner system. Indexes improve scalability and query performance, constraints prevent invalid states (especially double booking), triggers automate key business logic (price calculation and capacity enforcement), and views provide simplified reporting. With these mechanisms, the database becomes robust, consistent, and production-ready.