Hotel HotelID have relation with package and in same time have relation with daydetai

[ahmedsa]

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 :

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

testlast.jpg

[skhanal]

Since your requirement has many combinations, you need to create tables for each entities like Guest, Hotel, Package, flight, excursion, etc. Then link them together in a transaction table.