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 :
Package 1 4 days day flight hotel day1 amsterdam to aswan Hilton day2 Hilton day3 Hilton day4 aswan to amsterdam Package 1 4 days costs(Here actually my problem ) day flightcosts hotelcosts 26/07/2017 500 50 27/07/2017 50 28/07/2017 50 29/07/2017 500