= Relational Design

== Description
This page describes the relational database design of the Wedding Planner system. The relational model is derived directly from the final ER model and defines the database tables, their attributes, primary keys, foreign keys, and integrity constraints. The design ensures data consistency, avoids redundancy, and supports all system requirements.

== Diagram
[[Image(final_version.png, width=100%)]]

== Relational Schema

== Table: user
Stores registered users who organize weddings.

user(
    user_id PK,
    first_name,
    last_name,
    email UNIQUE,
    phone_number,
    gender,
    birthday
)

== Table: wedding
Represents weddings created and managed by users. Acts as the central table of the system.

wedding(
    wedding_id PK,
    date,
    budget,
    notes,
    type,
    status,
    user_id FK → user(user_id)
)

== Table: church
Represents churches where wedding ceremonies can take place. Each wedding can be associated with one church.

church(
    church_id PK,
    name,
    location,
    contact,
    wedding_id FK → wedding(wedding_id) UNIQUE
)

== Table: priest
Represents priests associated with churches.

priest(
    priest_id PK,
    name,
    contact,
    church_id FK → church(church_id)
)

== Table: event
Represents events that are part of a wedding (e.g. ceremony, reception).

event(
    event_id PK,
    event_type,
    date,
    start_time,
    end_time,
    status,
    wedding_id FK → wedding(wedding_id)
)

== Table: guest
Represents guests invited to a wedding.

guest(
    guest_id PK,
    first_name,
    last_name,
    email,
    wedding_id FK → wedding(wedding_id)
)

== Table: event_rsvp
Stores guest responses to event invitations. Resolves the many-to-many relationship between guest and event.

event_rsvp(
    response_id PK,
    status,
    response_date,
    guest_id FK → guest(guest_id),
    event_id FK → event(event_id),
    UNIQUE (guest_id, event_id)
)

== Table: attendance
Represents actual guest participation in events, including seating and role information.

attendance(
    attendance_id PK,
    status,
    table_number,
    role,
    guest_id FK → guest(guest_id),
    event_id FK → event(event_id),
    UNIQUE (guest_id, event_id)
)

== Table: venue_type
Defines categories of venues.

venue_type(
    type_id PK,
    type_name UNIQUE
)

== Table: venue
Represents venues available for wedding events.

venue(
    venue_id PK,
    name,
    location,
    city,
    address,
    capacity,
    menu,
    phone_number,
    price_per_guest,
    type_id FK → venue_type(type_id)
)

== Table: venue_booking
Represents reservations of venues for weddings.

venue_booking(
    booking_id PK,
    date,
    start_time,
    end_time,
    status,
    price,
    venue_id FK → venue(venue_id),
    wedding_id FK → wedding(wedding_id)
)

== Table: photographer
Represents photographers available for weddings.

photographer(
    photographer_id PK,
    name,
    email UNIQUE,
    phone_number,
    price_per_hour
)

== Table: photographer_booking
Represents photographer reservations for weddings.

photographer_booking(
    booking_id PK,
    date,
    start_time,
    end_time,
    status,
    photographer_id FK → photographer(photographer_id),
    wedding_id FK → wedding(wedding_id)
)

== Table: band
Represents music bands available for weddings.

band(
    band_id PK,
    band_name,
    genre,
    equipment,
    phone_number,
    price_per_hour
)

== Table: band_booking
Represents band reservations for weddings.

band_booking(
    booking_id PK,
    date,
    start_time,
    end_time,
    status,
    band_id FK → band(band_id),
    wedding_id FK → wedding(wedding_id)
)

== Table: registrar
Represents civil registrars who can officiate weddings.

registrar(
    registrar_id PK,
    name,
    contact,
    location,
    working_hours
)

== Table: registrar_booking
Represents registrar reservations.

registrar_booking(
    booking_id PK,
    date,
    start_time,
    end_time,
    status,
    registrar_id FK → registrar(registrar_id)
)

== Integrity Constraints

* Primary keys uniquely identify each table row.
* Foreign keys enforce referential integrity between related tables.
* UNIQUE constraints prevent duplicate RSVP and attendance records.
* CHECK constraints ensure valid time intervals (end_time > start_time).
* Cascading rules ensure consistent updates and deletions.

== Mapping from ER Model
Each entity in the ER model is mapped to a table.
Many-to-many relationships are resolved using associative tables (event_rsvp, attendance, venue_booking, band_booking, photographer_booking).
All booking entities are associated directly with wedding, reflecting the final ER model design.

== Relational Design History

v0.1 – Initial relational schema  
v0.2 – Alignment with updated ER model  
v0.3 – Removed incorrect event-level reservations  
v0.4 – Refined constraints and keys  
v0.5 – Final relational design aligned with ER Model Version 5