June 25, 2017 at 10:53 am
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
1 Alexia
2 Amon
3 Sfinx
every program have more duration may be 8 days or 15 days only
it have two periods only 8 days or 15 days .
so that i do duration program table have one to many with program .
DurationNo programID Duration
1 1 3 for Alexia
2 1 5 for Alexia
And same thing to program amon program and sfinx program 8 and 15 .
every program 3 or 5 have fixed details for every day as following :
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 5days it will be as following
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
June 26, 2017 at 8:11 am
Is this homework? Have you actually modeled this in a database system?
You will need a join between your tables. That will be a query such as this for table a and b.
select a.column1, sum(b.column2)
from a
inner join b
on a.Primarykey = b.ForeignKey
group by a.column1
Your design has various columns you've specified as IDs that can be primary or foreign keys.
In terms of what you've listed, you haven't really designed the day details or the costs. Those should link back to the program with FKs that let you perform a join. For the costs, rather than putting flight, hotel, transfer as columns, I'd make those rows, with a type. If I potentially had 2 transfers on a day, I wouldn't want a "Transfer2" column. I'd want to have these listed as rows that I can sum up.
June 26, 2017 at 8:15 am
you ask how to design, but you already posted the design of your database in a separate topic. What is your actual goal here if not to gain aid on how design a database with a specific goal in mind?
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
June 26, 2017 at 3:46 pm
Thank you for reply
what i do as followingCREATE TABLE program( ProgramID int primary key not null, ProgramName varchar(30) ) GO insert into program values(1,'Alexia'),(2,'Amon'),(3,'Sfinx') GOCREATE TABLE ProgramDuration(DurationNo int primary key not null,programID int not null,Duration varchar(30) null)insert into ProgramDurationvalues(1,1,'3 for Alexia'),(2,1,'5 for Alexia')GOCREATE TABLE DurationDetail( DurationNo int not null, [Days] varchar(20) not null, Hotel varchar(20) null, Flight varchar(50) null, transfers varchar(50) null ) insert into DurationDetail values (2,'Day1','Hilton','amsterdam to luxor','airport to hotel'), (2,'Day2','Hilton',null,'AbuSimple musuem'), (2,'Day3','Hilton',null,null), (2,'Day4','Hilton',null,null), (2,'Day5','Hilton','Luxor to amsterdam',null) GOCREATE TABLE DurationCost(DurationNo int not null,[Date] date not null,Hote_cost numeric(18,0) null,Flight_cost numeric(18,0) null,Transfer_cost numeric(18,0) null)insert into DurationCostvalues(2,'2017-06-25',25,500,20),(2,'2017-06-26',25,null,55),(2,'2017-06-27',25,null,null),(2,'2017-06-28',25,null,null),(2,'2017-06-29',25,500,null)GO
How table cost know that day1 have flight and transfer and hotel only
and last day have flights only and every day have hotel only
please help me in this point
June 26, 2017 at 4:58 pm
You aren't really defining what you are trying to accomplish. If you want to know the costs every day, it's just the list of costs. If you want to know some aggregation, what does that look like?
I think we have some language barrier here in discussion this, but you should show some results if you are trying to write the query. The query is separate from the design, however. The design is something you want to do that handles storing the data in a way that encourages accuracy and integrity.
When posting your code, please include line feeds. That one long long is really hard to understand.
July 1, 2017 at 5:56 pm
Actually i need to is to know is to calculate cost of flight and hotel and transfer and excursion per every day based on program duration
July 3, 2017 at 8:29 am
Show the calculations the, with a couple data sets. We can't see the requirements, and in code we can calculate this many ways. Without seeing what you are trying to calculate, we can't help with coding.
You should also show what code you've tried.
July 3, 2017 at 10:56 am
To help others see that long line of code in a more readable format:CREATE TABLE program (
ProgramID int primary key not null,
ProgramName varchar(30)
);
GO
insert into program values(1,'Alexia'),(2,'Amon'),(3,'Sfinx')
GO
CREATE TABLE ProgramDuration (
DurationNo int primary key not null,
programID int not null,
Duration varchar(30) null
)
insert into ProgramDuration values(1,1,'3 for Alexia'),(2,1,'5 for Alexia')
GO
CREATE TABLE DurationDetail (
DurationNo int not null,
[Days] varchar(20) not null,
Hotel varchar(20) null,
Flight varchar(50) null,
transfers varchar(50) null
)
insert into DurationDetail
values (2,'Day1','Hilton','amsterdam to luxor','airport to hotel'),
(2,'Day2','Hilton',null,'AbuSimple musuem'),
(2,'Day3','Hilton',null,null),
(2,'Day4','Hilton',null,null),
(2,'Day5','Hilton','Luxor to amsterdam',null)
GO
CREATE TABLE DurationCost (
DurationNo int not null,
[Date] date not null,
Hotel_cost numeric(18,0) null,
Flight_cost numeric(18,0) null,
Transfer_cost numeric(18,0) null
)
insert into DurationCost
values (2,'2017-06-25',25,500,20),
(2,'2017-06-26',25,null,55),
(2,'2017-06-27',25,null,null),
(2,'2017-06-28',25,null,null),
(2,'2017-06-29',25,500,null)
GO
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
July 5, 2017 at 12:40 pm
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply