What relations between package and flights and transfer and excursion and hotel

ahmedsa · July 6, 2017, 12:50pm

I designed database to booking hotels and programs tour for tourism company
but i have problem in relation between
table package and tables transfer and table hotel and table flight and table excursion
meaning what relation between day detaile table and flight and excursion and transfer and hotel ?

USE [NileTravel]  
GO  
/****** Object:  Table [dbo].[DayDetails]  Script Date: 05/07/2017 9:08:12 PM ******/  
SET ANSI_NULLS ON  
GO  
SET QUOTED_IDENTIFIER ON  
GO  
CREATE TABLE [dbo].[DayDetails](  
    [DayDetailsID] [int] NOT NULL,  
    [DetailsDurationID] [int] NULL,  
    [FlightTypeID] [int] NULL,  
    [HotelID] [int] NULL,  
    [ExcursionID] [int] NULL,  
    [TransferTypeID] [int] NULL,  
 CONSTRAINT [PK_DayDetails] PRIMARY KEY CLUSTERED   
(  
    [DayDetailsID] ASC  
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]  
) ON [PRIMARY]  
  
GO  
/****** Object:  Table [dbo].[DaysDetailsCost]  Script Date: 05/07/2017 9:08:12 PM ******/  
SET ANSI_NULLS ON  
GO  
SET QUOTED_IDENTIFIER ON  
GO  
CREATE TABLE [dbo].[DaysDetailsCost](  
    [DayCostID] [int] NOT NULL,  
    [DateDuration] [datetime] NULL,  
    [DetailsDurationID] [int] NULL,  
    [FlightCost] [numeric](18, 0) NULL,  
    [HotelCost] [numeric](18, 0) NULL,  
    [ExcursionCost] [numeric](18, 0) NULL,  
    [TransferCost] [numeric](18, 0) NULL,  
 CONSTRAINT [PK_DurationDetailsCost] PRIMARY KEY CLUSTERED   
(  
    [DayCostID] ASC  
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]  
) ON [PRIMARY]  
  
GO  
/****** Object:  Table [dbo].[DurationDetails]  Script Date: 05/07/2017 9:08:12 PM ******/  
SET ANSI_NULLS ON  
GO  
SET QUOTED_IDENTIFIER ON  
GO  
CREATE TABLE [dbo].[DurationDetails](  
    [DetailsDurationID] [int] NOT NULL,  
    [PackageDurationsID] [int] NULL,  
    [Days] [nvarchar](50) NULL,  
 CONSTRAINT [PK_DurationDetails] PRIMARY KEY CLUSTERED   
(  
    [DetailsDurationID] ASC  
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]  
) ON [PRIMARY]  
  
GO  
/****** Object:  Table [dbo].[Excursion]  Script Date: 05/07/2017 9:08:12 PM ******/  
SET ANSI_NULLS ON  
GO  
SET QUOTED_IDENTIFIER ON  
GO  
CREATE TABLE [dbo].[Excursion](  
    [ExcursionID] [int] NOT NULL,  
    [ExcursionName] [nvarchar](50) NULL,  
 CONSTRAINT [PK_Excursion] PRIMARY KEY CLUSTERED   
(  
    [ExcursionID] ASC  
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]  
) ON [PRIMARY]  
  
GO  
/****** Object:  Table [dbo].[ExcursionPeriod]  Script Date: 05/07/2017 9:08:12 PM ******/  
SET ANSI_NULLS ON  
GO  
SET QUOTED_IDENTIFIER ON  
GO  
CREATE TABLE [dbo].[ExcursionPeriod](  
    [ExcursionPeriodID] [int] NOT NULL,  
    [FromDate] [datetime] NULL,  
    [ToDate] [datetime] NULL,  
    [ExcursionID] [int] NULL,  
    [ExcursionPrice] [decimal](18, 0) NULL,  
    [TotalExcursion] [decimal](18, 0) NULL,  
 CONSTRAINT [PK_ExcursionPeriod] PRIMARY KEY CLUSTERED   
(  
    [ExcursionPeriodID] ASC  
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]  
) ON [PRIMARY]  
  
GO  
/****** Object:  Table [dbo].[FlightData]  Script Date: 05/07/2017 9:08:12 PM ******/  
SET ANSI_NULLS ON  
GO  
SET QUOTED_IDENTIFIER ON  
GO  
CREATE TABLE [dbo].[FlightData](  
    [FlighID] [nvarchar](50) NOT NULL,  
    [FlightNo] [nvarchar](50) NOT NULL,  
    [FlightDate] [datetime] NULL,  
    [FlightTypeID] [int] NULL,  
    [Arrival] [nvarchar](50) NULL,  
    [Departure] [nvarchar](50) NULL,  
    [AdultPrice] [money] NULL,  
    [ChildPrice] [money] NULL,  
    [Stock] [int] NULL,  
    [TotalPrice] [decimal](18, 0) NULL,  
    [Active] [bit] NULL,  
 CONSTRAINT [PK_FlightData_1] PRIMARY KEY CLUSTERED   
(  
    [FlighID] ASC  
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]  
) ON [PRIMARY]  
  
GO  
/****** Object:  Table [dbo].[FlightRoute]  Script Date: 05/07/2017 9:08:12 PM ******/  
SET ANSI_NULLS ON  
GO  
SET QUOTED_IDENTIFIER ON  
GO  
CREATE TABLE [dbo].[FlightRoute](  
    [FlightTypeID] [int] NOT NULL,  
    [FlightFrom] [nvarchar](max) NULL,  
    [FlightTo] [nvarchar](max) NULL,  
    [FlightRouteWay] [nvarchar](max) NULL,  
 CONSTRAINT [PK_FlightType] PRIMARY KEY CLUSTERED   
(  
    [FlightTypeID] ASC  
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]  
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]  
  
GO  
/****** Object:  Table [dbo].[Hotel]  Script Date: 05/07/2017 9:08:12 PM ******/  
SET ANSI_NULLS ON  
GO  
SET QUOTED_IDENTIFIER ON  
GO  
CREATE TABLE [dbo].[Hotel](  
    [HotelID] [int] NOT NULL,  
    [HotelName] [nvarchar](50) NULL,  
    [Rating] [nvarchar](10) NULL,  
 CONSTRAINT [PK_Product] PRIMARY KEY CLUSTERED   
(  
    [HotelID] ASC  
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]  
) ON [PRIMARY]  
  
GO  
/****** Object:  Table [dbo].[HotelPrice]  Script Date: 05/07/2017 9:08:12 PM ******/  
SET ANSI_NULLS ON  
GO  
SET QUOTED_IDENTIFIER ON  
GO  
CREATE TABLE [dbo].[HotelPrice](  
    [HotelPriceID] [int] NOT NULL,  
    [HotelID] [int] NULL,  
    [FromDate] [datetime] NULL,  
    [ToDate] [datetime] NULL,  
    [HotelPrice] [decimal](18, 0) NULL,  
 CONSTRAINT [PK_ProductPrice] PRIMARY KEY CLUSTERED   
(  
    [HotelPriceID] ASC  
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]  
) ON [PRIMARY]  
  
GO  
/****** Object:  Table [dbo].[Package]  Script Date: 05/07/2017 9:08:12 PM ******/  
SET ANSI_NULLS ON  
GO  
SET QUOTED_IDENTIFIER ON  
GO  
CREATE TABLE [dbo].[Package](  
    [PackageID] [int] NOT NULL,  
    [PackageName] [nvarchar](100) NULL,  
    [Duration] [nvarchar](50) NULL,  
    [HotelID] [int] NULL,  
    [ProgramID] [int] NULL,  
 CONSTRAINT [PK_Package] PRIMARY KEY CLUSTERED   
(  
    [PackageID] ASC  
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]  
) ON [PRIMARY]  
  
GO  
/****** Object:  Table [dbo].[PackageDuration]  Script Date: 05/07/2017 9:08:12 PM ******/  
SET ANSI_NULLS ON  
GO  
SET QUOTED_IDENTIFIER ON  
GO  
CREATE TABLE [dbo].[PackageDuration](  
    [PackageDurationsID] [int] NOT NULL,  
    [PackageID] [int] NULL,  
    [PackageDuration] [int] NULL,  
    [NightCounts] [int] NULL,  
 CONSTRAINT [PK_PackageDuration] PRIMARY KEY CLUSTERED   
(  
    [PackageDurationsID] ASC  
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]  
) ON [PRIMARY]  
  
GO  
/****** Object:  Table [dbo].[Program]  Script Date: 05/07/2017 9:08:12 PM ******/  
SET ANSI_NULLS ON  
GO  
SET QUOTED_IDENTIFIER ON  
GO  
CREATE TABLE [dbo].[Program](  
    [ProgramID] [int] NOT NULL,  
    [ProgramName] [nvarchar](50) NULL,  
    [ProgramTypeID] [int] NULL,  
 CONSTRAINT [PK_Program] PRIMARY KEY CLUSTERED   
(  
    [ProgramID] ASC  
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]  
) ON [PRIMARY]  
  
GO  
/****** Object:  Table [dbo].[RoomStock]  Script Date: 05/07/2017 9:08:12 PM ******/  
SET ANSI_NULLS ON  
GO  
SET QUOTED_IDENTIFIER ON  
GO  
CREATE TABLE [dbo].[RoomStock](  
    [RoomStockID] [int] NOT NULL,  
    [RoomTypeID] [int] NULL,  
    [Stock] [int] NULL,  
    [RoomStockPrice] [numeric](18, 0) NULL,  
    [HotelPriceID] [int] NULL,  
 CONSTRAINT [PK_RoomStock] PRIMARY KEY CLUSTERED   
(  
    [RoomStockID] ASC  
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]  
) ON [PRIMARY]  
  
GO  
/****** Object:  Table [dbo].[RoomType]  Script Date: 05/07/2017 9:08:12 PM ******/  
SET ANSI_NULLS ON  
GO  
SET QUOTED_IDENTIFIER ON  
GO  
CREATE TABLE [dbo].[RoomType](  
    [RoomTypeID] [int] NOT NULL,  
    [RoomType] [nvarchar](50) NULL,  
 CONSTRAINT [PK_RoomType] PRIMARY KEY CLUSTERED   
(  
    [RoomTypeID] ASC  
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]  
) ON [PRIMARY]  
  
GO  
/****** Object:  Table [dbo].[TransferPeriod]  Script Date: 05/07/2017 9:08:12 PM ******/  
SET ANSI_NULLS ON  
GO  
SET QUOTED_IDENTIFIER ON  
GO  
CREATE TABLE [dbo].[TransferPeriod](  
    [TransferID] [int] NOT NULL,  
    [FromDate] [datetime] NULL,  
    [ToDate] [datetime] NULL,  
    [TransferTypeID] [int] NULL,  
    [Price] [numeric](18, 0) NULL,  
    [Total] [numeric](18, 0) NULL,  
 CONSTRAINT [PK_TransferData] PRIMARY KEY CLUSTERED   
(  
    [TransferID] ASC  
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]  
) ON [PRIMARY]  
  
GO  
/****** Object:  Table [dbo].[TransferType]  Script Date: 05/07/2017 9:08:12 PM ******/  
SET ANSI_NULLS ON  
GO  
SET QUOTED_IDENTIFIER ON  
GO  
CREATE TABLE [dbo].[TransferType](  
    [TransferTypeID] [int] NOT NULL,  
    [TransferType] [nvarchar](50) NULL,  
 CONSTRAINT [PK_TransferType] PRIMARY KEY CLUSTERED   
(  
    [TransferTypeID] ASC  
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]  
) ON [PRIMARY]  
  
GO  
/****** Object:  Table [dbo].[Type]  Script Date: 05/07/2017 9:08:12 PM ******/  
SET ANSI_NULLS ON  
GO  
SET QUOTED_IDENTIFIER ON  
GO  
CREATE TABLE [dbo].[Type](  
    [ProgramTypeID] [int] NOT NULL,  
    [ProgramType] [nvarchar](50) NULL,  
 CONSTRAINT [PK_Type] PRIMARY KEY CLUSTERED   
(  
    [ProgramTypeID] ASC  
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]  
) ON [PRIMARY]  
  
GO  
INSERT [dbo].[DayDetails] ([DayDetailsID], [DetailsDurationID], [FlightTypeID], [HotelID], [ExcursionID], [TransferTypeID]) VALUES (1, 1, 1, 1, 1, 1)  
INSERT [dbo].[DayDetails] ([DayDetailsID], [DetailsDurationID], [FlightTypeID], [HotelID], [ExcursionID], [TransferTypeID]) VALUES (2, 1, NULL, 1, NULL, NULL)  
INSERT [dbo].[DayDetails] ([DayDetailsID], [DetailsDurationID], [FlightTypeID], [HotelID], [ExcursionID], [TransferTypeID]) VALUES (3, 1, NULL, 1, NULL, 1)  
INSERT [dbo].[DayDetails] ([DayDetailsID], [DetailsDurationID], [FlightTypeID], [HotelID], [ExcursionID], [TransferTypeID]) VALUES (4, 1, 2, 1, NULL, NULL)  
INSERT [dbo].[DaysDetailsCost] ([DayCostID], [DateDuration], [DetailsDurationID], [FlightCost], [HotelCost], [ExcursionCost], [TransferCost]) VALUES (1, CAST(0x0000A7BC00000000 AS DateTime), 1, CAST(100 AS Numeric(18, 0)), CAST(300 AS Numeric(18, 0)), CAST(60 AS Numeric(18, 0)), CAST(60 AS Numeric(18, 0)))  
INSERT [dbo].[DaysDetailsCost] ([DayCostID], [DateDuration], [DetailsDurationID], [FlightCost], [HotelCost], [ExcursionCost], [TransferCost]) VALUES (2, CAST(0x0000A7BD00000000 AS DateTime), 1, NULL, CAST(300 AS Numeric(18, 0)), NULL, NULL)  
INSERT [dbo].[DaysDetailsCost] ([DayCostID], [DateDuration], [DetailsDurationID], [FlightCost], [HotelCost], [ExcursionCost], [TransferCost]) VALUES (3, CAST(0x0000A7BE00000000 AS DateTime), 1, NULL, CAST(300 AS Numeric(18, 0)), CAST(60 AS Numeric(18, 0)), NULL)  
INSERT [dbo].[DaysDetailsCost] ([DayCostID], [DateDuration], [DetailsDurationID], [FlightCost], [HotelCost], [ExcursionCost], [TransferCost]) VALUES (4, CAST(0x0000A7BF00000000 AS DateTime), 1, CAST(120 AS Numeric(18, 0)), CAST(300 AS Numeric(18, 0)), NULL, NULL)  
INSERT [dbo].[DurationDetails] ([DetailsDurationID], [PackageDurationsID], [Days]) VALUES (1, 1, N'day1')  
INSERT [dbo].[DurationDetails] ([DetailsDurationID], [PackageDurationsID], [Days]) VALUES (2, 1, N'day2')  
INSERT [dbo].[DurationDetails] ([DetailsDurationID], [PackageDurationsID], [Days]) VALUES (3, 1, N'day3')  
INSERT [dbo].[DurationDetails] ([DetailsDurationID], [PackageDurationsID], [Days]) VALUES (4, 1, N'day4')  
INSERT [dbo].[Excursion] ([ExcursionID], [ExcursionName]) VALUES (1, N'Visit luxor musiem')  
INSERT [dbo].[Excursion] ([ExcursionID], [ExcursionName]) VALUES (2, N'Visit pyramides')  
INSERT [dbo].[Excursion] ([ExcursionID], [ExcursionName]) VALUES (3, N'Visit abo elhol')  
INSERT [dbo].[ExcursionPeriod] ([ExcursionPeriodID], [FromDate], [ToDate], [ExcursionID], [ExcursionPrice], [TotalExcursion]) VALUES (1, CAST(0x0000A6EE00000000 AS DateTime), CAST(0x0000A72900000000 AS DateTime), 1, CAST(50 AS Decimal(18, 0)), CAST(50 AS Decimal(18, 0)))  
INSERT [dbo].[ExcursionPeriod] ([ExcursionPeriodID], [FromDate], [ToDate], [ExcursionID], [ExcursionPrice], [TotalExcursion]) VALUES (2, CAST(0x0000A72A00000000 AS DateTime), CAST(0x0000A7C300000000 AS DateTime), 1, CAST(60 AS Decimal(18, 0)), CAST(60 AS Decimal(18, 0)))  
INSERT [dbo].[FlightData] ([FlighID], [FlightNo], [FlightDate], [FlightTypeID], [Arrival], [Departure], [AdultPrice], [ChildPrice], [Stock], [TotalPrice], [Active]) VALUES (N'1', N'ms750', CAST(0x0000A7A300000000 AS DateTime), 1, N'06', N'10', 50.0000, 50.0000, 10, CAST(100 AS Decimal(18, 0)), 1)  
INSERT [dbo].[FlightData] ([FlighID], [FlightNo], [FlightDate], [FlightTypeID], [Arrival], [Departure], [AdultPrice], [ChildPrice], [Stock], [TotalPrice], [Active]) VALUES (N'2', N'ms800', CAST(0x0000A7C300000000 AS DateTime), 2, N'10', N'12', 60.0000, 60.0000, 20, CAST(120 AS Decimal(18, 0)), 1)  
INSERT [dbo].[FlightRoute] ([FlightTypeID], [FlightFrom], [FlightTo], [FlightRouteWay]) VALUES (1, N'Amsterdam', N'Luxor', N'Amsterdam to luxor')  
INSERT [dbo].[FlightRoute] ([FlightTypeID], [FlightFrom], [FlightTo], [FlightRouteWay]) VALUES (2, N'Luxor', N'Amsterdam', N'Luxor to amsterdam')  
INSERT [dbo].[FlightRoute] ([FlightTypeID], [FlightFrom], [FlightTo], [FlightRouteWay]) VALUES (3, N'Aswan', N'Luxor', N'Aswan to Luxor')  
INSERT [dbo].[FlightRoute] ([FlightTypeID], [FlightFrom], [FlightTo], [FlightRouteWay]) VALUES (4, N'luxor', N'aswan', N'aswan to luxor')  
INSERT [dbo].[Hotel] ([HotelID], [HotelName], [Rating]) VALUES (1, N'Hilton', N'***')  
INSERT [dbo].[Hotel] ([HotelID], [HotelName], [Rating]) VALUES (2, N'Basma', N'**')  
INSERT [dbo].[Hotel] ([HotelID], [HotelName], [Rating]) VALUES (3, N'Movenpick', N'***')  
INSERT [dbo].[HotelPrice] ([HotelPriceID], [HotelID], [FromDate], [ToDate], [HotelPrice]) VALUES (1, 1, CAST(0x0000A6EE00000000 AS DateTime), CAST(0x0000A76A00000000 AS DateTime), CAST(200 AS Decimal(18, 0)))  
INSERT [dbo].[HotelPrice] ([HotelPriceID], [HotelID], [FromDate], [ToDate], [HotelPrice]) VALUES (2, 1, CAST(0x0000A78900000000 AS DateTime), CAST(0x0000A7C900000000 AS DateTime), CAST(300 AS Decimal(18, 0)))  
INSERT [dbo].[Package] ([PackageID], [PackageName], [Duration], [HotelID], [ProgramID]) VALUES (1, N'HiltonPackage', N'4', 1, NULL)  
INSERT [dbo].[Package] ([PackageID], [PackageName], [Duration], [HotelID], [ProgramID]) VALUES (2, N'BasmaPackage', N'4,6', 2, NULL)  
INSERT [dbo].[Package] ([PackageID], [PackageName], [Duration], [HotelID], [ProgramID]) VALUES (3, N'alexaPackage', N'4', NULL, 1)  
INSERT [dbo].[Package] ([PackageID], [PackageName], [Duration], [HotelID], [ProgramID]) VALUES (4, N'sfinxPackage', N'4,6', NULL, 1)  
INSERT [dbo].[PackageDuration] ([PackageDurationsID], [PackageID], [PackageDuration], [NightCounts]) VALUES (1, 1, 4, 3)  
INSERT [dbo].[PackageDuration] ([PackageDurationsID], [PackageID], [PackageDuration], [NightCounts]) VALUES (2, 2, 4, 3)  
INSERT [dbo].[PackageDuration] ([PackageDurationsID], [PackageID], [PackageDuration], [NightCounts]) VALUES (3, 2, 6, 5)  
INSERT [dbo].[PackageDuration] ([PackageDurationsID], [PackageID], [PackageDuration], [NightCounts]) VALUES (4, 3, 4, 3)  
INSERT [dbo].[PackageDuration] ([PackageDurationsID], [PackageID], [PackageDuration], [NightCounts]) VALUES (5, 4, 4, 3)  
INSERT [dbo].[PackageDuration] ([PackageDurationsID], [PackageID], [PackageDuration], [NightCounts]) VALUES (6, 4, 6, 5)  
INSERT [dbo].[Program] ([ProgramID], [ProgramName], [ProgramTypeID]) VALUES (1, N'alexa', 1)  
INSERT [dbo].[Program] ([ProgramID], [ProgramName], [ProgramTypeID]) VALUES (2, N'sfinx', 1)  
INSERT [dbo].[Program] ([ProgramID], [ProgramName], [ProgramTypeID]) VALUES (3, N'amon', 2)  
INSERT [dbo].[RoomStock] ([RoomStockID], [RoomTypeID], [Stock], [RoomStockPrice], [HotelPriceID]) VALUES (1, 1, 10, CAST(50 AS Numeric(18, 0)), 1)  
INSERT [dbo].[RoomStock] ([RoomStockID], [RoomTypeID], [Stock], [RoomStockPrice], [HotelPriceID]) VALUES (2, 2, 10, CAST(60 AS Numeric(18, 0)), 2)  
INSERT [dbo].[RoomType] ([RoomTypeID], [RoomType]) VALUES (1, N'Standard')  
INSERT [dbo].[RoomType] ([RoomTypeID], [RoomType]) VALUES (2, N'Family')  
INSERT [dbo].[TransferPeriod] ([TransferID], [FromDate], [ToDate], [TransferTypeID], [Price], [Total]) VALUES (1, CAST(0x0000A6EE00000000 AS DateTime), CAST(0x0000A74B00000000 AS DateTime), 1, CAST(50 AS Numeric(18, 0)), CAST(50 AS Numeric(18, 0)))  
INSERT [dbo].[TransferPeriod] ([TransferID], [FromDate], [ToDate], [TransferTypeID], [Price], [Total]) VALUES (2, CAST(0x0000A76600000000 AS DateTime), CAST(0x0000A7FF00000000 AS DateTime), 1, CAST(60 AS Numeric(18, 0)), CAST(60 AS Numeric(18, 0)))  
INSERT [dbo].[TransferPeriod] ([TransferID], [FromDate], [ToDate], [TransferTypeID], [Price], [Total]) VALUES (3, CAST(0x0000A78500000000 AS DateTime), CAST(0x0000A7C200000000 AS DateTime), 2, CAST(50 AS Numeric(18, 0)), CAST(50 AS Numeric(18, 0)))  
INSERT [dbo].[TransferType] ([TransferTypeID], [TransferType]) VALUES (1, N'From Hotel to airbort')  
INSERT [dbo].[TransferType] ([TransferTypeID], [TransferType]) VALUES (2, N'From Hotel to Musuem')  
INSERT [dbo].[Type] ([ProgramTypeID], [ProgramType]) VALUES (1, N'NileCruize')  
INSERT [dbo].[Type] ([ProgramTypeID], [ProgramType]) VALUES (2, N'Sun')  
INSERT [dbo].[Type] ([ProgramTypeID], [ProgramType]) VALUES (3, N'Sea')  
ALTER TABLE [dbo].[DayDetails]  WITH CHECK ADD  CONSTRAINT [FK_DayDetails_DurationDetails] FOREIGN KEY([DetailsDurationID])  
REFERENCES [dbo].[DurationDetails] ([DetailsDurationID])  
GO  
ALTER TABLE [dbo].[DayDetails] CHECK CONSTRAINT [FK_DayDetails_DurationDetails]  
GO  
ALTER TABLE [dbo].[DayDetails]  WITH CHECK ADD  CONSTRAINT [FK_DayDetails_Excursion] FOREIGN KEY([ExcursionID])  
REFERENCES [dbo].[Excursion] ([ExcursionID])  
GO  
ALTER TABLE [dbo].[DayDetails] CHECK CONSTRAINT [FK_DayDetails_Excursion]  
GO  
ALTER TABLE [dbo].[DayDetails]  WITH CHECK ADD  CONSTRAINT [FK_DayDetails_FlightRoute] FOREIGN KEY([FlightTypeID])  
REFERENCES [dbo].[FlightRoute] ([FlightTypeID])  
GO  
ALTER TABLE [dbo].[DayDetails] CHECK CONSTRAINT [FK_DayDetails_FlightRoute]  
GO  
ALTER TABLE [dbo].[DayDetails]  WITH CHECK ADD  CONSTRAINT [FK_DayDetails_Hotel] FOREIGN KEY([HotelID])  
REFERENCES [dbo].[Hotel] ([HotelID])  
GO  
ALTER TABLE [dbo].[DayDetails] CHECK CONSTRAINT [FK_DayDetails_Hotel]  
GO  
ALTER TABLE [dbo].[DayDetails]  WITH CHECK ADD  CONSTRAINT [FK_DayDetails_TransferType] FOREIGN KEY([TransferTypeID])  
REFERENCES [dbo].[TransferType] ([TransferTypeID])  
GO  
ALTER TABLE [dbo].[DayDetails] CHECK CONSTRAINT [FK_DayDetails_TransferType]  
GO  
ALTER TABLE [dbo].[DaysDetailsCost]  WITH CHECK ADD  CONSTRAINT [FK_DurationDetailsCost_TourDuration] FOREIGN KEY([DetailsDurationID])  
REFERENCES [dbo].[DurationDetails] ([DetailsDurationID])  
GO  
ALTER TABLE [dbo].[DaysDetailsCost] CHECK CONSTRAINT [FK_DurationDetailsCost_TourDuration]  
GO  
ALTER TABLE [dbo].[DurationDetails]  WITH CHECK ADD  CONSTRAINT [FK_DurationDetails_ProgramDuration] FOREIGN KEY([PackageDurationsID])  
REFERENCES [dbo].[PackageDuration] ([PackageDurationsID])  
GO  
ALTER TABLE [dbo].[DurationDetails] CHECK CONSTRAINT [FK_DurationDetails_ProgramDuration]  
GO  
ALTER TABLE [dbo].[ExcursionPeriod]  WITH CHECK ADD  CONSTRAINT [FK_ExcursionPeriod_Excursion] FOREIGN KEY([ExcursionID])  
REFERENCES [dbo].[Excursion] ([ExcursionID])  
GO  
ALTER TABLE [dbo].[ExcursionPeriod] CHECK CONSTRAINT [FK_ExcursionPeriod_Excursion]  
GO  
ALTER TABLE [dbo].[FlightData]  WITH CHECK ADD  CONSTRAINT [FK_FlightData_FlightData] FOREIGN KEY([FlighID])  
REFERENCES [dbo].[FlightData] ([FlighID])  
GO  
ALTER TABLE [dbo].[FlightData] CHECK CONSTRAINT [FK_FlightData_FlightData]  
GO  
ALTER TABLE [dbo].[HotelPrice]  WITH CHECK ADD  CONSTRAINT [FK_ProductPrice_Product] FOREIGN KEY([HotelID])  
REFERENCES [dbo].[Hotel] ([HotelID])  
GO  
ALTER TABLE [dbo].[HotelPrice] CHECK CONSTRAINT [FK_ProductPrice_Product]  
GO  
ALTER TABLE [dbo].[Package]  WITH CHECK ADD  CONSTRAINT [FK_Program_Hotel] FOREIGN KEY([HotelID])  
REFERENCES [dbo].[Hotel] ([HotelID])  
GO  
ALTER TABLE [dbo].[Package] CHECK CONSTRAINT [FK_Program_Hotel]  
GO  
ALTER TABLE [dbo].[PackageDuration]  WITH CHECK ADD  CONSTRAINT [FK_PackageDuration_Package] FOREIGN KEY([PackageID])  
REFERENCES [dbo].[Package] ([PackageID])  
GO  
ALTER TABLE [dbo].[PackageDuration] CHECK CONSTRAINT [FK_PackageDuration_Package]  
GO  
ALTER TABLE [dbo].[Program]  WITH CHECK ADD  CONSTRAINT [FK_Program_Type] FOREIGN KEY([ProgramTypeID])  
REFERENCES [dbo].[Type] ([ProgramTypeID])  
GO  
ALTER TABLE [dbo].[Program] CHECK CONSTRAINT [FK_Program_Type]  
GO  
ALTER TABLE [dbo].[RoomStock]  WITH CHECK ADD  CONSTRAINT [FK_RoomStock_RoomStock] FOREIGN KEY([RoomTypeID])  
REFERENCES [dbo].[RoomType] ([RoomTypeID])  
GO  
ALTER TABLE [dbo].[RoomStock] CHECK CONSTRAINT [FK_RoomStock_RoomStock]  
GO  
ALTER TABLE [dbo].[TransferPeriod]  WITH CHECK ADD  CONSTRAINT [FK_TransferData_TransferType] FOREIGN KEY([TransferTypeID])  
REFERENCES [dbo].[TransferType] ([TransferTypeID])  
GO  
ALTER TABLE [dbo].[TransferPeriod] CHECK CONSTRAINT [FK_TransferData_TransferType]  
GO

DaveMaxwell · July 6, 2017, 1:19pm

Wow, that’s an…interesting layout.

It looks like you’ve made it a little more complex than you need to, but since they’re all mixed up, it’s a little hard to tell. Is there any chance you can rearrange that diagram so that the dependent entities are all together? Might make it easier to help us give you advice on how to tie them in…

ahmedsa · July 6, 2017, 1:40pm

I need help if possible can any one help me and i will give you any details if possible

ahmedsa · July 6, 2017, 1:43pm

my problem How to make relation between costs and details per every day

Problem

How to get cost per every day based on day detail table ?

Details

I designed database for booking hotels and program for tourists

but i face problem how to calculate cost of every program and hotels

Package 1 4 days

day              flight                    hotel
day1             amsterdam to aswan         Hilton
day2                                        Hilton
day3                                        Hilton
day4            aswan to amsterdam

Package 1 4 days costs(Here actually my problem )

day              flightcosts                 hotelcosts
26/07/2017           500                           50
27/07/2017                                         50
28/07/2017                                         50
29/07/2017          500

DaveMaxwell · July 6, 2017, 3:53pm

There are a number of flaws in your database design which are going to prevent you from implementing this:

There is no starting date for a package, so you can’t calculate the date range
Your hotel structure can’t support reservations either.
You have no way to tie a flight to a package (there will be multiple flights per day)

But, high level, you need to

create a list of dates from the start of the package to the end of the package.
LEFT join your flight data based on the date in the row
LEFT join your hotel costs based on the date in the row

Since it looks like you’re using MS SQL, this would get you started…

SELECT DateValue
  FROM Package P
  OUTER APPLY (SELECT DATEADD(DAY,number,P.PackageDate) DateValue
		         FROM master..spt_values
		        WHERE type = 'P'
		          AND DATEADD(DAY,number+1, P.PackageDate) <= DATEADD(DAY, P.Duration, P.PackageDate)) AS DateValueList
  LEFT OUTER JOIN FlightData F ON F.FlightDate = DateValueList.DateValue
  LEFT OUTER JOIN HotelReservation H ON H.ReservationDate = DateValueList.DateValue
 WHERE PackageID = 1

ahmedsa · July 6, 2017, 5:14pm

Thank you for reply
first thing what i change in my design i can do what you say
firstly what i will change in my design

DaveMaxwell · July 6, 2017, 5:32pm

Sorry, I’m not sure I understand. Are you asking for help, or are you saying you’re going to revisit your database design?

If you’re asking for help, please alter your diagram so that everything is together, The easy way is to just drag them around so that there are not crossed lines. It just makes it easier to follow what goes together. Once you do that, we can look at what you’ve got and we can see what needs to be done to get you what you need.

ahmedsa · July 6, 2017, 6:02pm

you say

You have no way to tie a flight to a package (there will be multiple flights per day)
what this meaning or what i change to my design to make the line above

DaveMaxwell · July 6, 2017, 6:21pm

Exactly what I said. You tie package to hotels, but there’s no FK on either package or FlightData which allows you to tie one to the other.

ahmedsa · July 6, 2017, 7:41pm

I will explain i need to make design database for booking for tourism company
tourists book two thing
hotels per 8 days or 15 days or 21 days it is fixed duration per any hotel
and include costs of accommodation and travel flight costs
and
programs tours and this also may be 8 day or 15 days or 21 days
but program tours may be to more than one hotel because tourist go from city to city this require to make reservation to more hotels
so that i really have some points wrong in my design
can you help me

ahmedsa · July 6, 2017, 7:43pm

so that according to explain above relation between program and hotels and package is correct or wrong

ahmedsa · July 7, 2017, 1:21pm

according to my design above i have problem
I have hotel id in day details table
and in same time i have hotel id in package
are this problem or not

DaveMaxwell · July 10, 2017, 12:59pm

Sorry I’ve taken so long to get back to you, but this took a bit of thought, and I was unable to even think about it over the weekend.

Your structure needs some serious re-work because you’ve got some of your relationships backwards, and prices/durations where they are not needed

NOTE: For consistency sake, the ID on the table is the primary key for the table. If you see a field name with ID at the end, that is the FOREIGN key to the table without the ID on (i.e. HotelID is a foreign key to the Hotel table)

NOTE 2: Now, this is just how I would do it, but it seems to cover most of what you need

Let’s start of with the hotel since that seems to be the easiest. We need the basic information for the hotel:

Hotel
	ID
	HotelName
	Address
	City
	TelephoneNumber
	CityID

A hotel has rooms, right? And there are different types of rooms (2 double beds, queen size bed, etc.

HotelRoom
	ID
	HotelID
	RoomTypeID
	RoomNumber
	RoomFloor

RoomType
	ID
	RoomDescription

Now rooms have different prices depending on the type of the room and the time of the year (summer is typically more expensive than the spring)

HotelRoomTypePricing
	HotelID
	RoomTypeID
	FromDate
	ToDate
	RoomPrice

Now you can obviously book a room once for a specific date, so I would put the booked rooms in a table - if the room is on the table, it’s booked, otherwise it’s available. Note, I chose to put the price on here for your pricing later, as it’s more efficient than joining on the dates

HotelRoomBooking
	RoomID
	BookingDate
	BookingPrice

Now, you’'ll notice, there’s a cityID on the hotel table - I did that to allow for quick querying for a specific city (so a hotel in a suburb can be counted as a hotel for a specific table (ex. NYC has so many mailing “cities” but are all part of NYC )

City
	ID
	CityName
	CityState

You need flight information. Since the travel agent is going to be dealing with airlines separately, I don’t think you need a ton of information for it other than the booking - the rest would be overkill

Flight
	ID
	FlightDate
	Airline
	FlightNo
	SeatNo
	Departure
	Arrival
	Price

FlightTransfer
	FlightID
	HotelID
	TransferCost

Now we get to packages, you need a basic description, name and duration

Package
	ID
	PackageName
	PackageDuration

Now you need to specify WHERE the package goes

PackageDay
	ID
	PackageID
	PackageDayNumber
	CityID

You’ll have excursions in each location, and those excursions will cost a different price based on the time of year

Excursion
	ID
	PackageDayID
	ExcursionName
	
ExcursionCost
	ID
	ExcursionID
	FromDate
	ToDate
	Price

Now you need the customer who actually books the packages. Now you, don’t need to tie to the package, rather just the package days. You’ll notice the packageDayID has a room, flight and excursion to tie to. When you figure out the pricing, you will just need to LEFT JOIN on those appropriate tables, and if there’s no value, there’s no cost.

NOTE 3: For transfer cost, you’d need to join the hotel in since the transfer cost is to the hotel, not the room…

Customer
	ID
	CustomerName
	CustomerAddress
	CustomerCity
	CustomerState
	CustomerTelephone
	
CustomerPackageDay
	CustomerID
	PackageDayID
	CustomerPackageDate
	FlightID
	RoomID
	ExcursionID

ahmedsa · July 11, 2017, 11:56am

thank you for reply
there is fantastic analysis database design
some points i need to do it
first thing suppose i book 2 room and actually I have free 10 room
then after book will be 8 room also when i book 2 room
also must this affect on flights because
available 10 then must stock for flight be 8
flight stock must be equal room stock
how to do that from design above

DaveMaxwell · July 11, 2017, 12:29pm

The structure I gave you is based on the rooms in the hotel, not on a count of rooms. The driver is the HotelRoomBooking table. You would need to join HotelRoom and HotelRoomBooking based on the date - if there is a record there, the room is booked. If not, the room is available. If you’re looking for all available rooms in a hotel for a date, you would do something like this.

SELECT RoomNumber
     , RoomFloor
     , RoomDescription
     , BookingPrice
  FROM HotelRoom R
  JOIN RoomType RT ON R.RoomTypeID = RT.ID
  LEFT OUTER JOIN HotelRoomBooking B ON B.RoomID = R.ID AND BookingDate = '21/10/2017'
 WHERE HotelID = 1
   AND BookingPrice IS NULL

Again, I did NOT set up flights other than informational since flights are much harder to incorporate (they don’t typically reserve blocks of seats like a hotel will unless the whole plane is chartered). If you wanted to do that, you would need to add a new table like flightseats with a list of ALL the seats in the flight, then a table for pricing of those seats.

I’m just not sure the effort is worth it…

ahmedsa · July 11, 2017, 2:56pm

another question if possible
I have fixed program or hotel duration and based on details i get cost
meaning my details of package not change every day as you notice below
your design based on data changed for package every day
my details of package is fixed
see the following image

DaveMaxwell · July 11, 2017, 3:14pm

That doesn’t match your requirement in this post, which is what I built the structure on: What relations between package and flights and transfer and excursion and hotel - #4 by ahmedsa

I’m not sure I understand the question, though…you wouldn’t charge the same for a person booking a queen sized bed as one that books a double bed. Same would go for someone who books an excursion each day, so the price can’t really be fixed. To get the total price, you add the prices for the selected choices together to get the final cost. What am I missing?

ahmedsa · July 11, 2017, 3:28pm

thank you for help and reply
of course prices not fixed
but details is fixed
the database design i doing is for this website
egypte.nl
i also have dahsoboard
if you can help me in make database deign for that

ahmedsa · July 11, 2017, 3:44pm

this image above for dashboard to this website booking
egypte.nl

DaveMaxwell · July 11, 2017, 6:36pm

That’s what PackageDay does. If you want it to be more granular, you can add HotelID to it

Or am I missing something more?