3
Answers

How to join between table Duration Details and costs as imag

How to design database for tourism company to calculate cost of flight and hotel per every program tour based on date ?
 
what i do is
 
Table - program
+-----------+-------------+
| ProgramID | ProgramName |
+-----------+-------------+
| 1 | Alexia |
| 2 | Amon |
| 3 | Sfinx |
+-----------+-------------+
 
every program have more duration may be 3 days or 5 days only
 
it have two periods only 3 days or 5 days .
 
so that i do duration program table have one to many with program .
 
Table - ProgramDuration
 
+------------+-----------+---------------+
| DurationNo | programID | Duration |
+------------+-----------+---------------+
| 1 | 1 | 8 for Alexia |
| 2 | 1 | 15 for Alexia |
+------------+-----------+---------------+
 
And same thing to program amon program and sfinx program 3 and 5 .
 
every program 3 or 5 have fixed details for every day as following :
 
Table Duration Details
 
+------+--------+--------------------+-------------------+
| Days | Hotel | Flight | transfers |
+------+--------+--------------------+-------------------+
| Day1 | Hilton | amsterdam to luxor | airport to hotel |
| Day2 | Hilton | | AbuSimple musuem |
| Day3 | Hilton | | |
| Day4 | Hilton | | |
| Day5 | Hilton | Luxor to amsterdam | |
+------+--------+--------------------+-------------------+
 
every program determine starting by flight date so that
 
if flight date is 25/06/2017 for program alexia 5 days it will be as following
 
+------------+-------+--------+----------+
| Date | Hotel | Flight | Transfer |
+------------+-------+--------+----------+
| 25/06/2017 | 25 | 500 | 20 |
| 26/06/2017 | 25 | | 55 |
| 27/06/2017 | 25 | | |
| 28/06/2017 | 25 | | |
| 29/06/2017 | 25 | 500 | |
+------------+-------+--------+----------+
 
And this is actually what i need how to make relations ship to join costs with program .
 
for flight and hotel costs as above ?
 
for 5 days cost will be 1200
 
25 is cost per day for hotel Hilton
 
500 is cost for flight
 
20 and 55 is cost per transfers
 
image display what i need
 
join between cost and duration program

Answers (3)