Thanks for the advise
"Paul Brewer" <paul_at_paul.brewers.org.uk> wrote in message
news:3dfe3de3_3_at_mk-nntp-1.news.uk.worldonline.com...
> 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 Thu Dec 19 2002 - 10:25:45 CST
