Re: data definition

Gavin,

That model looks nearer.
But (hint) how do you know which hotel a room belongs to? Can there not be more than one room 101 - each in a different hotel?

Once the model is straightened out, you might like to think about the primary key on another of the tables. A room belongs to a hotel, and (I assume) we don't want a room to be booked more than once for a given date....

Regards,
Paul

"Gavin Sim" <schowe_at_singnet.com.sg> wrote in message news:ati4l9$s4$1_at_mawar.singnet.com.sg...
> Hi Tim,
> thanks for your comment. This is only part of my modelling being reviewed.
> Actually i am trying to practise on the data definition on oracle thus my
> data model isn't that important at this point of time. But i did realised
> there is mistake and made the necessary amendments.
>
> Given the sample below the info_no is the primary key of the hotel.
> hotel_no and room_no are the primary keys
>
> information(info_no, hotel_no, room_no, datetime)
> hotel(hotel_no, hotel_name, Hotel_district)
> room(room_no, room_describe)
>
>
> Given the information above, how can I write in data definition in a way
> that i can ensure (no hotel or room can have 2 different information at
the
> same time. This mean a user can only register in a hotel or register a
room
> at the same time and not register 2 rooms or 2 hotels at the same time.
>
> can I use constraint check( unique(hotel,room)...... on the information
> table?
> I know this is wrong too sign.
>
>
>
>
> .
> "Tim X" <timx_at_spamto.devnul.com> wrote in message
> news:874r9fs7nm.fsf_at_tiger.rapttech.com.au...
> > "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!
>
>
Received on Sun Dec 15 2002 - 10:06:20 CST