Hotel Reservation (Normalization)
-
one thing that strikes me as odd is;
why do you have RoomStatusId on both the reservations table and the room table?
and the RoomStatusId on the reservation table is not linked to the RoomStatus table.
-
oh! I forgot to remove the Status on the Reservation Table.. Sorry!
I need to retrieve all available rooms? do i need to create a new table?
how and what are the columns for that?
thanks!
-
TheOxblood (9/22/2011)
oh! I forgot to remove the Status on the Reservation Table.. Sorry!I need to retrieve all available rooms? do i need to create a new table?
how and what are the columns for that?
thanks!
select from rooms join roomstatus where status = available.
Why do you have a reservation details table? It looks like a 1:1 relationship with reservation and the details are scattered across both tables. Seems to me a reservation would belong to a client, in fact the entire reservation details table is all part of a reservation.
Is this for a hotel reservation system? It looks like it. Maybe you should some more details to the room type table? Things like smoking, window view etc. I have to laugh that you can rent a room for a certain number of hours. 😛
Just curious why you storing the password twice? That is typically just a front end thing. You have them type the password twice to make sure they typed it correctly. Since they are the same there is no reason to store it twice.
All in all is doesn't look too bad with the exception of the reservation details table.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/ -
Yes i am planning to merge it..
Yes it is for hotel reservation! 🙂
How can I allow per each room their status?
For example, RoomNo 10 is RESERVED at 7:00am-12:00pm
but 12:00pm onwards, the room is VACANT
How can i make status like that only for a period of time?
-
TheOxblood (9/22/2011)
Yes i am planning to merge it..Yes it is for hotel reservation! 🙂
How can I allow per each room their status?
For example, RoomNo 10 is RESERVED at 7:00am-12:00pm
but 12:00pm onwards, the room is VACANT
How can i make status like that only for a period of time?
Well if there is no reservation during a certain block of time is MUST be vacant right? Do you really rent rooms by the hour? Most hotels have a standard checkin/checkout time. For example checkout is by 10am on your departing date and checkin time is after 3pm. This gives a nice block of time for house cleaning. Now conference rooms are a different animal entirely and should have their own data structure due to the drastic differences in renting guest rooms and conference rooms.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/ -
I wouldn't store the room status (if status is meant to indicate if the room is avialable or not) I would store the rooms and the reservations and make the status a calculated column in a view based upon the reservation for the room.
you say this is a per-hour-hotel (do they even exist?) but you are only storing the arrival date and depature date, it would be better to store these as datetimes and not worry about storing the number of hours that the guest is staying.
-
So, you are saying that the availability of the rooms will depend on the reservation time of the other?
SELECT * FROM Table WHERE ArrivalDate <> ... AND DepartureDate <> ...?
Yes it is existing, it is a motel type of hotel btw 🙂
I created a simple front end, so basically i eliminate the DepartureDate.. because if i have the NumberOfHours.. i can easily add it on the ArrivalTime 🙂
-
steveb. (9/22/2011)
I wouldn't store the room status (if status is meant to indicate if the room is avialable or not) I would store the rooms and the reservations and make the status a calculated column in a view based upon the reservation for the room.you say this is a per-hour-hotel (do they even exist?) but you are only storing the arrival date and depature date, it would be better to store these as datetimes and not worry about storing the number of hours that the guest is staying.
what do you mean by "and make the status a calculated column in a view based upon the reservation for the room."
-
TheOxblood (9/22/2011)
steveb. (9/22/2011)
I wouldn't store the room status (if status is meant to indicate if the room is avialable or not) I would store the rooms and the reservations and make the status a calculated column in a view based upon the reservation for the room.you say this is a per-hour-hotel (do they even exist?) but you are only storing the arrival date and depature date, it would be better to store these as datetimes and not worry about storing the number of hours that the guest is staying.
what do you mean by "and make the status a calculated column in a view based upon the reservation for the room."
I guess i am looking at this from a db normalisation perspective where one of the goals is to eliminate duplicated data.
so in this case you could write a view that finds all the reservations for the current date and from this you could ascertain if a room is rented at that particular point of time.
I would concentrate on getting a solid db structure (which i think you are close to) rather than focusing on how the front end is going to be structured.
-
TheOxblood (9/22/2011)
So, you are saying that the availability of the rooms will depend on the reservation time of the other?SELECT * FROM Table WHERE ArrivalDate <> ... AND DepartureDate <> ...?
Yes it is existing, it is a motel type of hotel btw 🙂
I created a simple front end, so basically i eliminate the DepartureDate.. because if i have the NumberOfHours.. i can easily add it on the ArrivalTime 🙂
Yes that would in fact be the case. A room is available if there is nobody renting it. 😉
Take a look at the following example based on your table structure.
create table #Reservations
(
ReservationID int identity not null,
RoomNum int,
ArrivalDate datetime,
DepartureDate datetime
)
create table #Rooms
(
RoomNum int identity not null,
HourlyRate decimal(5,2)
)
insert #Rooms (HourlyRate)
values
(23.99),
(13.99),
(3.99),
(2.99),
(1.99)
insert #Reservations (RoomNum, ArrivalDate, DepartureDate)
values
(1, GETDATE(), DATEADD(hh, 3, getdate())),
(1, GETDATE() + 2, DATEADD(hh, 5, getdate() + 2))
--now to see what rooms are available, we shouldn't be able to select Room 1 since there is a reservation already during this time slot
declare @DesiredReservationArrivalDate datetime = getdate()
declare @DesiredReservationDepartureDate datetime = getdate() + 3
select @DesiredReservationArrivalDate as DesiredArrival, @DesiredReservationDepartureDate as DesiredDeparture, *
from #Rooms r
left join #Reservations rs on rs.RoomNum = r.RoomNum
where @DesiredReservationArrivalDate > isnull(ArrivalDate, '')
and isnull(DepartureDate, @DesiredReservationDepartureDate + 1) > @DesiredReservationDepartureDate
drop table #Reservations
drop table #Rooms
This is using both the concept that Steve suggested about the calculate field but also the concept of vacancy when there is no reservation. This may not be 100% of what you need but should give you an idea of what we are both talking about. As Steve said, this looks pretty solid overall with just a few tweaks here and there.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/ -
I made a modifications on my database, would this be more good?
-
steveb. (9/22/2011)
TheOxblood (9/22/2011)
steveb. (9/22/2011)
I wouldn't store the room status (if status is meant to indicate if the room is avialable or not) I would store the rooms and the reservations and make the status a calculated column in a view based upon the reservation for the room.you say this is a per-hour-hotel (do they even exist?) but you are only storing the arrival date and depature date, it would be better to store these as datetimes and not worry about storing the number of hours that the guest is staying.
what do you mean by "and make the status a calculated column in a view based upon the reservation for the room."
I guess i am looking at this from a db normalisation perspective where one of the goals is to eliminate duplicated data.
so in this case you could write a view that finds all the reservations for the current date and from this you could ascertain if a room is rented at that particular point of time.
I would concentrate on getting a solid db structure (which i think you are close to) rather than focusing on how the front end is going to be structured.
Uploaded with ImageShack.us
-
What is the point of the RoomAvailable table? Are you using that a holder for all available rooms? That seems like not a good idea to me. Seems like all this has done is replace a bit field in the Room table (IsAvailable) but way more complicated. Get rid of that table entirely imho. The reservation is for a room so the RoomNo stays with the reservation.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/ -
Sean Lange (9/22/2011)
TheOxblood (9/22/2011)
So, you are saying that the availability of the rooms will depend on the reservation time of the other?SELECT * FROM Table WHERE ArrivalDate <> ... AND DepartureDate <> ...?
Yes it is existing, it is a motel type of hotel btw 🙂
I created a simple front end, so basically i eliminate the DepartureDate.. because if i have the NumberOfHours.. i can easily add it on the ArrivalTime 🙂
Yes that would in fact be the case. A room is available if there is nobody renting it. 😉
Take a look at the following example based on your table structure.
create table #Reservations
(
ReservationID int identity not null,
RoomNum int,
ArrivalDate datetime,
DepartureDate datetime
)
create table #Rooms
(
RoomNum int identity not null,
HourlyRate decimal(5,2)
)
insert #Rooms (HourlyRate)
values
(23.99),
(13.99),
(3.99),
(2.99),
(1.99)
insert #Reservations (RoomNum, ArrivalDate, DepartureDate)
values
(1, GETDATE(), DATEADD(hh, 3, getdate())),
(1, GETDATE() + 2, DATEADD(hh, 5, getdate() + 2))
--now to see what rooms are available, we shouldn't be able to select Room 1 since there is a reservation already during this time slot
declare @DesiredReservationArrivalDate datetime = getdate()
declare @DesiredReservationDepartureDate datetime = getdate() + 3
select @DesiredReservationArrivalDate as DesiredArrival, @DesiredReservationDepartureDate as DesiredDeparture, *
from #Rooms r
left join #Reservations rs on rs.RoomNum = r.RoomNum
where @DesiredReservationArrivalDate > isnull(ArrivalDate, '')
and isnull(DepartureDate, @DesiredReservationDepartureDate + 1) > @DesiredReservationDepartureDate
drop table #Reservations
drop table #Rooms
This is using both the concept that Steve suggested about the calculate field but also the concept of vacancy when there is no reservation. This may not be 100% of what you need but should give you an idea of what we are both talking about. As Steve said, this looks pretty solid overall with just a few tweaks here and there.
THANK YOU VERY MUCH for this!
Yay! When I was in school on the afternoon, I think about the LEFT JOIN command! For me to select all rooms that was LEFT without any reservations! Am I correct? 🙂
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply