Re: data definition

"Gavin Sim" <schowe_at_singnet.com.sg> writes:

> Hi
> Hope someone here is able to help me.
>
> There is this particular questions which I am not sure how to go about doing
> it.
>
> example
> hotel(hotel_no, room_no, reg_type, datetime)
> room(room_no, room_describe)
> registration(reg_type, days_stay, payment)
>
> In my data definition how do I make a constraint in such a way that no
> room_no or hotel_no can have 2 different reg_type at the same time?
> I tried using Unique but still wrong.
>
> constraint diff_hotel_time check (unique (hotel_no, room_no)
> where count(*) datetime from hotel>1));
>
> I know the above is wrong, i am unable to come out with a solution on how to
> match all the dates together so that they can be used for checking on the
> hotel_no and room_no.
>
> Any help is greatly appreciated.

Your data model seems a little strange going on what you have given above. What are the primary keys for each of your tables? How do they relate to each other - for example, you have a reference to hotel_no at the top, but in the room table/object, there is no reference to hotel, so how do you distinguish between rooms with the same number in different hotels to get the room description?

I gather that what you are trying to do is have a way to make sure no room is double booked. Assuming the datetime is when the booking starts, you will need to get the days_stay amount and add that to the datetime value to determine the occupation time.

I guess you could control this with a before insert/update trigger, but really, I think you need to go back to the modelling stage and redo your data model as I can see many problems with this one. For a start, even the trigger solution is likely to perform badly, as you have no easy way of checking existing reservations - you would have to compare all reservations which start before the end date of the reservation you want to enter - depending on the number of records in the reservation table, this could be a problem. If this is a genuine reservation system, you also need to consider issues of maximising room reservations - this means finding ways to avoid small difficult to book intervals etc (a bit similar to the disc fragmentation problem - except guests will get a bit miffed if you wake them in the middle of the night to move them to a new room!).

Maybe have a check-in date and a check-out date rather than number of days booked (you can easily calculate this when its needed). Your constraint would then simply be checking the check-in and check-out days don't overlap with any other reservation for that room. In most cases, you are better off not storing information in the database which you can calculate from other information and having the exact dates of check-in and check-out would probably be more useful than recording the number of days someone is staying.

Tim

-- 
Tim Cross
The e-mail address on this message is FALSE (obviously!). My real e-mail is
to a company in Australia called rapttech and my login is tcross - if you 
really need to send mail, you should be able to work it out!