Changes between Initial Version and Version 1 of RelationModel

Timestamp:

04/18/26 13:46:04 (2 weeks ago)

Author:

231116

Comment:

--

RelationModel

@@ +1 @@
+[[Image(er_diagram)]]
+
+
+= Entities
+
+== User
+`User` is the supertype of every account in the system. It centralizes shared identity, authentication and contact data so that the three subtypes (Guest, Host, Admin) do not have to redeclare the same attributes. Modeling it as a separate entity also simplifies authentication and authorization logic, because login always resolves to a single `user_id` regardless of role.
+
+**Candidate keys:**
+- `user_id` — surrogate numeric identifier (chosen primary key).
+- `email` — natural unique key, but it can change over time and may be reused, so it is kept only as a UNIQUE constraint.
+
+`user_id` is chosen as the primary key because it is stable, and independent of business changes.
+
+**Attributes:**
+- `user_id` — numeric, required. Auto-generated; primary key.
+- `email` — text, required, unique. Must follow email format.
+- `password_hash` — text, required. Hashed credential, never stored as plain text.
+- `first_name` — text, required. Max length ~255.
+- `last_name` — text, required. Max length ~255.
+- `phone` — text, optional. Free format up to ~30 characters.
+- `created_at` — datetime, required. Set automatically when the account is created.
+- `updated_at` — datetime, optional. Updated whenever the profile is changed.
+
+=== Guest
+`Guest` is the specialization of `User` for accounts that browse, favorite and book rooms. It is modeled as a subtype because only some users actually consume the booking side of the platform, and many constraints (e.g. “only a guest can make a booking”) are easier to express against this subtype.
+
+**Candidate keys:**
+- `guest_id` — inherited from `User`; serves as both primary key and foreign key to `User`.
+
+**Attributes:** no additional attributes beyond those of `User`.
+
+=== Host
+`Host` is the specialization of `User` for accounts that publish and manage properties. A user becomes a Host only after submitting a `HostApplication` that an `Admin` approves, so this subtype also exists to express that promotion explicitly.
+
+**Candidate keys:**
+- `host_id` — inherited from `User`; primary key and foreign key to `User`.
+
+**Attributes:** no additional attributes beyond those of `User`.
+
+=== Admin
+`Admin` is the specialization of `User` for accounts with administrative privileges (reviewing host applications, moderating content). It is modeled as a subtype to keep authorization rules clean and to allow other entities to reference an administrator specifically (for example as the reviewer of a host application).
+
+**Candidate keys:**
+- `admin_id` — inherited from `User`; primary key and foreign key to `User`.
+
+**Attributes:** no additional attributes beyond those of `User`.
+
+== HostApplication
+Represents the request a `Guest` submits when they want to become a `Host`. It is modeled as its own entity to support an approval workflow without overloading `User` with workflow attributes, and to keep an auditable history of who applied, when, and which admin reviewed it.
+
+**Candidate keys:**
+- `application_id` — surrogate numeric identifier.
+- `(user_id, application_date)` could function as a natural key but is not reliably unique, so only the surrogate is used.
+
+`application_id` was chosen as a primary key.
+
+**Attributes:**
+- `application_id` — numeric, required. Auto-generated primary key.
+- `user_id` — numeric, required. References the guest submitting the application.
+- `application_date` — date, optional. When the application was filed.
+- `status` — text, required. Allowed values: `{PENDING, APPROVED, REJECTED}`.
+- `reviewed_by_admin_id` — numeric, optional. The admin who processed the application.
+- `review_date` — date, optional. When the application was reviewed.
+- `rejection_reason` — text, optional. Up to ~500 characters; only meaningful when `status = REJECTED`.
+
+== Property
+Represents a physical accommodation owned by a host (a house, an apartment building, a hotel, etc.). It is modeled as a separate entity because a property is the natural unit of ownership, location, and policy, and because it groups one or more bookable rooms underneath it.
+
+**Candidate keys:**
+- `property_id` — surrogate numeric identifier (chosen primary key).
+
+**Attributes:**
+- `property_id` — numeric, required. Auto-generated primary key.
+- `host_id` — numeric, required. The host who owns the property.
+- `title` — text, required. Max length ~255.
+- `description` — text, optional. Up to ~500 characters.
+- `base_price` — numeric, required. Reference price; must be ≥ 0.
+- `address_id` — numeric, required. Foreign key to `Address`.
+- `listing_type_id` — numeric, required. Foreign key to `ListingType`.
+- `max_guests` — integer, required. Must be > 0.
+- `status` — text, required. Allowed values such as `{ACTIVE, INACTIVE, PENDING}`.
+- `created_at` — datetime, required.
+- `updated_at` — datetime, optional.
+
+== ListingType
+A small reference table describing what kind of listing a property is (e.g. *Apartment*, *House*, *Hotel*, *Hostel*). Modeled as a separate entity so the set of allowed types is centrally managed and easily extended without altering `Property`.
+
+**Candidate keys:**
+- `property_type_id` — surrogate primary key.
+- `type_name` — natural unique key.
+
+`property_type_id` was chosen as a primary key because it is stable and independent of changes.
+
+**Attributes:**
+- `property_type_id` — numeric, required. Primary key.
+- `type_name` — text, required, unique.
+- `description` — text, optional. Up to ~500 characters.
+
+== Room
+Represents an individual bookable unit inside a `Property`. It is modeled separately from `Property` because pricing, capacity, availability, and amenities are most naturally tracked per room, not per building, and because a single property can contain many rooms with different characteristics.
+
+**Candidate keys:**
+- `room_id` — surrogate primary key.
+
+**Attributes:**
+- `room_id` — numeric, required. Auto-generated primary key.
+- `property_id` — numeric, required. The property this room belongs to.
+- `room_type_id` — numeric, required. Foreign key to `RoomType`.
+- `room_name` — text, required. Max length ~100.
+- `capacity` — integer, required. Standard number of guests; must be > 0.
+- `extra_capacity` — integer, required. Additional guests allowed beyond standard.
+- `price_per_night` — numeric, required. Must be ≥ 0.
+- `extra_guest_price` — numeric, optional. Surcharge per extra guest.
+- `description` — text, optional. Up to ~500 characters.
+- `status` — text, required. Allowed values such as `{AVAILABLE, UNAVAILABLE, MAINTENANCE}`.
+
+== RoomType
+A reference table describing the kind of room (e.g. *Single*, *Double*, *Suite*, *Dorm*). It is modeled as a separate entity for the same reasons as `ListingType`: to keep the controlled vocabulary in one place.
+
+**Candidate keys:**
+- `room_type_id` — surrogate primary key.
+- `type_name` — natural key.
+
+**Attributes:**
+- `room_type_id` — numeric, required.
+- `type_name` — text, required. Max length ~100.
+- `description` — text, optional.
+
+== Amenity
+Represents a feature that can be offered either by a whole property (e.g. Pool, Parking) or by a specific room (e.g. Air conditioning, Smart TV). It is modeled as a shared catalog so the same amenity does not have to be duplicated for every property or room.
+
+**Candidate keys:**
+- `amenety_id` — surrogate primary key.
+
+**Attributes:**
+- `amenety_id` — numeric, required.
+- `amenity_name` — text, required. Max length ~100.
+- `type` — text, required. Indicates whether the amenity applies to a property, a room, or both.
+- `description` — text, optional.
+
+== Address
+Represents the postal location of a property. It is modeled as a separate entity so that address data can be normalized and referenced consistently, and so a country lookup can be enforced via foreign key rather than free text.
+
+**Candidate keys:**
+- `address_id` — surrogate primary key.
+
+**Attributes:**
+- `address_id` — numeric, required.
+- `country_id` — numeric, required. Foreign key to `Country`.
+- `city` — text, required. Max length ~100.
+- `street` — text, required. Max length ~100.
+- `zip_code` — text, required. Max length ~50.
+
+== Country
+A reference table of countries used by `Address`. Modeled separately so country names and ISO codes are managed in one place.
+
+**Candidate keys:**
+- `country_id` — surrogate primary key.
+- `country_code` — natural unique key (e.g. ISO 3166).
+
+`country_id` was chosen as a primary key because it is stable and country codes could be prone to changes.
+
+**Attributes:**
+- `country_id` — numeric, required.
+- `country_name` — text, required.
+- `country_code` — text, required, unique.
+
+== Image
+Represents a photo attached to some entity in the system (a property, a room, or another listing-related object). It is modeled as a polymorphic entity (`entity_type` + `entity_id`) so that the same image table can serve different owners without duplicating the structure.
+
+**Candidate keys:**
+- `image_id` — surrogate primary key.
+
+**Attributes:**
+- `image_id` — numeric, required.
+- `entity_type` — text, required. Identifies the kind of owner (e.g. `PROPERTY`, `ROOM`).
+- `entity_id` — numeric, required. The id of the owning entity.
+- `url` — text, required. Must be a valid URL.
+- `alt_text` — text, optional. Accessibility description.
+- `is_cover` — boolean, required. Marks the main/cover image.
+- `sort_order` — integer, optional. Display order.
+- `uploaded_at` — datetime, required.
+
+== Availability
+Represents a window of time during which a specific room is offered (or explicitly blocked). It is modeled as its own entity so that availability rules can vary per room and over time, and so the system can validate bookings against concrete windows rather than implicit assumptions.
+
+**Candidate keys:**
+- `availability_id` — surrogate primary key.
+
+**Attributes:**
+- `availability_id` — numeric, required.
+- `room_id` — numeric, required. The room this window applies to.
+- `available_from` — date, required.
+- `available_to` — date, required. Must be ≥ `available_from`.
+- `available_time_from` — datetime, required. 
+- `avaliable_time_to` — datetime, required. 
+- `is_available` — boolean, required. `TRUE` for open windows, `FALSE` for blocked windows.
+- `description` — text, optional.
+
+== Booking
+Represents a confirmed (or pending) reservation made by a `Guest` for a specific `Room` over a date range. It is modeled as a central transactional entity because almost every business operation, like payment, review, discount, cancellation is anchored to a booking.
+
+**Candidate keys:**
+- `booking_id` — surrogate primary key.
+
+**Attributes:**
+- `booking_id` — numeric, required.
+- `guest_id` — numeric, required. The guest who made the booking.
+- `room_id` — numeric, required. The room being booked.
+- `check_in_date` — date, required.
+- `check_out_date` — date, required. Must be > `check_in_date`.
+- `guests_count` — integer, required. Must be > 0 and ≤ room capacity (incl. extra).
+- `total_price` — numeric, required. Final amount after discounts/extras.
+- `booking_status` — text, required. Allowed values such as `{PENDING, CONFIRMED, CANCELLED, COMPLETED}`.
+- `booked_at` — datetime, required.
+
+== Payment
+Represents the financial transaction associated with a booking. It is modeled separately so that different payment methods can be supported uniformly and so the payment state is tracked independently from the booking lifecycle.
+
+**Candidate keys:**
+- `payment_id` — surrogate primary key.
+
+**Attributes:**
+- `payment_id` — numeric, required.
+- `booking_id` — numeric, optional. The booking being paid for.
+- `payment_method_id` — numeric, required.
+- `amount` — numeric, required. Must be ≥ 0.
+- `payment_status` — text, required. Allowed values such as `{PENDING, PAID, FAILED, REFUNDED}`.
+- `paid_at` — datetime, optional. Set when payment succeeds.
+
+== PaymentMethod
+A reference table describing supported ways to pay (e.g. Credit Card, PayPal, Bank Transfer). Modeled separately so methods can be added without altering `Payment`.
+
+**Candidate keys:**
+- `payment_method_id` — surrogate primary key.
+
+**Attributes:**
+- `payment_method_id` — numeric, required.
+- `method_name` — text, required.
+- `description` — text, optional.
+
+== Discount
+Represents a promotional code created by a host that can be applied to bookings. It is modeled as its own entity so multiple bookings can reuse the same discount and so validity windows and statuses are tracked centrally.
+
+**Candidate keys:**
+- `discount_id` — surrogate primary key.
+
+**Attributes:**
+- `discount_id` — numeric, required.
+- `host_id` — numeric, required. The host who created the discount.
+- `code` — text, required. Promo code.
+- `title` — text, required.
+- `description` — text, optional.
+- `discount_type` — text, required. Allowed values such as `{PERCENT, FIXED}`.
+- `discount_value` — numeric, required. Must be > 0.
+- `valid_from` — date, required.
+- `valid_to` — date, required. Must be ≥ `valid_from`.
+- `is_active` — boolean, required.
+
+== Review
+Represents feedback written by a `Guest` about a `Property` after staying there. Modeled as its own entity so that reviews can be queried, aggregated, and moderated independently.
+
+**Candidate keys:**
+- `review_id` — surrogate primary key.
+
+**Attributes:**
+- `review_id` — numeric, required.
+- `booking_id` — numeric, required. The booking the review is based on.
+- `guest_id` — numeric, required. The author.
+- `property_id` — numeric, required. The reviewed property.
+- `rating` — integer, required. Constrained to the interval `[1, 5]`.
+- `comment` — text, optional.
+- `created_at` — datetime, required.
+
+== FavoriteListing
+Represents the “save for later” relationship between a guest and a property. It is modeled as a dedicated entity (rather than a flag on `Property`) because it is a many-to-many association with its own timestamp.
+
+**Candidate keys:**
+- `(user_id, property_id)` — composite natural primary key.
+
+**Attributes:**
+- `user_id` — numeric, required. The guest favoriting the property.
+- `property_id` — numeric, required.
+- `created_at` — datetime, optional. When the favorite was added.
+
+== Notification
+Represents a system-generated message delivered to a user (booking confirmations, host application updates, new reviews, etc.). It is necessary for timely communication and is modeled as an entity tied to one recipient at a time.
+
+Candidate keys:
+- `notification_id` — surrogate primary key.
+
+Attributes:
+- `notification_id` — numeric, required.
+- `user_id` — numeric, required. Recipient.
+- `message` — text, required. Human-readable body.
+- `sent_at` — datetime, required.
+- `is_read` — boolean, required. Defaults to `FALSE`; becomes `TRUE` when viewed.
+
+== CancellationPolicy
+Represents the refund rules that a property applies when a booking is cancelled. It is modeled as a reusable entity because the same policy can be attached to multiple properties via the join table `CancellationPolicy_Property`.
+
+Candidate keys:
+- `policy_id` — surrogate primary key.
+
+Attributes:
+- `policy_id` — numeric, required.
+- `property_id` — numeric, required. The property the policy is linked to.
+- `policy_name` — text, optional. Human-readable label (e.g. Flexible, Strict).
+- `description` — text, optional.
+- `refund_percentage` — integer, optional. Percentage (0–100) refunded when conditions are met.
+- `days_before_checking` — integer, optional. Cancellation deadline relative to check-in.
+
+= Relationships
+
+`is_a (User – Guest / Host / Admin)` [[BR]]
+Models the user specialization hierarchy. `User` is the supertype; `Guest`, `Host`, and `Admin` are disjoint subtypes that inherit `user_id`. Modeled this way so that login and identity remain centralized while role-specific data and constraints live with the appropriate subtype.
+
+Keys: each subtype is identified by the inherited `user_id`, which also acts as a foreign key to `User`.
+
+----
+
+`applied (Guest – HostApplication)` [[BR]]
+A guest can submit applications to become a host. Modeled as 1–N: one guest can submit many applications over time, but each application is submitted by exactly one guest. Implemented via the foreign key `user_id` in `HostApplication`.
+
+----
+
+`reviewed_by (Admin – HostApplication)` [[BR]]
+An admin can review host applications (approve or reject them). Modeled as 1–N: one admin can review many applications, while each application is reviewed by at most one admin. Implemented via `reviewed_by_admin_id` in `HostApplication`. The relationship is optional because a freshly submitted application has not yet been reviewed.
+
+----
+
+`created_by (Host – Property)` [[BR]]
+Represents that a property is owned/published by a host. Modeled as 1–N: a host may publish many properties, while each property has exactly one host. Implemented through the foreign key `host_id` in `Property`.
+
+----
+
+`located_at (Property – Address)` [[BR]]
+Each property has a single address. Modeled as N–1 from `Property` to `Address`. Implemented through the foreign key `address_id` in `Property`.
+
+----
+
+`base_country (Address – Country)` [[BR]]
+Each address belongs to exactly one country, and each country can appear in many addresses. Modeled as N–1, implemented through `country_id` in `Address`.
+
+----
+
+`of_type (Property – ListingType)` [[BR]]
+Classifies each property as a specific listing type (apartment, hotel, etc.). Modeled as N–1 from `Property` to `ListingType`, implemented through `listing_type_id` in `Property`.
+
+----
+
+`part_of (Property – Room)` [[BR]]
+Represents that a room is part of a property. Modeled as 1–N: a property contains many rooms, while each room belongs to exactly one property. Implemented via `property_id` in `Room`.
+
+----
+
+`of_room_type (Room – RoomType)` [[BR]]
+Classifies each room (single, double, suite, …). Modeled as N–1 from `Room` to `RoomType`, implemented through `room_type_id` in `Room`.
+
+----
+
+`has_amenity (Property – Amenity) and (Room – Amenity)` [[BR]]
+Represents which amenities are offered by a property or a room. Both are modeled as M–N relationships through the join tables `PropertyAmenity` and `RoomAmenity`. The same amenity can be reused across many properties and rooms, and a property/room can offer many amenities.
+
+----
+
+`shows_image (Room – Image)` [[BR]]
+Represents the photos attached to a room. Modeled as 1–N from the owning entity to `Image`, implemented polymorphically via the `entity_type` and `entity_id` columns in `Image`.
+
+----
+
+`is_available (Room – Availability)` [[BR]]
+Represents the time windows during which a room is offered or blocked. Modeled as 1–N: a room has many availability windows, while each window belongs to exactly one room. Implemented through `room_id` in `Availability`.
+
+----
+
+`makes (Guest – Booking)` [[BR]]
+Represents that a guest creates bookings. Modeled as 1–N: a guest can have many bookings, while each booking is made by exactly one guest. Implemented through `guest_id` in `Booking`.
+
+----
+
+`books (Booking – Room)` [[BR]]
+Represents which room a booking is for. Modeled as N–1 from `Booking` to `Room`: a room can be booked many times across different date ranges, while each booking refers to exactly one room. Implemented through `room_id` in `Booking`.
+
+----
+
+`applies_to (Availability – Booking)` [[BR]]
+Links bookings to the availability windows they consume. Modeled as M–N through the join table `Availability_Booking`, since a booking may span more than one availability window and a window may contain multiple bookings (on different sub-dates).
+
+----
+
+`paid_with (Booking – Payment)` [[BR]]
+Represents the financial settlement of a booking. Modeled as N–1: multiple bookings can be associated with a single payment (e.g., batch payments), while each booking references one payment. Implemented through `booking_id` in `Payment` (and a back-reference in `Booking`).
+
+----
+
+`pay_with (Payment – PaymentMethod)` [[BR]]
+Represents the method used to settle a payment (card, PayPal, etc.). Modeled as N–1 from `Payment` to `PaymentMethod`, implemented through `payment_method_id` in `Payment`.
+
+----
+
+`created_discount (Host – Discount)` [[BR]]
+Represents that a host creates promotional discounts. Modeled as 1–N: a host can create many discounts, while each discount belongs to one host. Implemented through `host_id` in `Discount`.
+
+----
+
+`applied_to (Booking – Discount)` [[BR]]
+Represents that one or more discounts can be applied to a booking. Modeled as M–N through the join table `BookingDiscount`, because a booking may combine several discounts and a discount may be redeemed across many bookings.
+
+----
+
+`extras_on (Amenity – Booking)` [[BR]]
+Represents add-on amenities purchased as part of a booking (e.g. Breakfast, Airport pickup). Modeled as M–N through `Amenity_Booking`, with extra attributes `price` and `quantity` on the relationship itself, since these are properties of the *purchase*, not of the amenity.
+
+----
+
+`writes (User – Review)` [[BR]]
+Represents that a user authors a review. Modeled as 1–N: a user can write many reviews, while each review has exactly one author. Implemented via `guest_id` in `Review`.
+
+----
+
+`review_of (Property – Review)` [[BR]]
+Represents which property a review is about. Modeled as 1–N: a property accumulates many reviews, while each review targets exactly one property. Implemented via `property_id` in `Review`.
+
+----
+
+`based_on (Booking – Review)` [[BR]]
+Represents that a review must be backed by an actual stay. Modeled as a 1–N relationship: each review references exactly one booking, while a booking can be associated with multiple reviews. Implemented via booking_id in Review. 
+
+----
+
+`favorites (Guest – Property)` [[BR]]
+Represents the wishlist relationship: which properties a guest has saved. Modeled as M–N through `FavoriteListing`, with `created_at` recording when the favorite was added.
+
+----
+
+`receives (User – Notification)` [[BR]]
+Represents that a user receives notifications. Modeled as 1–N: each notification belongs to exactly one user, while a user can receive many. Implemented via `user_id` in `Notification`.
+
+----
+
+`governs (CancellationPolicy – Property)` [[BR]]
+Represents that a cancellation policy governs the refund rules of a property. Modeled as M–N through the join table `CancellationPolicy_Property`, since a single policy can be reused across many properties and a property can fall under multiple policies (e.g. seasonal variations).
+
+= Business rules and constraints
+
+A handful of cross-entity constraints are enforced at the application level on top of the schema:
+
+- A user becomes a `Host` only after one of their `HostApplication` records has `status = APPROVED` and a `reviewed_by_admin_id`.
+- A `Booking` may only be created on a `Room` whose `Availability` covers the requested `[check_in_date, check_out_date)` and whose status is `AVAILABLE`.
+- `guests_count` on a `Booking` must not exceed `Room.capacity + Room.extra_capacity`.
+- A `Review` may be created only if the underlying `Booking` belongs to the same `guest_id` and has `booking_status = COMPLETED`.
+- A `Discount` can be applied to a `Booking` only when the booking date falls between `valid_from` and `valid_to` and `is_active = TRUE`.
+- Refunds for a cancelled `Booking` are computed from the `CancellationPolicy` linked to the booked property and the time difference between cancellation and `check_in_date`.