Changes between Version 3 and Version 4 of P7

Timestamp:

01/18/26 01:06:40 (3 days ago)

Author:

193284

Comment:

--

P7

@@ -1 +1 @@
 = Phase P7: Advanced Database Development (SQL DDL) =
-This phase introduces advanced database mechanisms that help enforce business rules directly at database level. 
-In our Wedding Planner database (PostgreSQL via DBeaver), we use indexes, constraints, triggers and views to improve performance, prevent invalid data and automate important logic.
 
 == Overview ==
-In this phase we implement:
- * Indexes – faster searching/filtering in large tables
- * Constraints – enforce validity and prevent inconsistent records
- * Triggers – automate rules (price calculation, venue capacity control)
- * Views – simplify reporting queries and provide reusable reports
-
-== What we cover in this phase ==
- * Indexes for frequent query patterns (events, bookings, RSVP)
- * Constraints for data validation + preventing overlapping bookings
- * Triggers to automate derived values (venue price) and enforce capacity
- * Views for simplified summaries (RSVP counts, confirmed bookings, guest counts)
-
-----
-
-== Indexes (Performance Improvement) ==
-
-=== Index: Event Date and Time Search Optimization ===
-This improves performance when searching or filtering events by date and time interval.
-
-*Tables affected:* `event`  
-*Index type:* B-tree composite indexes
+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
 
 {{{
@@ -31 +26 @@
 }}}
 
-*Explanation:*  
-The indexes store pre-sorted combinations of (`date`, `start_time`) and (`date`, `end_time`), which allows fast retrieval of events for a specific day/time window without scanning the entire table.
-
----
-
-=== Index: Booking Status Filtering ===
-These indexes optimize availability checks and booking filtering by date and status.
-
-*Tables affected:* `venue_booking`, `photographer_booking`, `band_booking`  
-*Index type:* B-tree composite indexes
+*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
 
 {{{
@@ -48 +42 @@
 }}}
 
-*Explanation:*  
-Booking records are organized by (`date`, `status`), allowing fast retrieval of rows such as: “confirmed bookings on a given date”.
-
----
-
-=== Index: RSVP Status Filtering ===
-This index speeds up reports of RSVP responses per event.
-
-*Table affected:* `event_rsvp`  
-*Index type:* Composite B-tree index
+*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
 
 {{{
@@ -63 +55 @@
 }}}
 
-*Explanation:*  
-RSVP rows are grouped by event and status, so queries like “all accepted guests for event X” are executed efficiently.
-
-----
-
-== Constraints (Data Integrity) ==
-
-=== Constraint: Prevent Overlapping Bookings ===
-This prevents double-booking of the same venue/photographer/band at overlapping time intervals.
-
-To support this constraint, we use:
- * `btree_gist` extension
- * generated timestamp columns (`start_ts`, `end_ts`)
- * GiST exclusion constraints
+*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:
 
 {{{
@@ -82 +71 @@
 }}}
 
-*Generated timestamps (date + time):*
+Then we add generated timestamp columns which combine date + start_time/end_time:
+
 {{{
 ALTER TABLE venue_booking
@@ -97 +87 @@
 }}}
 
-*Exclude overlap constraints:*
+Finally, we enforce overlap prevention:
+
 {{{
 ALTER TABLE venue_booking
-ADD CONSTRAINT exclude_overlap_vb EXCLUDE USING gist (
+ADD CONSTRAINT exclude_overlap
+EXCLUDE USING gist (
     venue_id WITH =,
     tsrange(start_ts, end_ts) WITH &&
@@ -106 +98 @@
 
 ALTER TABLE photographer_booking
-ADD CONSTRAINT exclude_overlap_pb EXCLUDE USING gist (
+ADD CONSTRAINT exclude_overlap_pb
+EXCLUDE USING gist (
     photographer_id WITH =,
     tsrange(start_ts, end_ts) WITH &&
@@ -112 +105 @@
 
 ALTER TABLE band_booking
-ADD CONSTRAINT exclude_overlap_bb EXCLUDE USING gist (
+ADD CONSTRAINT excl_bb_no_overlap
+EXCLUDE USING gist (
     band_id WITH =,
     tsrange(start_ts, end_ts) WITH &&
@@ -118 +112 @@
 }}}
 
-*Explanation:*  
-The database rejects inserting a booking if the interval overlaps with another booking for the same resource (venue/band/photographer).
-
----
-
-=== Constraint: Status and Value Validation ===
-We prevent invalid values for status/role/budget using CHECK constraints.
+*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.
 
 {{{
@@ -160 +152 @@
 }}}
 
-*Explanation:*  
-If an invalid value is inserted or updated, PostgreSQL rejects the operation and keeps the database consistent.
-
-== Triggers (Automation of Business Logic) ==
-
-=== Trigger: Automatic Venue Price Calculation ===
-This trigger calculates the total venue booking price based on:
- * venue price per guest
- * current number of guests for that wedding
-
-*Tables involved:* `venue_booking`, `venue`, `guest`  
-*Trigger type:* BEFORE INSERT on `venue_booking`
+*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
 
 {{{
@@ -194 +184 @@
 }}}
 
-*Reason for use:*  
-This ensures price is always correct and prevents manual calculation errors inside the application.
-
----
-
-=== Trigger: Venue Capacity Enforcement ===
-This trigger prevents adding guests if the confirmed venue capacity will be exceeded.
-
-*Tables involved:* `guest`, `venue_booking`, `venue`  
-*Trigger type:* BEFORE INSERT OR UPDATE on `guest`
+*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.
 
 {{{
@@ -229 +214 @@
 
     IF guest_count + 1 > venue_cap THEN
-        RAISE EXCEPTION 'Cannot add guest: venue capacity (%) exceeded', venue_cap;
+        RAISE EXCEPTION
+        'Cannot add guest: venue capacity (%) exceeded',
+        venue_cap;
     END IF;
 
@@ -242 +229 @@
 }}}
 
-*Reason for use:*  
-This enforces a real-world rule: guest count cannot exceed venue capacity.
-
-----
-
-== Views (Simplified Reports) ==
-
-=== View: RSVP summary per event ===
-Shows RSVP counts per status for each event.
+*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.
 
 {{{
@@ -258 +244 @@
     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
+    SUM(CASE WHEN status = 'pending'  THEN 1 ELSE 0 END) AS maybe_count
 FROM event_rsvp
 GROUP BY event_id;
 }}}
 
----
-
-=== View: Confirmed bookings by date (all types) ===
-A unified report for all confirmed bookings in one result.
+=== View 2: Confirmed bookings by date (all service providers) ===
+This view unifies confirmed bookings (venue, photographer, band) into one report.
 
 {{{
@@ -302 +286 @@
 }}}
 
----
-
-=== View: Guest count per wedding ===
-A reusable summary of how many guests exist for each wedding.
+=== View 3: Guest count per wedding ===
+This view counts guests grouped by wedding_id.
 
 {{{
@@ -316 +298 @@
 }}}
 
-----
+== 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: [[Image(P7_indexes_list.png)]]
+ * EXCLUDE constraint for overlap prevention: [[Image(P7_exclude_overlap_band_booking.png)]]
+ * Trigger function execution in DBeaver: [[Image(P7_trigger_function_capacity.png)]]
+ * Created view in schema and executed script: [[Image(P7_view_wedding_guest_count.png)]]
 
 == Conclusion ==
-In this phase we implemented advanced database mechanisms that improve scalability and correctness of our Wedding Planner system.
-Indexes help queries run faster as the number of bookings/events grows.
-Constraints enforce strict data rules and prevent invalid states, such as double-booking.
-Triggers automate derived values and business rules like venue capacity control.
-Finally, views provide reusable reports and simplify querying for common summaries.
+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.