Changes between Version 1 and Version 2 of Phase2


Ignore:
Timestamp:
06/04/26 10:58:12 (12 days ago)
Author:
231106
Comment:

--

Legend:

Unmodified
Added
Removed
Modified

  • Phase2

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