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