I need to make database design relations to calculate cost per tour for touristes in egypt
Flight cost + hotel cost per every day for person based on flight date
Suppose I have flight on date 23/06/2017 from amesterdam to egypt
And from cairo to amesterdam in 26/06/2017 will return back
So that calculation will be as following :
Table tour
1 Tour1
2 Tour2
3 Tour3
Table Tour Duration
Tour1 3 days
Tour2 6 days
Table Tour Duration Details
Day flight Hotel
Day1 amesterdam to cairo Hilton
Day2 Cairo to aswan Hilton
Day3 Cairo to amsterdam
From 23/06/2017 to 26/06/2017
Table cost Per day
Day1 500 25
Day2 300 25
Day3 500
Table fligh data
Tour1 23/06/2017 26/06/2017 1350
Tour2 01/07/2017 07/07/2017 1425 (5 * 25 + 1300)
25 represent cost accomodation per night in hilton hotel
500 represent cost flight from cairo amsterdam ,amsterdam to cairo
300 represent cost per flight from cairo to aswan
How to design table relations to calculate cost per every day(cost hotel + cost flight) depending on flight date ?