ahmed salah

ahmed salah

  • 1.6k
  • 530
  • 121.6k

Hotel HotelID have relation with package and in same time ha

Jul 7 2017 10:30 AM
Problem
 

Hotel HotelID have relation with package and in same time have relation with daydetails table duplicate

 

I need to design database for booking tours for tourism company

Tourist booking package have two type :

Reservation hotel : this is will by 4 days or 6 days .

Suppose he select hotel Hilton 4 days meaning 3 Nights include ( accommodation costs + flight costs + transfer costs )

Reservation Program: this will be 4 days or 6 days only

Suppose he select program alexa 4 days meaning 3 Nights ( accommodation costs + flight costs + transfer costs + excursions costs)

Accommodation cost include room price per days + services depend on periods

Meaning room price from 01/01/2017 to 01/04/2017 different from 01/05/2017 to 01/09/2017

Flight include internal flights and return back to his country

Transfer : include internal transfer from airport to hotel .

Price of transfers from date 01/01/2017 to 01/04/2017 different from 02/04/2017 to 02/12/2017

Excursions : visit musumes like that and this is represent cost of visit

Excursion price depend on periods .

Price of excursion from date 01/01/2017 to 01/04/2017 different from 02/04/2017 to 02/12/2017

And excursion only found on programs not hotel reservation

8 days for hotel or program start from flight date

Every flight have more than one package (reservation hotel or program)

Final Result as below :

  1. Package 1 4 days  
  2. day              flight                                   hotel  
  3. day1             amsterdam to aswan          Hilton  
  4. day2                                                      Hilton  
  5. day3                                                      Hilton  
  6. day4            aswan to amsterdam                  
  7. Package 1 4 days costs(Here actually my problem )  
  8. day                  flightcosts                 hotelcosts  
  9. 26/07/2017           500                               50  
  10. 27/07/2017                                             50  
  11. 28/07/2017                                            50  
  12. 29/07/2017          500