ahmed salah

ahmed salah

  • 1.6k
  • 530
  • 121.6k

How to join between table Duration Details and costs as imag

Jun 25 2017 6:08 PM
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)