|
|||||||||
|
|||||||||
|
|||||||||
| |
|||
 |
|
|
|
|
|||||||||
|
|||||||||
|
|||||||||
| |
|||
|
|
|
|
| |||||||||
 |
|
|
«
Previous Thread
|
Next Thread
»
|
Thread Tools | Search this Thread | Display Modes |
Dev Articles Community Forums Sponsor:
|
|
|
|
#1
June 17th, 2003, 06:39 AM
|
|||
|
|||
|
Unsure about Schema....
Hi...
Let me pose a senario.... I am designing a database for a hotel website.... I am unsure as to how to store bookings and search availability. do i use a table with every date from jan 1st to dec 31st and then have a checkbox available in each row for each room?? I'm so damn unsure about this one.... Many thanks for any help offered. Regards, Rob.
|
|
#2
June 17th, 2003, 08:53 AM
|
||||
|
||||
|
You would create a bookings table, which might look something like:
bookingid INT PK (AUTO INCREMENT) roomnum INT startdate SMALLDATETIME (or just DATE in Access) enddate SMALLDATETIME (or just DATE in Access) customerid INT FK
|
|
#3
August 5th, 2003, 03:06 PM
|
|||
|
|||
|
building on stumpy
you could opt to create a room table
ROOMID int (PK) TYPEID smallint {FK} and a Room type table TYPEID smallint (PK) characteristics varchar(100) -- like (non)smoking, king size bed, double/single room etc.. availability for a room non-reserved room for a specific type Code:
DECLARE @typeid smallint, @checkdate smalldatetime SET @typeid = 1 SET @checkdate = GETDATE() SELECT COUNT(*) FROM room WHERE TYPEID = @typeid AND NOT ROOMID IN ( SELECT ROOMID FROM bookings WHERE ROOMID IN (SELECT ROOMID FROM room where TYPEID = @typeid) AND ( DATEDIFF(d, startdate, @checkdate) <=0 AND DATEDIFF(d, enddate, @checkdate) >=0 ) ) Have not tested this select statement, but you get the picture. Not entirely sure about the DATEDIFFs....  Hope this helps
__________________
- Rogier Doekes
|
|
| Viewing: Dev Articles Community Forums > Databases > Microsoft SQL Server > Unsure about Schema.... |
| Thread Tools | Search this Thread |
| Display Modes | Rate This Thread |
|
|
 |
|
|
Del.icio.us
Digg
Google
Spurl
Blink
Furl
Simpy
Y! MyWeb
Linear Mode
