Changes between Version 2 and Version 3 of P7


Ignore:
Timestamp:
01/18/26 00:52:43 (3 days ago)
Author:
193284
Comment:

--

Legend:

Unmodified
Added
Removed
Modified

  • P7

    v2 v3  
    1 = Phase P7: Advanced Database Development (Indexes, Constraints, Triggers, Views) =
     1= Phase P7: Advanced Database Development (SQL DDL) =
     2This phase introduces advanced database mechanisms that help enforce business rules directly at database level.
     3In our Wedding Planner database (PostgreSQL via DBeaver), we use indexes, constraints, triggers and views to improve performance, prevent invalid data and automate important logic.
    24
    35== Overview ==
    4 In this phase we implement advanced database mechanisms that enforce correctness and improve performance in the Wedding Planner system.
    5 Instead of relying only on application logic, we move important rules directly into PostgreSQL using indexes, constraints, triggers and views.
     6In this phase we implement:
     7 * Indexes – faster searching/filtering in large tables
     8 * Constraints – enforce validity and prevent inconsistent records
     9 * Triggers – automate rules (price calculation, venue capacity control)
     10 * Views – simplify reporting queries and provide reusable reports
    611
    712== What we cover in this phase ==
    8  * Indexes for faster searching/filtering (availability checks, RSVP reports)
    9  * Constraints for preventing invalid/inconsistent data
    10  * Triggers for automation of derived logic (pricing, capacity checks)
    11  * Views for simplified reporting (RSVP summary, confirmed bookings overview)
     13 * Indexes for frequent query patterns (events, bookings, RSVP)
     14 * Constraints for data validation + preventing overlapping bookings
     15 * Triggers to automate derived values (venue price) and enforce capacity
     16 * Views for simplified summaries (RSVP counts, confirmed bookings, guest counts)
    1217
    1318----
    1419
    15 == 1) Indexes (Query Performance) ==
    16 Indexes speed up database queries by avoiding full table scans.
    17 They are especially important in our system because availability checks and RSVP reports are executed frequently.
    18 
    19 === Index 1: Event date/time search ===
    20 This index improves performance when searching or filtering events by date + start/end time.
    21 
    22 *Table:* `event`
     20== Indexes (Performance Improvement) ==
     21
     22=== Index: Event Date and Time Search Optimization ===
     23This improves performance when searching or filtering events by date and time interval.
     24
     25*Tables affected:* `event` 
     26*Index type:* B-tree composite indexes
    2327
    2428{{{
    … …  
    2731}}}
    2832
    29 *Why it matters:* 
    30 When the system loads the schedule for a wedding day, the database can quickly find events in a time window (without scanning the whole table).
    31 
    32 === Index 2: Booking status filtering (availability checks) ===
    33 Availability endpoints frequently filter bookings by date + status (only CONFIRMED bookings should block resources).
    34 
    35 *Tables:* `venue_booking`, `photographer_booking`, `band_booking`
     33*Explanation:* 
     34The 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.
     35
     36---
     37
     38=== Index: Booking Status Filtering ===
     39These indexes optimize availability checks and booking filtering by date and status.
     40
     41*Tables affected:* `venue_booking`, `photographer_booking`, `band_booking` 
     42*Index type:* B-tree composite indexes
    3643
    3744{{{
    … …  
    4148}}}
    4249
    43 *Reason:* 
    44 These indexes make queries like “all confirmed bookings on date X” significantly faster.
    45 
    46 === Index 3: RSVP status report ===
    47 RSVP summaries filter guests by status per event.
    48 
    49 *Table:* `event_rsvp`
     50*Explanation:* 
     51Booking records are organized by (`date`, `status`), allowing fast retrieval of rows such as: “confirmed bookings on a given date”.
     52
     53---
     54
     55=== Index: RSVP Status Filtering ===
     56This index speeds up reports of RSVP responses per event.
     57
     58*Table affected:* `event_rsvp` 
     59*Index type:* Composite B-tree index
    5060
    5161{{{
    … …  
    5363}}}
    5464
    55 *Reason:* 
    56 This makes reporting queries efficient (e.g., confirmed vs declined guests for an event).
     65*Explanation:* 
     66RSVP rows are grouped by event and status, so queries like “all accepted guests for event X” are executed efficiently.
    5767
    5868----
    5969
    60 == 2) Constraints (Consistency & Integrity) ==
    61 Constraints guarantee correctness even if invalid data reaches the database.
    62 In Wedding Planner, this is critical for avoiding double bookings and enforcing valid status values.
    63 
    64 === Constraint 1: Prevent overlapping bookings (NO double booking) ===
    65 A venue / photographer / band must not be booked in overlapping time intervals.
    66 
    67 To support this, we first create generated timestamp columns and enable GiST indexing.
     70== Constraints (Data Integrity) ==
     71
     72=== Constraint: Prevent Overlapping Bookings ===
     73This prevents double-booking of the same venue/photographer/band at overlapping time intervals.
     74
     75To support this constraint, we use:
     76 * `btree_gist` extension
     77 * generated timestamp columns (`start_ts`, `end_ts`)
     78 * GiST exclusion constraints
    6879
    6980{{{
    7081CREATE EXTENSION IF NOT EXISTS btree_gist;
    71 
     82}}}
     83
     84*Generated timestamps (date + time):*
     85{{{
    7286ALTER TABLE venue_booking
    7387ADD COLUMN start_ts TIMESTAMP NOT NULL GENERATED ALWAYS AS ("date" + start_time) STORED,
    … …  
    8397}}}
    8498
    85 Then we add EXCLUDE constraints:
    86 
     99*Exclude overlap constraints:*
    87100{{{
    88101ALTER TABLE venue_booking
    … …  
    105118}}}
    106119
    107 *Why this is important:* 
    108 This enforces a real-world rule at database level:
    109 even if two users try to insert bookings at the same time, PostgreSQL blocks conflicts automatically.
    110 
    111 === Constraint 2: Status validation (CHECK constraints) ===
    112 CHECK constraints restrict allowed values and prevent “random text statuses”.
     120*Explanation:* 
     121The database rejects inserting a booking if the interval overlaps with another booking for the same resource (venue/band/photographer).
     122
     123---
     124
     125=== Constraint: Status and Value Validation ===
     126We prevent invalid values for status/role/budget using CHECK constraints.
    113127
    114128{{{
    115129ALTER TABLE event
    116130ADD CONSTRAINT chk_event_status
    117 CHECK (status IN ('PLANNED', 'CONFIRMED', 'CANCELLED'));
     131CHECK (status IN ('scheduled', 'confirmed', 'cancelled'));
    118132
    119133ALTER TABLE event_rsvp
    120134ADD CONSTRAINT chk_event_rsvp_status
    121 CHECK (status IN ('ACCEPTED', 'DECLINED', 'PENDING'));
     135CHECK (status IN ('accepted', 'declined', 'pending'));
    122136
    123137ALTER TABLE venue_booking
    124138ADD CONSTRAINT chk_venue_booking_status
    125 CHECK (status IN ('PENDING', 'CONFIRMED', 'CANCELLED'));
     139CHECK (status IN ('pending', 'confirmed', 'cancelled'));
    126140
    127141ALTER TABLE photographer_booking
    128142ADD CONSTRAINT chk_photographer_booking_status
    129 CHECK (status IN ('PENDING', 'CONFIRMED', 'CANCELLED'));
     143CHECK (status IN ('pending', 'confirmed', 'cancelled'));
    130144
    131145ALTER TABLE band_booking
    132146ADD CONSTRAINT chk_band_booking_status
    133 CHECK (status IN ('PENDING', 'CONFIRMED', 'CANCELLED'));
    134 }}}
    135 
    136 Additional validations:
    137 
    138 {{{
     147CHECK (status IN ('pending', 'confirmed', 'cancelled'));
     148
     149ALTER TABLE attendance
     150ADD CONSTRAINT chk_attendance_status
     151CHECK (status IN ('attending', 'not_attending'));
     152
    139153ALTER TABLE wedding
    140 ADD CONSTRAINT chk_budget CHECK (budget > 0);
     154ADD CONSTRAINT chk_budget
     155CHECK (budget > 0);
    141156
    142157ALTER TABLE attendance
    143158ADD CONSTRAINT chk_att_role
    144 CHECK (role IN ('GUEST', 'BRIDE', 'GROOM', 'BEST_MAN', 'MAID_OF_HONOR'));
    145 }}}
    146 
    147 *Result:* 
    148 The database rejects invalid states automatically, ensuring consistent data across the whole system.
    149 
    150 
    151 ----
    152 
    153 == 3) Triggers (Automation of Business Logic) ==
    154 Triggers automate derived logic and enforce rules without relying on the application.
    155 
    156 === Trigger 1: Automatic venue booking price calculation ===
    157 The total venue booking price is derived from:
    158 `venue.price_per_guest * number_of_guests_for_wedding`.
     159CHECK (role IN ('Guest', 'Bride', 'Groom', 'Best Man', 'Maid of Honor'));
     160}}}
     161
     162*Explanation:* 
     163If an invalid value is inserted or updated, PostgreSQL rejects the operation and keeps the database consistent.
     164
     165== Triggers (Automation of Business Logic) ==
     166
     167=== Trigger: Automatic Venue Price Calculation ===
     168This trigger calculates the total venue booking price based on:
     169 * venue price per guest
     170 * current number of guests for that wedding
     171
     172*Tables involved:* `venue_booking`, `venue`, `guest` 
     173*Trigger type:* BEFORE INSERT on `venue_booking`
    159174
    160175{{{
    … …  
    179194}}}
    180195
    181 *Why it matters:* 
    182 This ensures consistent pricing and avoids manual calculation mistakes.
    183 
    184 === Trigger 2: Venue capacity enforcement ===
    185 This trigger prevents inserting guests if venue capacity would be exceeded.
     196*Reason for use:* 
     197This ensures price is always correct and prevents manual calculation errors inside the application.
     198
     199---
     200
     201=== Trigger: Venue Capacity Enforcement ===
     202This trigger prevents adding guests if the confirmed venue capacity will be exceeded.
     203
     204*Tables involved:* `guest`, `venue_booking`, `venue` 
     205*Trigger type:* BEFORE INSERT OR UPDATE on `guest`
    186206
    187207{{{
    … …  
    197217    JOIN venue v ON v.venue_id = vb.venue_id
    198218    WHERE vb.wedding_id = NEW.wedding_id
    199       AND vb.status = 'CONFIRMED';
     219      AND vb.status = 'confirmed';
    200220
    201221    IF venue_cap IS NULL THEN
    … …  
    222242}}}
    223243
    224 *Why it matters:* 
    225 It guarantees that capacity rules are enforced even if the app forgets to validate.
     244*Reason for use:* 
     245This enforces a real-world rule: guest count cannot exceed venue capacity.
    226246
    227247----
    228248
    229 == 4) Views (Simplified Reports) ==
    230 Views provide reusable report queries for the system dashboard.
    231 
    232 === View 1: RSVP summary per event ===
     249== Views (Simplified Reports) ==
     250
     251=== View: RSVP summary per event ===
     252Shows RSVP counts per status for each event.
     253
    233254{{{
    234255CREATE VIEW vw_event_rsvp_summary AS
    235256SELECT
    236257    event_id,
    237     SUM(CASE WHEN status = 'ACCEPTED' THEN 1 ELSE 0 END) AS yes_count,
    238     SUM(CASE WHEN status = 'DECLINED' THEN 1 ELSE 0 END) AS no_count,
    239     SUM(CASE WHEN status = 'PENDING' THEN 1 ELSE 0 END) AS maybe_count
     258    SUM(CASE WHEN status = 'accepted' THEN 1 ELSE 0 END) AS yes_count,
     259    SUM(CASE WHEN status = 'declined' THEN 1 ELSE 0 END) AS no_count,
     260    SUM(CASE WHEN status = 'pending' THEN 1 ELSE 0 END) AS maybe_count
    240261FROM event_rsvp
    241262GROUP BY event_id;
    242263}}}
    243264
    244 === View 2: Confirmed bookings by date (all resources) ===
     265---
     266
     267=== View: Confirmed bookings by date (all types) ===
     268A unified report for all confirmed bookings in one result.
     269
    245270{{{
    246271CREATE VIEW vw_confirmed_bookings_by_date AS
    … …  
    252277    end_time
    253278FROM venue_booking
    254 WHERE status = 'CONFIRMED'
     279WHERE status = 'confirmed'
    255280
    256281UNION ALL
    … …  
    263288    end_time
    264289FROM photographer_booking
    265 WHERE status = 'CONFIRMED'
     290WHERE status = 'confirmed'
    266291
    267292UNION ALL
    … …  
    274299    end_time
    275300FROM band_booking
    276 WHERE status = 'CONFIRMED';
    277 }}}
    278 
    279 === View 3: Guest count per wedding ===
     301WHERE status = 'confirmed';
     302}}}
     303
     304---
     305
     306=== View: Guest count per wedding ===
     307A reusable summary of how many guests exist for each wedding.
     308
    280309{{{
    281310CREATE VIEW vw_wedding_guest_count AS
    … …  
    290319
    291320== Conclusion ==
    292 This phase upgrades our PostgreSQL database with advanced mechanisms used in real systems.
    293 Indexes improve performance for scheduling, booking checks and RSVP reports.
    294 Constraints enforce correctness and prevent double bookings and invalid statuses.
    295 Triggers automate business rules such as venue pricing and capacity limits.
    296 Views simplify reporting and provide reusable queries for dashboards and analytics.
    297 
     321In this phase we implemented advanced database mechanisms that improve scalability and correctness of our Wedding Planner system.
     322Indexes help queries run faster as the number of bookings/events grows.
     323Constraints enforce strict data rules and prevent invalid states, such as double-booking.
     324Triggers automate derived values and business rules like venue capacity control.
     325Finally, views provide reusable reports and simplify querying for common summaries.