How to join between table Duration Details and costs as imag

Question

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