Semi-Repeat Posting, Hostel Help II

[andreainpanama]

My confusion about my database setup just won't quit!!! This is once again
for my small backpackers hostel. (Only 22 beds and 5 rooms!) Part of this
posting is a repeat from a previous.

I keep changing my perspective... I can't get it straight in my head.

This is what I have so far:

Guest Info Table
Client ID Main Guest(PK)
Last Name of Main Guest
First Name of Main Guest
Nationality of Main Guest
Client ID of 1st friend
Last Name of 1st friend
First Name of 1st friend
Nationality of 1st friend
Client ID of 2nd friend
Last Name of 2nd friend
First Name of 2nd friend
Nationality of 2nd friend

(I am manually adding clientID of friends, therefore counting the number of
guests as I go, but this will get difficult when I start getting into the
thousands!!!)

Guest "Stay" Info Table (This is a subform of the Guest Info table)
Client ID Main Guest
Guest Stay ID (PK)
Arrival Date
Room Name
Price, Tax, etc


Room Info Table
Room Name (PK)
Room Description
Room Price


Nationality Table
List of Countries (PK)


I am thinking I need to do many to many relationships, but I am all twisted
around in my head as to how to set them up. I think what I need most is some
help in managing to adapt what I have already set up, to what I need. If you
could tell me what to specifically change or add, using my fields and tables,
that would be such a big help! I would also appreciate verification on my
relationship set up, and which need referential integrity. It is very cheap
for me to call from Panama, so if you don't feel like typing, I could
schedule a call.)

Every "guest" can have more than one "stay"
(therefore each "guest" can have more than one "room" say in the course of a
year. Is this correct thinking?????)1 to many
Every "room" can have more than one "stay"
(therefore more than one "guest"??????) 1 to many
Every "room" can have more than one "guest" 1 to many
Each individual "stay" can only have one "room" 1 to 1
Each "guest" can only have one "nationality" 1 to 1
Each "nationality" can have more than one "guest" 1 to many



What I have, works OK, but these are the issues that I am trying to resolve.

The following paragraphs are some repeat text from a previous posting of
mine. Quite a theoretical argument started around my post, and I could
barely decipher the help!

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.

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

 

[tina]

this is not a trivial project you've undertaken. to do it correctly, and
therefore efficiently, you need to really understand the principles of
relational data modeling. i didn't see your previous thread; but if nobody
recommended that you study relational data modeling already, then i strongly
recommend that you do so. for more information, see
http://home.att.net/~california.db/tips.html#aTip1.
also suggest you take a look at the hotel reservations model at
http://www.databaseanswers.org/data_models/hotels/index.htm.

note that using somebody else's data model can be a great timesaver - but it
*does not* replace the necessity of learning the principles of relational
data modeling for yourself. you are in a much better position to analyze
your business process and determine the best tables/relationships design,
than anyone in these newsgroups - once you've learned how to do it.

hth

 

[David Cox]

Try

GroupId
Clientid
Main guest (true/false)
Last name
first name
nationality

Every member of a group has the same group id

You may need an associated (i.e.previous) group field so that you can track
changes that occur when people join or leave a group. You can handle that
situation in different ways.

David F. Cox