Is this possible in Access?? 1

[SamDemon]

I have been asked by a local company if the following problem can be solved with Access. I am under the impression that it will be possible but i thought i'd pick a few brains before i tried and failed miserably!!

Let me set the scenario...

The company in question deals with 40+ tour operators and 9 hotels (different room types in each). Each of the tour operators is limited to which hotels they are permitted to sell, i.e.

Tour Operator A is permitted to sell to Hotel 1, Hotel 3 and Hotel 6
Tour Operator B is permitted to sell to Hotel 4, Hotel 8 and Hotel 9… etc.

This is the straight forward part! What the company would like is the ability to load and send special offers on certain hotels & rooms to the tour operators that are permitted to sell them, i.e. An offer is created for Hotels 1 & 2 but only on rooms 1, 3 and 5 of hotel 1 and room 1 in hotel 2.

So in essence this offer would only be available for Tour Operator A.

So my question to you is would this be possible? I thought that it could be created from 4 tables, possibly tblTourOperatorDetails, tblHotelDetails, tblRoomDetails and tblSpecialOffer. Any advise?

Kind Regards

Sam


It's just common sense, shame sense isn't common!

 

[SkipVought]

Sam,

Sure, it's possible! You need to put some more flesh on the skeleton, thought, regarding your database structure.

Let's see some more of your design. Then we can pick it apart, etc. ;-)

Skip,

_{[red]Be advised:[/red] The dyslexic, agnostic, insomniac, lays awake all night wondering...
"Is there really a DOG?"}

 

[willir]

Start the juices flowing....

tblOperator
OperatorID - primary key
OperatorName
...etc (all things unique to the tour guide operator table)

tblHotel
HotelID - primary key
HotelName
...etc (all things unique to the Hotel table)

Are you sure a hotel can only have one tour guide operator??? Will this always be as such?? If so...

tblHotel
HotelID - primary key
HotelName
OperatorID - foreign key to tblOperator

This is a one-to-many relationship.

If you want to be more flexible, setup a many-to-many relationship.

tblHotel
HotelID - primary key
HotelName

tblHotelOperator
HotelID - foreign key to tblHotel
OperatorID - foreign key to tblOperator
...plus anything unique to the Operator x Hotel table such as special discounts.

Okay, now one approach for discounts...

tblDiscount
DiscountID - primary key
HotelID - foreign key to tblHotel
OperatorID - foreign key to Operator
StartDate
EndDate
DiscountRate
Conditions - text or memo

Now, you could get fancy and have individual rooms as a one-to-many...

tblRoom
RoomID - primary key
HotelID - foreign key to tblHotel
RoomNo
RoomType (single, double, ...)
Smoking - boolean (yes/no)
A/C - boolean (yes/no)
...plus other things unique to rooms

tblRate
RateID - primary key
RoomID - foreign key to tblRoom
StartDate
EndDate
RateType (single occupancy, double occupancy...)
Rate

Discussion:
Here, you would have different rate types. One record for single occupancy, another record for double occupancy, another record for seniors, another record for a weekly rate discount...

Also, note that the Start/End date allow you to have seasonal rates.

tblBooking
BookingID - primary key
RoomID - foreign key to tblRoom
RateID - foreign key to tblRate
OperatorID - foreign key tblOperator
CustomerID - foreign key to customer (if you will track customers)

...but now I go side-tracked to a booking / reservation system.

Hopefully this helped...
Richard

 

[Crowley16]

Just off the top of my head, and not examined things all that closely, basically just thinking out loud...

what about just a operator, hotel and rooms table.

then you just need to have a few tables in between JUST to specify which operator can book out what rooms in what hotels.

i.e. have a link table of what resources each table of operators have access to, and as long as you keep that table static, then you can use it to show the special offers and stuff...

I think I'm making sense...

--------------------
Procrastinate Now!

 

[SamDemon]

Thanks very much Willir! I have decided to create the tables as follows:-

tblHotelDetails
HotelID
HotelName
Address1
Address2
Address3
Address4
Postcode
Telephone
Fax
MainEmail
WebSite

tblTourOperatorDetails
ATOL
CompanyName
Address1
Address2
Address3
Address4
Postcode
Telephone
Fax
MainEmail
WebSite

tblHotelTourOperator
HotelID
ATOL

The only problem I am having is with linking the Special Offers, as each special offer can be used with many hotels. What I was thinking was the following tables:-

tblSpecialOffer
SpecialID - Primary Key
TypeOfOffer
StartDate
EndDate

tblSpecialHotel
SpecialID
HotelID

What I am concerned about is whether this will pull in the operators as well!?!?

Your advise is greatly appreciated.

Kind Regards

Sam

It's just common sense, shame sense isn't common!

 

[PHV]

whether this will pull in the operators as well
By joining tblSpecialHotel with tblHotelTourOperator on HotelID

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[SamDemon]

PHV this would only allow one hotel per Special would it not?

It's just common sense, shame sense isn't common!

 

[PHV]

No, the junction table tblSpecialHotel is typical for a n:m relation (many-to-many).

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[SamDemon]

Sorry about this PHV, but the relationship join between the two is showing as intermediate... would this be a problem later down the line?

It's just common sense, shame sense isn't common!

 

[willir]

Plug in data to test...
[tt]
tblHotelDetails
HotelID HotelName
1 Sweet Dreams
2 Pit Stop
3 Rest Eazy

tblTourOperatorDetails
ATOL CompanyName
1 Family Tours
2 No Surprizes Tours
[/tt]
(I am not sure what ATOL is so, I just used a number)
[tt]

tblHotelTourOperator
HotelID ATOL
1 1
2 1
2 2
3 2

tblSpecialOffer
SpecialID TypeOfOffer (Rate) StartDate EndDate
1 Group30 30% 01/01/2004 01/12/2004
2 Group20 20% 01/01/2004 01/12/2004
2 OffSeason1 50% 01/05/2004 03/01/2004
3 OffSeason2 50% 04/01/2004 06/07/2004
4 OffSeason3 50% 09/15/2004 11/07/2004
5 OffSeason4 50% 12/01/2004 12/21/2004
6 Promotion 20% 11/01/2004 11/30/2004
[/tt]
I added rate to show more info

[tt]
tblSpecialHotel
SpecialID HotelID
1 1
2 2
1 3
2 1
2 2
2 3
[/tt]

So...
Family Tours will use Sweet Dreams and Pit Stop
No Surprizes will use Pit Stop and Rest Eazy

Sweet Dreams and Rest Eazy offer a 30% group discount. This means that Family Tours and No Surprizes both can take advantage of this offer.

Pit Stop offers a 20% group discount. Both tour operators may use Pit Stop, so they both can take advantage of this offer.

All three hotels offer a 50% off season rate from Jan 5/04 to Mar 1/04.

Is there any weakness here? Maybe, depends on your needs.
- First, I know I suggested it, but having the start and end date on tblSpecialOffer may create more administrative maintenance. Each special rate has to encompass different dates, and then you have to enter / assign the hotels. This means that for each special, and each year, new specials will have to be created. It might make more sense to move the StartDate / EndDate to the tblSpecialHotel. This way, you can use the same SpecialOffer, such as OffSeason, over and over. The design as posted will work, but may involve more data entry.

- Operators

I am concerned about is whether this will pull in the operators as well

PHV stated how link the tables will provide the answer. Hopefully, using sample data demonstrates this.
BUT...
Will hotels offer different rates to the tour operators, or offer the specials for different periods?
If so, then you will track the ATOL code either in the tblSpecialOffer, perhaps a better choice would be to use tblSpecialHotel.

Richard