= Phase P9 — Data Consistency and Integrity =

== 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.