Changes between Initial Version and Version 1 of P9Integrity

Timestamp:

01/14/26 23:44:08 (6 days ago)

Author:

213087

Comment:

--

P9Integrity

@@ +1 @@
+= Phase P9 — Data Consistency and Integrity (Wedding Planner) =
+
+== Overview ==
+This section explains how data consistency and integrity are ensured in the Wedding Planner database system.
+The goal is to prevent invalid, duplicate, or contradictory data by enforcing constraints, foreign keys, cascade rules, and transactional consistency at the database level (PostgreSQL).
+
+The described mechanisms are directly applied to real use cases such as venue booking, wedding ownership, and user-related data.
+
+== Data Integrity Concepts ==
+Data integrity in relational databases is achieved by enforcing rules that guarantee correctness throughout the lifecycle of the data.
+In the Wedding Planner system, integrity is ensured through:
+
+Entity integrity (primary keys)
+
+Referential integrity (foreign keys)
+
+Domain integrity (CHECK, NOT NULL, UNIQUE)
+
+Transactional integrity (ACID properties)
+
+== Referential Integrity and Constraints ==
+
+Referential integrity ensures that relationships between tables remain valid.
+For example, a wedding cannot exist without a valid user, and a venue booking cannot exist without a valid wedding and venue.
+
+=== Core Tables and Constraints ===
+
+|| Table || Purpose ||
+|| user || Stores registered users ||
+|| wedding || Stores wedding events created by users ||
+|| venue || Stores available venues ||
+|| venue_booking || Connects weddings with venues and time slots ||
+
+=== SQL Implementation ===
+
+{{{
+#!sql
+CREATE TABLE "user" (
+    user_id SERIAL PRIMARY KEY,
+    first_name VARCHAR(50) NOT NULL,
+    last_name VARCHAR(50) NOT NULL,
+    email VARCHAR(120) UNIQUE NOT NULL
+);
+
+CREATE TABLE wedding (
+    wedding_id SERIAL PRIMARY KEY,
+    wedding_date DATE NOT NULL,
+    budget NUMERIC(12,2),
+    user_id INTEGER NOT NULL,
+    CONSTRAINT fk_wedding_user
+        FOREIGN KEY (user_id)
+        REFERENCES "user"(user_id)
+        ON DELETE CASCADE
+);
+
+CREATE TABLE venue (
+    venue_id SERIAL PRIMARY KEY,
+    name VARCHAR(120) NOT NULL,
+    capacity INTEGER NOT NULL CHECK (capacity > 0)
+);
+
+CREATE TABLE venue_booking (
+    booking_id SERIAL PRIMARY KEY,
+    venue_id INTEGER NOT NULL,
+    wedding_id INTEGER NOT NULL,
+    booking_date DATE NOT NULL,
+    start_time TIME NOT NULL,
+    end_time TIME NOT NULL,
+    CONSTRAINT fk_booking_venue
+        FOREIGN KEY (venue_id)
+        REFERENCES venue(venue_id)
+        ON DELETE CASCADE,
+    CONSTRAINT fk_booking_wedding
+        FOREIGN KEY (wedding_id)
+        REFERENCES wedding(wedding_id)
+        ON DELETE CASCADE,
+    CONSTRAINT chk_time_valid
+        CHECK (end_time > start_time)
+);
+}}}
+This design prevents orphan records and ensures that deleting a wedding or user automatically removes dependent bookings.
+
+== Preventing Double Booking Using Transactions ==
+
+One of the most critical integrity problems in the Wedding Planner system is preventing multiple weddings from booking the same venue at overlapping times.
+
+This problem cannot be solved reliably without transactions.
+
+=== Transaction-Based Solution ===
+
+{{{
+#!sql
+
+BEGIN;
+
+SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
+
+SELECT 1
+FROM venue_booking
+WHERE venue_id = 1
+  AND booking_date = '2026-06-20'
+  AND NOT (end_time <= '16:00' OR start_time >= '18:00')
+FOR UPDATE;
+
+INSERT INTO venue_booking (
+  venue_id, wedding_id, booking_date, start_time, end_time
+)
+VALUES (
+  1, 1, '2026-06-20', '16:00', '18:00'
+);
+
+COMMIT;
+
+
+}}}
+
+The transaction guarantees that:
+
+Availability is checked and reserved atomically
+
+Concurrent booking attempts cannot create overlaps
+
+Conflicting transactions are rolled back safely
+
+== Database-Level Overlap Protection (PostgreSQL) ==
+
+PostgreSQL allows enforcing scheduling rules declaratively using exclusion constraints.
+
+=== Exclusion Constraint for Venues ===
+
+{{{
+#!sql
+
+CREATE EXTENSION IF NOT EXISTS btree_gist;
+
+ALTER TABLE venue_booking
+ADD COLUMN booking_range tsrange
+GENERATED ALWAYS AS (
+  tsrange(
+    booking_date + start_time,
+    booking_date + end_time
+  )
+) STORED;
+
+ALTER TABLE venue_booking
+ADD CONSTRAINT venue_no_overlap
+EXCLUDE USING gist (
+  venue_id WITH =,
+  booking_range WITH &&
+);
+
+
+}}}
+
+This constraint guarantees at the database level that a venue cannot be booked twice for overlapping time intervals, regardless of application logic.
+
+== Cascade Rules and Design Decisions ==
+
+Different cascade strategies are used depending on business logic.
+
+|| Rule || Usage in Wedding Planner ||
+|| ON DELETE CASCADE || Deleting a wedding removes all related bookings automatically ||
+|| ON DELETE RESTRICT || Prevents deleting users with active weddings ||
+|| ON DELETE SET NULL || Keeps historical records if service providers are removed ||
+
+These decisions ensure both data correctness and auditability.
+
+== Best Practices Applied ==
+
+Integrity rules are enforced at the database level
+
+Application logic complements, but does not replace, constraints
+
+Transactions are used for multi-step operations
+
+Scheduling conflicts are handled atomically
+
+Referential actions reflect real business semantics
+
+== Conclusion ==
+By combining relational constraints, foreign keys, cascade rules, and transactional control, the Wedding Planner database ensures strong consistency and integrity.
+The system prevents invalid states such as orphan records, overlapping bookings, and partial updates, while remaining scalable and safe for concurrent usage.
+
+This approach aligns with real-world production database standards and fully supports the functional requirements of the Wedding Planner project.