Stuck building Hotel database !

[coda7777]

Hello there,

as the title says i've been looping around myself trying to create the best version but i fail every time for building such database

let's get to data info

Countries >> Can have more than 1 Destination >
each destination can have many hotels >
Each hotel have multiple type rooms >

Each Hotel have seasons pricing > which means
each room has different price depend on the season

each room can receive a promotion / special offer / deduction based on condition
supplements can be added to any room , and any offer can be applied to any room depend on the contracts

stuck doing all this data in a way that i can apply specific offer starting for example 1st of sep and ending 5th of sep on a room in period of Dec 1st to DEC 10th for example , then this offer can be stopped or it can run out or i can add another

despite the fact it's a hell of calculations i also got stuck building such model !! anybody can help ?

 

[theDBguy]

Hi. Can you post your table structure for review? Thanks.

 

[GinaWhipp]

Hmm, maybe this will help...
https://www.access-diva.com/dm13.html

 

[NauticalGent]

Hmm, maybe this will help...
https://www.access-diva.com/dm13.html

Slightly off-topic: Gina, your website design is ‘purt near impressive! Your work?

 

[GinaWhipp]

Slightly off-topic: Gina, your website design is ‘purt near impressive! Your work?

Why thank you!

Hmm, not sure if you mean the design or the stuff found there so the answer to both is yes except code snippets where cited.

 

[coda7777]

Hmm, maybe this will help...

i agree, impressive work on the hotel booking database

and yes slightly off topic, same category but it's from a tour operator perspective, it's not about the booking , it's about the pricing and contracting
and i can't put any model so far to get the purpose as i said.... still stuck doing it

 

[GinaWhipp]

Yep, missed that prospective and that might be fun to create a data model for but no time in my schedule right now.

Have you checked here...
http://www.databaseanswers.org/data_models/

 

[Pat Hartman]

Gina, your stuff is great but I have yet to find anything even remotely useful on that database answers site.

@coda,
Is this a class assignment or something you have been hired to do. In either case, it would be very helpful to us if you post what you have so far. And also, we are here to help you but not to do your work for you so if you post what you have, we can tell you if you are on track or offer suggestions.

 

[GinaWhipp]

Pat, was thinking the Reservations section would be helpful in getting started.

 

[Pat Hartman]

I didn't even look at it. The list of samples always sounds so promising but I've never found even a single useful idea there.

 

[GinaWhipp]

There is some decent models there especially if you are looking for a *first step*. It's like a writing a novel, the first sentence is the hardest but after that you're good. I believe that's what those Data Models offer, the first sentence.

 

[Mark_]

@coda7777,

First step is to identify everything that a customer can be asking for when setting up a booking. Does this include more than one room? Are the rooms for different periods? Does the customer have an affiliation that gives them a discount? Can this be used with other discounts? Does the hotel offer packages (services/travel/what have you) that affects the pricing? Does the hotel offer a long term (weekly) rate of any type?

This can be a very extensive system. How long do you have to work on it? Also, what are the hard specifications you've already received?

 

[jdraw]

coda7777,
You might consider looking at some available tour software packages to get ideas. Many offer free trials or at least marketing videos. It seems to be a fairly large undertaking and will require some planning, design and testing. Time spent on requirements and analysis up front is critical. Building and vetting a data model to verify your design vs requirements will save you time in development/programming.
I found this list via Google.
Good luck with your project.

 

[The_Doc_Man]

Since you are in the design phase, you can save yourself a lot of hair removal (of the unpleasant "ripping out in chunks" kind) by immediately stepping away from the curb. You are trying to model your business and you have given us an overview, but only you will know the details so we can only advise. Here is my advice.

You need to build a roadmap, a specification, a design document, something tangible. You start by gathering requirements. What do you want out of this project, i.e. what will be the things this app will deliver to you? If there is an order of application for the various driving factors (and you surely WILL have a preferred order), define the priorities or ordering factors. Like, compute base cost, then apply seasonal discount, then apply booking agent contractual discount, then apply special-offer coupons, then ... you get the idea.

In this design process, something I sometimes suggest is that you get a dry erase board and some markers and some sticky notes and try to diagram the process of converting your various inputs into a finished result. Don't hesitate to take a picture of the board when you get to a point where you see some insights into this sticky morass of data.

Remember two basic rules:

Old Programmer's Rule #1: If you can't do it on paper, you can't do it in Access.

Access has NO KNOWLEDGE of the travel industry or hotel bookings or tour contracts. All it can do is make tables, queries, forms, reports, etc. What goes in them is up to you as the subject-matter expert. This road map will guide you through the computations you need by showing you when, where, and (to some degree) how they should be applied. So the acid test of your model is that once you have your design, try to apply the steps by hand to see if you get the desired result.

Old Programmer's Rule #2: Access won't tell you anything you didn't tell it first.

Access creates no data. It only chews up and spits out what you tell it. If you wanted to see XYZ as output, you need a source of XYZ as input - or you need X, Y, and Z as separate inputs and a formula to merge them. As part of the design verification process you need to identify every desired output and step backwards to the point where you define all the inputs required to get there. You aren't done until every output has been verified as having a proper source.

This sounds tedious as all heck. To paraphrase a line from Moneyball, designing a business model isn't hard, it's extremely hard. But it is worth it. Nicklaus Wirth, the father of the Pascal programming language, once said that 80% of all programming problems are cause by poor data design. (Again, paraphrasing.) So be prepared to take the time to get the design right, because without out it, you will hate yourself and suffer painful hair loss as earlier described in passing.

 

[coda7777]

@coda7777,

First step is to identify everything that a customer can be asking for when setting up a booking. Does this include more than one room? Are the rooms for different periods? Does the customer have an affiliation that gives them a discount? Can this be used with other discounts? Does the hotel offer packages (services/travel/what have you) that affects the pricing? Does the hotel offer a long term (weekly) rate of any type?

This can be a very extensive system. How long do you have to work on it? Also, what are the hard specifications you've already received?

yea in that case it have a lot of different criteria that's why it's hard to establish it

 

[Mark_]

yea in that case it have a lot of different criteria that's why it's hard to establish it

Are you designing this for a specific client (even your own company) that is telling you what it wants or is this more general purpose, such as for resale?

If the former, you can go to them (preferably to the people actually taking the reservations) and ask for specifics.

 

[coda7777]

Are you designing this for a specific client (even your own company) that is telling you what it wants or is this more general purpose, such as for resale?

If the former, you can go to them (preferably to the people actually taking the reservations) and ask for specifics.

it's for my own business, still starting and i've been searching software for long time
trust me nothing even close
1 system i know it in kind of stupid way is called atcore from

but u will die if u work with that app lol

 

[coda7777]

i deleted all foreign keys and deleted some tables even
but the idea is very basic now

1. Add Country
2. Add City
3. Add Hotels
4. Add Rooms per Hotel
5. Add Seasons per Hotel
6. Add Facts per Hotel
7. Rates are Calculated from Hotel > Seasons + Room Type = Price
8. Rates are Value for Adult / Children(4 parts) Percentage % from Adult / 3rd Adult Supp = percentage % of the Adult / and so on

Rates Should auto calculate because it can be 365 days long and hotel can have 12 room types :banghead:

 

[Pat Hartman]

Posting a picture is nice but doesn't really help us to help you. Sometimes we can easily fix a problem but we can't do anything with a "picture"

 

[Mark_]

Is your target user another Hotel, one you run, or one you book for?