| 63 | | === Constraint 1: Prevent overlapping bookings (no double booking) === |
| 64 | | One of the most important business rules in Wedding Planner is that a resource cannot be booked twice in overlapping time intervals. |
| 65 | | This is enforced with EXCLUDE constraints using GiST indexes. |
| 66 | | |
| 67 | | First, we enable required extension: |
| 68 | | |
| 69 | | {{{ |
| 70 | | CREATE EXTENSION IF NOT EXISTS btree_gist; |
| 71 | | }}} |
| 72 | | |
| 73 | | Then we add generated timestamp columns which combine date + start_time/end_time: |
| 74 | | |
| 75 | | {{{ |
| 76 | | ALTER TABLE venue_booking |
| 77 | | ADD COLUMN start_ts TIMESTAMP NOT NULL GENERATED ALWAYS AS ("date" + start_time) STORED, |
| 78 | | ADD COLUMN end_ts TIMESTAMP NOT NULL GENERATED ALWAYS AS ("date" + end_time) STORED; |
| 79 | | |
| 80 | | ALTER TABLE photographer_booking |
| 81 | | ADD COLUMN start_ts TIMESTAMP NOT NULL GENERATED ALWAYS AS ("date" + start_time) STORED, |
| 82 | | ADD COLUMN end_ts TIMESTAMP NOT NULL GENERATED ALWAYS AS ("date" + end_time) STORED; |
| 83 | | |
| 84 | | ALTER TABLE band_booking |
| 85 | | ADD COLUMN start_ts TIMESTAMP NOT NULL GENERATED ALWAYS AS ("date" + start_time) STORED, |
| 86 | | ADD COLUMN end_ts TIMESTAMP NOT NULL GENERATED ALWAYS AS ("date" + end_time) STORED; |
| 87 | | }}} |
| 88 | | |
| 89 | | Finally, we enforce overlap prevention: |
| 90 | | |
| 91 | | {{{ |
| 92 | | ALTER TABLE venue_booking |
| 93 | | ADD CONSTRAINT exclude_overlap |
| 94 | | EXCLUDE USING gist ( |
| 95 | | venue_id WITH =, |
| 96 | | tsrange(start_ts, end_ts) WITH && |
| 97 | | ); |
| 98 | | |
| 99 | | ALTER TABLE photographer_booking |
| 100 | | ADD CONSTRAINT exclude_overlap_pb |
| 101 | | EXCLUDE USING gist ( |
| 102 | | photographer_id WITH =, |
| 103 | | tsrange(start_ts, end_ts) WITH && |
| 104 | | ); |
| 105 | | |
| 106 | | ALTER TABLE band_booking |
| 107 | | ADD CONSTRAINT excl_bb_no_overlap |
| 108 | | EXCLUDE USING gist ( |
| 109 | | band_id WITH =, |
| 110 | | tsrange(start_ts, end_ts) WITH && |
| 111 | | ); |
| 112 | | }}} |
| 113 | | |
| 114 | | *Why this matters:* |
| 115 | | This guarantees that a venue/band/photographer cannot be double booked for overlapping time ranges, even if the application sends incorrect input. |
| 116 | | |
| 117 | | === Constraint 2: CHECK constraints for valid values === |
| 118 | | To prevent invalid statuses and roles, we added CHECK constraints. |
| 119 | | |
| 120 | | {{{ |
| 121 | | ALTER TABLE event |
| 122 | | ADD CONSTRAINT chk_event_status |
| 123 | | CHECK (status IN ('scheduled', 'confirmed', 'cancelled')); |
| 124 | | |
| 125 | | ALTER TABLE event_rsvp |
| 126 | | ADD CONSTRAINT chk_event_rsvp_status |
| 127 | | CHECK (status IN ('accepted', 'declined', 'pending')); |
| 128 | | |
| 129 | | ALTER TABLE venue_booking |
| 130 | | ADD CONSTRAINT chk_venue_booking_status |
| 131 | | CHECK (status IN ('pending', 'confirmed', 'cancelled')); |
| 132 | | |
| 133 | | ALTER TABLE photographer_booking |
| 134 | | ADD CONSTRAINT chk_photographer_booking_status |
| 135 | | CHECK (status IN ('pending', 'confirmed', 'cancelled')); |
| 136 | | |
| 137 | | ALTER TABLE band_booking |
| 138 | | ADD CONSTRAINT chk_band_booking_status |
| 139 | | CHECK (status IN ('pending', 'confirmed', 'cancelled')); |
| 140 | | |
| 141 | | ALTER TABLE attendance |
| 142 | | ADD CONSTRAINT chk_attendance_status |
| 143 | | CHECK (status IN ('attending', 'not_attending')); |
| 144 | | |
| 145 | | ALTER TABLE wedding |
| 146 | | ADD CONSTRAINT chk_budget |
| 147 | | CHECK (budget > 0); |
| 148 | | |
| 149 | | ALTER TABLE attendance |
| 150 | | ADD CONSTRAINT chk_att_role |
| 151 | | CHECK (role IN ('Guest', 'Bride', 'Groom', 'Best Man', 'Maid of Honor')); |
| 152 | | }}} |
| 153 | | |
| 154 | | *Why this matters:* |
| 155 | | The database rejects invalid values automatically, ensuring consistent and predictable data for the application. |
| 156 | | |
| 157 | | == Triggers == |
| 158 | | Triggers automate important derived logic and enforce real-world constraints. |
| 159 | | |
| 160 | | === Trigger 1: Automatic venue price calculation === |
| 161 | | This trigger automatically calculates venue booking price if it is not provided, based on: |
| 162 | | * venue.price_per_guest |
| 163 | | * number of guests for the wedding |
| 164 | | |
| 165 | | {{{ |
| 166 | | CREATE OR REPLACE FUNCTION calc_venue_price() |
| 167 | | RETURNS trigger AS $$ |
| 168 | | BEGIN |
| 169 | | IF NEW.price IS NULL THEN |
| 170 | | SELECT price_per_guest * COUNT(*) |
| 171 | | INTO NEW.price |
| 172 | | FROM venue v |
| 173 | | JOIN guest g ON g.wedding_id = NEW.wedding_id |
| 174 | | WHERE v.venue_id = NEW.venue_id; |
| 175 | | END IF; |
| 176 | | RETURN NEW; |
| 177 | | END; |
| 178 | | $$ LANGUAGE plpgsql; |
| 179 | | |
| 180 | | CREATE TRIGGER trg_calc_venue_price |
| 181 | | BEFORE INSERT ON venue_booking |
| 182 | | FOR EACH ROW |
| 183 | | EXECUTE FUNCTION calc_venue_price(); |
| 184 | | }}} |
| 185 | | |
| 186 | | *Why this matters:* |
| 187 | | Price is a derived field. This ensures it is always correct and not dependent only on application-level calculations. |
| 188 | | |
| 189 | | === Trigger 2: Venue capacity enforcement === |
| 190 | | This trigger prevents inserting/updating guests when the number of guests exceeds the confirmed venue capacity. |
| 191 | | |
| 192 | | {{{ |
| 193 | | CREATE OR REPLACE FUNCTION check_guest_capacity() |
| 194 | | RETURNS trigger AS $$ |
| 195 | | DECLARE |
| 196 | | guest_count INTEGER; |
| 197 | | venue_cap INTEGER; |
| 198 | | BEGIN |
| 199 | | SELECT v.capacity |
| 200 | | INTO venue_cap |
| 201 | | FROM venue_booking vb |
| 202 | | JOIN venue v ON v.venue_id = vb.venue_id |
| 203 | | WHERE vb.wedding_id = NEW.wedding_id |
| 204 | | AND vb.status = 'confirmed'; |
| 205 | | |
| 206 | | IF venue_cap IS NULL THEN |
| 207 | | RETURN NEW; |
| 208 | | END IF; |
| 209 | | |
| 210 | | SELECT COUNT(*) INTO guest_count |
| 211 | | FROM guest |
| 212 | | WHERE wedding_id = NEW.wedding_id |
| 213 | | AND (TG_OP = 'INSERT' OR guest_id <> OLD.guest_id); |
| 214 | | |
| 215 | | IF guest_count + 1 > venue_cap THEN |
| 216 | | RAISE EXCEPTION |
| 217 | | 'Cannot add guest: venue capacity (%) exceeded', |
| 218 | | venue_cap; |
| 219 | | END IF; |
| 220 | | |
| 221 | | RETURN NEW; |
| 222 | | END; |
| 223 | | $$ LANGUAGE plpgsql; |
| 224 | | |
| 225 | | CREATE TRIGGER trg_guest_capacity |
| 226 | | BEFORE INSERT OR UPDATE ON guest |
| 227 | | FOR EACH ROW |
| 228 | | EXECUTE FUNCTION check_guest_capacity(); |
| 229 | | }}} |
| 230 | | |
| 231 | | *Why this matters:* |
| 232 | | This enforces a critical business rule: a wedding cannot have more guests than venue capacity. |
| 233 | | |
| 234 | | == Views == |
| 235 | | Views simplify reporting and provide useful aggregated results. |
| 236 | | |
| 237 | | === View 1: RSVP summary per event === |
| 238 | | This view returns counts of accepted/declined/pending RSVP answers for each event. |
| 239 | | |
| 240 | | {{{ |
| 241 | | CREATE VIEW vw_event_rsvp_summary AS |
| 242 | | SELECT |
| 243 | | event_id, |
| 244 | | SUM(CASE WHEN status = 'accepted' THEN 1 ELSE 0 END) AS yes_count, |
| 245 | | SUM(CASE WHEN status = 'declined' THEN 1 ELSE 0 END) AS no_count, |
| 246 | | SUM(CASE WHEN status = 'pending' THEN 1 ELSE 0 END) AS maybe_count |
| 247 | | FROM event_rsvp |
| 248 | | GROUP BY event_id; |
| 249 | | }}} |
| 250 | | |
| 251 | | === View 2: Confirmed bookings by date (all service providers) === |
| 252 | | This view unifies confirmed bookings (venue, photographer, band) into one report. |
| 253 | | |
| 254 | | {{{ |
| 255 | | CREATE VIEW vw_confirmed_bookings_by_date AS |
| 256 | | SELECT |
| 257 | | "date", |
| 258 | | 'VENUE' AS booking_type, |
| 259 | | venue_id AS resource_id, |
| 260 | | start_time, |
| 261 | | end_time |
| 262 | | FROM venue_booking |
| 263 | | WHERE status = 'confirmed' |
| 264 | | |
| 265 | | UNION ALL |
| 266 | | |
| 267 | | SELECT |
| 268 | | "date", |
| 269 | | 'PHOTOGRAPHER' AS booking_type, |
| 270 | | photographer_id AS resource_id, |
| 271 | | start_time, |
| 272 | | end_time |
| 273 | | FROM photographer_booking |
| 274 | | WHERE status = 'confirmed' |
| 275 | | |
| 276 | | UNION ALL |
| 277 | | |
| 278 | | SELECT |
| 279 | | "date", |
| 280 | | 'BAND' AS booking_type, |
| 281 | | band_id AS resource_id, |
| 282 | | start_time, |
| 283 | | end_time |
| 284 | | FROM band_booking |
| 285 | | WHERE status = 'confirmed'; |
| 286 | | }}} |
| 287 | | |
| 288 | | === View 3: Guest count per wedding === |
| 289 | | This view counts guests grouped by wedding_id. |
| 290 | | |
| 291 | | {{{ |
| 292 | | CREATE VIEW vw_wedding_guest_count AS |
| 293 | | SELECT |
| 294 | | wedding_id, |
| 295 | | COUNT(*) AS total_guests |
| 296 | | FROM guest |
| 297 | | GROUP BY wedding_id; |
| 298 | | }}} |
| 299 | | |
| 300 | | == Proof / Screenshots == |
| 301 | | To confirm that Phase P7 is implemented in PostgreSQL (DBeaver), we provide screenshots of successful execution and created objects: |
| 302 | | |
| 303 | | * Indexes list created in schema: [[Image(P7_indexes_list.png)]] |
| 304 | | * EXCLUDE constraint for overlap prevention: [[Image(P7_exclude_overlap_band_booking.png)]] |
| 305 | | * Trigger function execution in DBeaver: [[Image(P7_trigger_function_capacity.png)]] |
| 306 | | * Created view in schema and executed script: [[Image(P7_view_wedding_guest_count.png)]] |
| 307 | | |
| 308 | | == Conclusion == |
| 309 | | 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. |
| 310 | | With these mechanisms, the database becomes robust, consistent, and production-ready. |
| | 58 | === [[Testing and Validation]] === |
| | 59 | This section contains example SQL testing scenarios used to validate the advanced database functionality. |
