Re: data definition

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 - 08:52:34 CST