Database design in SQL Server 2005

Hi
I am facing a problem in designing a database for my project.Please help

I have hotel Information.The hotel allocates rooms for my company.This is done on weekly basis.

Now suppose in first week of a year the number of rooms allocated to the company is 3,2ndweek =5,3rdWeek=5 and so on...

So when i search based on a week i should have one result set
If i search based on MOnth i should have one result set.. in this way.

So what fields i need to take in a database table so that when i search based on week /month/quarter/year i get different resultsets.

Create a table called Room with all room details and a roomId, booked date etc. Create a Search stored procedure which take searchType as an input parameter. Search type can be Weekly or Monthly. SP would be like:

if @.searchType = "Weekly"

//get the start date, calculate end date of the week (which simply could be startDate +7 ) and select the rooms booked within this date

if @.searchType = "Monthly"

//get the start date and calcuate the end date of the month and select rooms booked in this date range

So basically you need just a start date, and depending on the comparison type, you calculate the end date and search within this date range

Hope this helps,

Vivek

|||

Hotels(Pid | Hotel_Name | Address)

Rooms_Type(like master, single, deluxe etc)

Pid | Description

Hotel_Rooms (Pid | Hotel_ID | Room_ID )

Room_Allocations (Pid | Date | BookedYN | Booking_No | Hotel_Rooms_ID | Qty_Allotted )

above is the list of tables i think should be helpful to you. Room_Allocations is key table here that can help you out. Note : say you've Hotel Crown Plaza, for which the quantity allotted to you for Master Room is 5, in that case you will insert 5 records in Room_Allocation table. now when you book a room you'll insert booking_reference against a room where BookedYN is 'N'.

hope it makes sense.

regards,

satish

|||

Thanks Vivek !!!

Your idea has helped me.

|||

Hey Satish

Thanks for sharing your ideas...

|||

no worries.

cheers,

satish.

|||

HI
Thanks for your help .I have a doubt.
Plz help

i have a tble structure like this:

Id St_dt end_dt hot_id no_of_room
1 Jan1 Jan30 1 5

Now i want to enter another data like
2 Jan15 Jan20 1 7

what i want is my actual table should have 3 rows of data like

Id St_dt end_dt hot_id no_of_room
1 Jan1 Jan15 1 5
2 Jan15 Jan20 1 7
3 Jan20 Jan30 1 5

How do i do this. Or is there any other way of avoiding data overlapping