I need to make database design relations to calculate cost per tour for tourists 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 Amsterdam to Egypt
And from Cairo to Amsterdam in 26/06/2017 will return back
So that calculation will be as following :
Table tour
1 Tour 1
2 Tour 2
3 Tour 3
Table Tour Duration
Tour1 3 days
Tour2 6 days
Table Tour Duration Details
Day flight Hotel
Day 1 Amsterdam to Cairo Hilton
Day 2 Cairo to Aswan Hilton
Day 3 Cairo to Amsterdam
From 23/06/2017 to 26/06/2017
Table cost Per day
Day1 500 25
Day2 300 25
Day3 500
Table flight data
Tour 1 23/06/2017 26/06/2017 1350
Tour 2 01/07/2017 07/07/2017 1425 (5 * 25 + 1300)
25 represent cost accommodation per night in Hilton hotel
500 represent cost flight from cairn 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 ?