Changes between Initial Version and Version 1 of Phase2


Ignore:
Timestamp:
04/22/26 14:16:42 (10 days ago)
Author:
231195
Comment:

--

Legend:

Unmodified
Added
Removed
Modified

  • Phase2

    v1 v1  
     1CREATE TABLE Role (
     2  role_id   int8 NOT NULL,
     3  role_name varchar(50) NOT NULL UNIQUE,
     4  PRIMARY KEY (role_id));
     5
     6CREATE TABLE UserRole (
     7  user_id     int8 NOT NULL,
     8  role_id     int8 NOT NULL,
     9  assigned_at timestamp NOT NULL,
     10  PRIMARY KEY (user_id, role_id)
     11);
     12
     13CREATE TABLE Host (
     14  host_id          int8 NOT NULL,
     15  user_id          int8 NOT NULL UNIQUE,
     16  host_description text,
     17  host_rating      numeric(3, 2),
     18  is_verified      bool NOT NULL,
     19  joined_at        timestamp NOT NULL,
     20  PRIMARY KEY (host_id));
     21
     22CREATE TABLE Guest (
     23  guest_id       int8 NOT NULL,
     24  user_id        int8 NOT NULL UNIQUE,
     25  preferences    text,
     26  loyalty_points int4 DEFAULT 0 NOT NULL,
     27  created_at     timestamp NOT NULL,
     28  PRIMARY KEY (guest_id));
     29
     30CREATE TABLE Country (
     31  country_id   int8 NOT NULL,
     32  country_name varchar(100) NOT NULL UNIQUE,
     33  country_code varchar(3) NOT NULL UNIQUE,
     34  PRIMARY KEY (country_id));
     35
     36CREATE TABLE City (
     37  city_id     int8 NOT NULL,
     38  country_id  int8 NOT NULL,
     39  city_name   varchar(100) NOT NULL,
     40  postal_code varchar(20) NOT NULL UNIQUE,
     41  PRIMARY KEY (city_id));
     42
     43CREATE TABLE Address (
     44  address_id    int8 NOT NULL,
     45  country_id    int8 NOT NULL,
     46  city_id       int8 NOT NULL,
     47  street        varchar(150) NOT NULL,
     48  street_number varchar(20),
     49  latitude      numeric(10, 6),
     50  longitude     numeric(10, 6),
     51  PRIMARY KEY (address_id));
     52
     53CREATE TABLE PropertyType (
     54  property_type_id int8 NOT NULL,
     55  type_name        varchar(50) NOT NULL UNIQUE,
     56  PRIMARY KEY (property_type_id));
     57
     58CREATE TABLE Property (
     59  property_id      int8 NOT NULL,
     60  host_id          int8 NOT NULL,
     61  property_type_id int8 NOT NULL,
     62  address_id       int8 NOT NULL,
     63  title            varchar(150) NOT NULL,
     64  description      text,
     65  max_guests       int4 NOT NULL,
     66  bedroom_count    int4,
     67  bathroom_count   int4 NOT NULL,
     68  base_price       numeric(10, 2) NOT NULL,
     69  check_in_time    time,
     70  check_out_time   time,
     71  status           varchar(30) NOT NULL,
     72  created_at       timestamp NOT NULL,
     73  PRIMARY KEY (property_id)
     74  );
     75
     76CREATE TABLE Amenity (
     77  amenity_id   int8 NOT NULL,
     78  amenity_name varchar(100) NOT NULL UNIQUE,
     79  description  text,
     80  PRIMARY KEY (amenity_id));
     81
     82CREATE TABLE PropertyAmenity (
     83  property_id int8 NOT NULL,
     84  amenity_id  int8 NOT NULL,
     85  PRIMARY KEY (property_id,
     86  amenity_id));
     87
     88CREATE TABLE PropertyImage (
     89  image_id    int8 NOT NULL,
     90  property_id int8 NOT NULL,
     91  image_url   varchar(500) NOT NULL,
     92  caption     varchar(255),
     93  is_primary  bool NOT NULL,
     94  uploaded_at timestamp NOT NULL,
     95  PRIMARY KEY (image_id));
     96
     97CREATE TABLE Room (
     98  room_id         int8,
     99  property_id     int8,
     100  room_name       varchar(100),
     101  room_type       varchar(50),
     102  capacity        int4 NOT NULL,
     103  price_per_night numeric(10, 2) NOT NULL,
     104  status          varchar(30) NOT NULL,
     105  PRIMARY KEY (room_id));
     106
     107CREATE TABLE Availability (
     108  availability_id int8 NOT NULL,
     109  property_id     int8 NOT NULL,
     110  room_id         int8 NOT NULL,
     111  available_date  date NOT NULL,
     112  is_available    bool NOT NULL,
     113  special_price   numeric(10, 2),
     114  PRIMARY KEY (availability_id));
     115
     116CREATE TABLE Booking (
     117  booking_id     int8 NOT NULL,
     118  guest_id       int8 NOT NULL,
     119  property_id    int8 NOT NULL,
     120  room_id        int8 NOT NULL,
     121  check_in_date  date NOT NULL,
     122  check_out_date date NOT NULL,
     123  guest_count    int4 NOT NULL,
     124  total_price    numeric(10, 2) NOT NULL,
     125  booking_status varchar(30) NOT NULL,
     126  created_at     timestamp NOT NULL,
     127  PRIMARY KEY (booking_id),
     128  CONSTRAINT chk_booking_dates
     129    CHECK (check_out_date > check_in_date));
     130
     131CREATE TABLE BookingGuest (
     132  booking_guest_id int8 NOT NULL,
     133  booking_id       int8 NOT NULL,
     134  guest_id         int8 NOT NULL,
     135  full_name        varchar(100) NOT NULL,
     136  age              int4 NOT NULL,
     137  document_number  varchar(50),
     138  is_primary_guest bool NOT NULL,
     139  PRIMARY KEY (booking_guest_id));
     140
     141CREATE TABLE Payment (
     142  payment_id            int8 NOT NULL,
     143  booking_id            int8 NOT NULL,
     144  amount                numeric(10, 2) NOT NULL,
     145  payment_date          timestamp NOT NULL,
     146  payment_method_id     int8 NOT NULL,
     147  payment_status        varchar(30) NOT NULL,
     148  transaction_reference varchar(100),
     149  PRIMARY KEY (payment_id));
     150
     151CREATE TABLE PaymentMethod (
     152  payment_method_id int8 NOT NULL,
     153  method_name       varchar(50) NOT NULL UNIQUE,
     154  pay_online        bool NOT NULL,
     155  PRIMARY KEY (payment_method_id));
     156
     157CREATE TABLE Review (
     158  review_id   int8 NOT NULL,
     159  booking_id  int8 NOT NULL UNIQUE,
     160  guest_id    int8 NOT NULL,
     161  property_id int8 NOT NULL,
     162  rating      int4 NOT NULL,
     163  comment     text,
     164  review_date timestamp NOT NULL,
     165  PRIMARY KEY (review_id),
     166  CONSTRAINT chk_rating_range
     167    CHECK (rating >= 1 AND rating <= 5));
     168
     169CREATE TABLE Favorite (
     170  favorite_id int8 NOT NULL,
     171  guest_id    int8 NOT NULL,
     172  property_id int8 NOT NULL,
     173  created_at  timestamp NOT NULL,
     174  PRIMARY KEY (favorite_id),
     175  CONSTRAINT unique_const
     176    UNIQUE (guest_id, property_id));
     177
     178CREATE TABLE Message (
     179  message_id       int8 NOT NULL,
     180  sender_user_id   int8 NOT NULL,
     181  reciever_user_id int8 NOT NULL,
     182  booking_id       int8 NOT NULL,
     183  property_id      int8 NOT NULL,
     184  message_text     text NOT NULL,
     185  sent_at          timestamp NOT NULL,
     186  is_read          bool NOT NULL,
     187  PRIMARY KEY (message_id));
     188
     189CREATE TABLE Discount (
     190  discount_id    int8 NOT NULL,
     191  property_id    int8 NOT NULL,
     192  host_id        int8 NOT NULL,
     193  discount_type  varchar(30) NOT NULL,
     194  discount_value numeric(10, 2) NOT NULL,
     195  start_date     date NOT NULL,
     196  end_date       date NOT NULL,
     197  is_active      bool NOT NULL,
     198  PRIMARY KEY (discount_id),
     199  CONSTRAINT chk_discount_dates
     200    CHECK (end_date >= start_date));
     201
     202CREATE TABLE "User" (
     203  user_id       int8 NOT NULL,
     204  first_name    varchar(100) NOT NULL,
     205  last_name     varchar(100) NOT NULL,
     206  email         varchar(255) NOT NULL UNIQUE,
     207  password_hash text NOT NULL,
     208  phone         varchar(30),
     209  date_of_birth date,
     210  profile_image varchar(500),
     211  created_at    timestamp NOT NULL,
     212  status        varchar(30) NOT NULL,
     213  PRIMARY KEY (user_id),
     214  CONSTRAINT chk_user_email_format
     215    CHECK (email LIKE '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$')
     216    );
     217
     218 
     219ALTER TABLE UserRole ADD CONSTRAINT FKUserRole510371 FOREIGN KEY (user_id) REFERENCES "User" (user_id);
     220ALTER TABLE UserRole ADD CONSTRAINT FKUserRole537901 FOREIGN KEY (role_id) REFERENCES Role (role_id);
     221ALTER TABLE Host ADD CONSTRAINT FKHost364816 FOREIGN KEY (user_id) REFERENCES "User" (user_id);
     222ALTER TABLE Guest ADD CONSTRAINT FKGuest463773 FOREIGN KEY (user_id) REFERENCES "User" (user_id);
     223ALTER TABLE City ADD CONSTRAINT FKCity681686 FOREIGN KEY (country_id) REFERENCES Country (country_id);
     224ALTER TABLE Address ADD CONSTRAINT FKAddress542818 FOREIGN KEY (country_id) REFERENCES Country (country_id);
     225ALTER TABLE Address ADD CONSTRAINT FKAddress943654 FOREIGN KEY (city_id) REFERENCES City (city_id);
     226ALTER TABLE Property ADD CONSTRAINT FKProperty146200 FOREIGN KEY (host_id) REFERENCES Host (host_id);
     227ALTER TABLE Property ADD CONSTRAINT FKProperty26489 FOREIGN KEY (property_type_id) REFERENCES PropertyType (property_type_id);
     228ALTER TABLE Property ADD CONSTRAINT FKProperty394643 FOREIGN KEY (address_id) REFERENCES Address (address_id);
     229ALTER TABLE PropertyImage ADD CONSTRAINT FKPropertyIm748462 FOREIGN KEY (property_id) REFERENCES Property (property_id);
     230ALTER TABLE Room ADD CONSTRAINT FKRoom250935 FOREIGN KEY (property_id) REFERENCES Property (property_id);
     231ALTER TABLE Availability ADD CONSTRAINT FKAvailabili408544 FOREIGN KEY (property_id) REFERENCES Property (property_id);
     232ALTER TABLE Availability ADD CONSTRAINT FKAvailabili235433 FOREIGN KEY (room_id) REFERENCES Room (room_id);
     233ALTER TABLE Booking ADD CONSTRAINT FKBooking733456 FOREIGN KEY (guest_id) REFERENCES Guest (guest_id);
     234ALTER TABLE Booking ADD CONSTRAINT FKBooking932562 FOREIGN KEY (property_id) REFERENCES Property (property_id);
     235ALTER TABLE Booking ADD CONSTRAINT FKBooking711414 FOREIGN KEY (room_id) REFERENCES Room (room_id);
     236ALTER TABLE BookingGuest ADD CONSTRAINT FKBookingGue951829 FOREIGN KEY (booking_id) REFERENCES Booking (booking_id);
     237ALTER TABLE Payment ADD CONSTRAINT FKPayment809900 FOREIGN KEY (booking_id) REFERENCES Booking (booking_id);
     238ALTER TABLE Review ADD CONSTRAINT FKReview586694 FOREIGN KEY (guest_id) REFERENCES Guest (guest_id);
     239ALTER TABLE Review ADD CONSTRAINT FKReview785800 FOREIGN KEY (property_id) REFERENCES Property (property_id);
     240ALTER TABLE Review ADD CONSTRAINT FKReview705219 FOREIGN KEY (booking_id) REFERENCES Booking (booking_id);
     241ALTER TABLE Favorite ADD CONSTRAINT FKFavorite639091 FOREIGN KEY (guest_id) REFERENCES Guest (guest_id);
     242ALTER TABLE Favorite ADD CONSTRAINT FKFavorite838197 FOREIGN KEY (property_id) REFERENCES Property (property_id);
     243ALTER TABLE Message ADD CONSTRAINT FKMessage71564 FOREIGN KEY (sender_user_id) REFERENCES "User" (user_id);
     244ALTER TABLE Message ADD CONSTRAINT FKMessage798241 FOREIGN KEY (booking_id) REFERENCES Booking (booking_id);
     245ALTER TABLE Discount ADD CONSTRAINT FKDiscount526380 FOREIGN KEY (property_id) REFERENCES Property (property_id);
     246ALTER TABLE PropertyAmenity ADD CONSTRAINT FKPropertyAm351867 FOREIGN KEY (property_id) REFERENCES Property (property_id);
     247ALTER TABLE Payment ADD CONSTRAINT FKPayment187362 FOREIGN KEY (payment_method_id) REFERENCES PaymentMethod (payment_method_id);
     248ALTER TABLE BookingGuest ADD CONSTRAINT FKBookingGue340084 FOREIGN KEY (guest_id) REFERENCES Guest (guest_id);
     249ALTER TABLE PropertyAmenity ADD CONSTRAINT FKPropertyAm118382 FOREIGN KEY (amenity_id) REFERENCES Amenity (amenity_id);
     250ALTER TABLE Discount ADD CONSTRAINT FKDiscount498768 FOREIGN KEY (host_id) REFERENCES Host (host_id);
     251ALTER TABLE Message ADD CONSTRAINT FKMessage715611 FOREIGN KEY (reciever_user_id) REFERENCES "User" (user_id);
     252ALTER TABLE Message ADD CONSTRAINT FKMessage307221 FOREIGN KEY (property_id) REFERENCES Property (property_id);