Hostel Database Setup

Further to my earlier reply

Also note that NationalityID might be better as country of residence
using the ISO 3166 standard country codes.

Sure, if you have the list handy and feel like entering them all.

http://www.iso.org/iso/en/prods-serv...sts/index.html

--
Slainte

Craig Alexander Morrison
Crawbridge Data (Scotland) Limited

"Amy Blankenship" wrote in message
...

When I woke up this morning and saw that there were over 20 postings to my
op, I thought, Oh no, what have I done, asking for professional help on this
site and now I have many developers offering me services and others
reprimanding them for advertising on this site!

Imagine my surprise and glee when I saw that all of these responses where
still actually related to my original post!

Thanks guys, but now I am more confused than ever. My original post said
what I want, but it didn't go into detail about what I currently have. I
will post back with a better description of my current set up, and hopefully
get some help in real world language. Are any of you available by phone?
It's really cheap to call from here.

Thanks so much,

Andrea

"Jamie Collins" wrote:


andreainpanama wrote:
I want a way that every single person has a
client ID number.

Clients
=======
ClientID
LastName
FirstName
NationalityID

How do you know whether today's Jean Dupont from France is the Jean
Dupont from France who stayed here in July last year or is the Jean
Dupont from France who stayed here in July last year e.g. do you
require them to quote their ClientID number when repeat booking?
Another approach could be to record their passport number, with perhaps
a different identiifer for locals.

Also note that NationalityID might be better as country of residence
using the ISO 3166 standard country codes.

Jamie.

--

I could talk by Microsoft Messenger. E.mail me at amy at magnoliamultimedia
dot com, and I'll send you details.

-Amy

"andreainpanama" wrote in message
...
When I woke up this morning and saw that there were over 20 postings to my
op, I thought, Oh no, what have I done, asking for professional help on
this
site and now I have many developers offering me services and others
reprimanding them for advertising on this site!

Imagine my surprise and glee when I saw that all of these responses where
still actually related to my original post!

Thanks guys, but now I am more confused than ever. My original post said
what I want, but it didn't go into detail about what I currently have. I
will post back with a better description of my current set up, and
hopefully
get some help in real world language. Are any of you available by phone?
It's really cheap to call from here.

Thanks so much,

Andrea

"Jamie Collins" wrote:


andreainpanama wrote:
I want a way that every single person has a
client ID number.

Clients
=======
ClientID
LastName
FirstName
NationalityID

How do you know whether today's Jean Dupont from France is the Jean
Dupont from France who stayed here in July last year or is the Jean
Dupont from France who stayed here in July last year e.g. do you
require them to quote their ClientID number when repeat booking?
Another approach could be to record their passport number, with perhaps
a different identiifer for locals.

Also note that NationalityID might be better as country of residence
using the ISO 3166 standard country codes.

Jamie.

--

Andrea,

Database Design by Email is dangerous.

I would suggest you consider a few approaches:

1. Learn a bit about Relational Database design. (The main thing to
concentrate on is the process of Relational Data Analysis (aka
Normalisation))

Come back to the newsgroup with specific questions about particular
problems with the database AND the application(s)

2. Consider employing a local developer.

3. Consider employing a developer who can at least make a personal visit to
collect the user requirements and collate the data sources (current forms,
reports, spreadsheets, register) and sit down and talk through your
requirements.

4 Consider employing a remote developer

In cases 2, 3 and 4 get references, check with their previous clients and
look at samples of the work they have done. There are many many cowboys
around who be little more than users themselves who think they know what
they are doing.

Whatever approach make sure you own the source code so that you can tweak
the design in future either in-house or with another developer.

5. You could try the newsgroups but they are really best suited to an answer
to a specific question

If you go with this approach make sure you evaluate all the answers and note
any disagreements between other posters, this will assume that you have
learnt a little bit more about Relational Database Design.

If you are fairly confident in the process of creating forms and reports you
may just get the developer to create the database and you can do the
applications.

6. Check to see if anyone in the same line as you has done something similar
and see if you can make a mutually acceptable arrangement.

BTW Concentrate on getting the database design correct (or as correct as you
can) before worrying about Forms, Reports etc. You cannot get out of a
database what you do not put in.

Let your watchwords be Data Integrity and Data Consistency and ensure that
the developer is as concerned as you about the accuracy of your data.
Duplicate data is a killer to any system, and more so if it contains
important financial information.

BTW Keep this a secret, designing a good relational database is actually
rather straightfoward, if you understand the business (or rather the data
and how the business uses the data) and how to normalise data.

Using Access to create the applications becomes so much easier once you have
a good relational database design . Access is still one of the best tools to
design applications that use relational databases.

--
Slainte

Craig Alexander Morrison
Crawbridge Data (Scotland) Limited


"andreainpanama" wrote in message
...
When I woke up this morning and saw that there were over 20 postings to my
op, I thought, Oh no, what have I done, asking for professional help on
this
site and now I have many developers offering me services and others
reprimanding them for advertising on this site!

Imagine my surprise and glee when I saw that all of these responses where
still actually related to my original post!

Thanks guys, but now I am more confused than ever. My original post said
what I want, but it didn't go into detail about what I currently have. I
will post back with a better description of my current set up, and
hopefully
get some help in real world language. Are any of you available by phone?
It's really cheap to call from here.

Thanks so much,

Andrea

"Jamie Collins" wrote:


andreainpanama wrote:
I want a way that every single person has a
client ID number.

Clients
=======
ClientID
LastName
FirstName
NationalityID

How do you know whether today's Jean Dupont from France is the Jean
Dupont from France who stayed here in July last year or is the Jean
Dupont from France who stayed here in July last year e.g. do you
require them to quote their ClientID number when repeat booking?
Another approach could be to record their passport number, with perhaps
a different identiifer for locals.

Also note that NationalityID might be better as country of residence
using the ISO 3166 standard country codes.

Jamie.

--

There is some professional software that already exists. I googled on
"hostel software" and these were on the first page. I chose them because
they both seem to have demo versions. Even if you have someone program a
database for you, it is good to have some standard to judge it by. Also, you
may find they have some functionality that would be nice, other than just for
tracking your clients.

http://www.hotel-software.com/

http://www.dustysoftware.co.nz/

Well, this postings seems to have caused quite a stir....36 responses and
counting!!!...but yet most of the stuff is going right over my head, and as a
few of you commented...I didn't want my posting to become the brunt of a
theoretical battle!

After continuing my research, I am now thinking that perhaps my design
problems might be resolved by making my Guest Info and Bookings Table, many
to many relationship. Eg, 1 room can have more than one guest at any given
time and
1 guest can have many different bookings in the course of any given time.

If the consensus is yes, I should have many to many, are there any
suggestions as to how I can manipulate my existing setup to fit a new one?
Please keep the answers easy, I am a little dislexic when it comes to all
this theory and new vocabulary.

If you prefer, I can start a new post with my new question.

Thanks so much!


"andreainpanama" wrote:

Thanks to all of you for all of your help.

I have a small backpackers hostel and I am currently working on a database
to help me keep track of all of my guests.

These are the two basic design issues that I am running into.

Issue 1. Sometimes, there are 2 or 3 guests staying to gether in a private
room (as opposed to individuals staying in dorm beds which are always
registered separately). Since we rent the private rooms by the room, groups
are considered to be one booking and one stay in one room, even though there
are three individuals. I want to keep track not only of the guest that is
the official guest in my register (the one who pays for the room), but the 2
people who are accompanying him. Why? Because I a) want to know how many
different individuals have walked through my doors b) I want to know the
nationalities of every individual and c) Many times one of the people who
have stayed as a friend of a guest in a private room, will come back on their
own a few days later, and then they might become the paying guest or an
individual in a dorm. Therefore, I want a way that every single person has a
client ID number.

I have managed to do this rather haphazardly...by having a table with the
following fields,

MAIN GUEST 1(TITLE)
clientid1
lastname1
firstname1
nationality1
etc1
FRIEND2 (TITLE)
clientid2
lastname2
firstname2
nationality2
etc2
FRIEND 3 (TITLE)
clientid3
lastname3
firstname3
nationality3
etc3

And I just go in and manually assign a number to each person. Tried using
subforms, could't get them to work. Didn't use autonumbers because I wasn't
sure how to deal with the following Issue 2 below.....

Issue 2. Repeat Bookings. I currently have a booking table which allows
each guest to have a booking with dates, prices, room choice, etc. How do I
deal with repeat bookings of the same person? How do I structure tables and
forms to deal with the infinite amount of booking data that might apply to
one guest since I have no idea if a person will come back 0 times, or 3 times
or 65 times, or if they will be in the same room the next night, etc. How do
I limit my table and form size? How can I pull up a client number say 55,
and then enter new booking records? Should I do this with individual booking
tables? And once again, are my problems solve with autonumbers?


Finally, let me explain why I want this data...

To present real and true financial and marketing data to perspective buyers
of my business.
To answer my guests questions, eg, "How many guests have you had?" "What
country are the majority of your guests from?" "How many Swiss visitors have
you had?" "Are most of your guests under 30?" etc.


I would really appreciate someone's opinions.


(PS, I am a relative newbie, and know nothing about programming language.)

AndreainPanama

you can see my webpage at www.purplehousehostel.com