designing a holiday database - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

designing a holiday database

Author

Topic

php95saj
Starting Member

43 Posts

Posted - 2003-03-17 : 13:43:58

I am trying to design database for a wevsite that is going to offer online booking for a holiday resort. The information that I have that a standard room would cost a certain amount between a certain period of the year.
as an example:

Time Room Budget Economy Delux

Dec-Jan Single $50 $65 $75
Double $30 $35 $40

Jan-Mar Single $40 $50 $65
Double $25 $30 $35

Mar-May Single $45 $60 $70
Double $28 $30 $35

I would want to design a normalised database that will enable me to have all this information stored in normalised table so that I can pull information together. Thanks.

nr
SQLTeam MVY

12543 Posts

Posted - 2003-03-17 : 14:23:01

My first thought is that you shouldn't be doing this on your own.

RoomType (id, description)
RoomSize (id, Description)
RoomCost (RoomType_id, Roomsize_id, StartDate, EndDate, Cost)

You might have a period table or the size and type tables might be combined - depends on the business model.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

X002548
Not Just a Number

15586 Posts

Posted - 2003-03-17 : 14:27:00

I agree. The following code should head you off in the right direction, but I would seriously look of Relational Data Modeling.

http://www.sqlteam.com/item.asp?ItemID=122

Here's the code:

CREATE TABLE RoomSize (
RoomSize varchar(15) NOT NULL
, RoomSizeDesc varchar(255) NOT NULL
, AddDate datetime Not Null
, AddBy varchar(8) Not Null
, UpdDate datetime
, UpdBy varchar(8)
) ON [PRIMARY]
GO

CREATE TABLE RoomType (
RoomType varchar(15) NOT NULL
, RoomTypeDesc varchar(255) NOT NULL
, AddDate datetime Not Null
, AddBy varchar(8) Not Null
, UpdDate datetime
, UpdBy varchar(8)
) ON [PRIMARY]
GO

CREATE TABLE Rates (
StartDate datetime NOT NULL
, EndDate datetime NOT NULL
, RoomSize varchar(15) NOT NULL
, RoomType varchar(15) NOT NULL
, Rate decimal(4,2)
, AddDate datetime Not Null
, AddBy varchar(8) Not Null
, UpdDate datetime
, UpdBy varchar(8)
) ON [PRIMARY]
GO

ALTER TABLE RoomSize WITH NOCHECK ADD
PRIMARY KEY CLUSTERED
(
RoomSize
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO

ALTER TABLE RoomType WITH NOCHECK ADD
PRIMARY KEY CLUSTERED
(
RoomType
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO

ALTER TABLE Rates WITH NOCHECK ADD
PRIMARY KEY CLUSTERED
(
StartDate
, EndDate
, RoomSize
, RoomType
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO

ALTER TABLE RoomSize WITH NOCHECK ADD
CONSTRAINT RoomSize_CST1 DEFAULT (getdate()) FOR AddDate
GO

ALTER TABLE RoomType WITH NOCHECK ADD
CONSTRAINT RoomType_CST1 DEFAULT (getdate()) FOR AddDate
GO

ALTER TABLE Rates WITH NOCHECK ADD
CONSTRAINT Rate_CST1 DEFAULT (getdate()) FOR AddDate
GO

ALTER TABLE Rates ADD
FOREIGN KEY
(
RoomSize
) REFERENCES RoomSize (
RoomSize
)
GO
ALTER TABLE Rates ADD
FOREIGN KEY
(
RoomType
) REFERENCES RoomType (
RoomType
)
GO

INSERT INTO RoomSize (
RoomSize
, RoomSizeDesc
, AddBy)
SELECT 'Single','This is a single room. It accomodates...','INITLOAD'
UNION ALL
SELECT 'Double','This is a double room. It accomodates...','INITLOAD'
GO

INSERT INTO RoomType (
RoomType
, RoomTypeDesc
, AddBy)
SELECT 'Budget','This is a Budget room. Your lucky to get Air','INITLOAD'
UNION ALL
SELECT 'Economy','This is a Economy room. Come with Lights','INITLOAD'
UNION ALL
SELECT 'Deluxe','This is a Deluxe room. You get free bunny slippers','INITLOAD'
GO

INSERT INTO Rates (
StartDate
, EndDate
, RoomSize
, RoomType
, Rate
, AddBy)
SELECT '12/01/2003','12/01/2003 12:59:59','Single','Budget',50.00,'INITLOAD'
UNION ALL
SELECT '12/01/2003','12/01/2003 12:59:59','Single','Economy',65.00,'INITLOAD'
UNION ALL
SELECT '12/01/2003','12/01/2003 12:59:59','Single','Deluxe',65.00,'INITLOAD'
UNION ALL
SELECT '12/01/2003','12/01/2003 12:59:59','Double','Budget',30.00,'INITLOAD'
UNION ALL
SELECT '12/01/2003','12/01/2003 12:59:59','Double','Economy',35.00,'INITLOAD'
UNION ALL
SELECT '12/01/2003','12/01/2003 12:59:59','Double','Deluxe',40.00,'INITLOAD'
--ECT
GO

SELECT * FROM Rates
GO

DROP TABLE Rates
GO

DROP TABLE RoomSize
GO

DROP TABLE RoomType
GO

Brett

8-)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources