Hostel Database Setup

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

Try this:

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

Rooms
=======
RoomID
RoomNo
NumberofBeds
etc.

RoomStay
========
StayID
RoomID
StayBegin
StayEnd

ClientRoomStay
=========
StayID
ClientID
IsMainClient

Nationalities
=========
NationalityID
NationalityName
etc.

Then you want to make a Rooms form. Insert a new subform into your Rooms
form that uses RoomStay as a data source. That will need controls for the
stay beginning and the stay end, but the RoomID will be set by the
LinkMaster/LinkChild relationship between Rooms and RoomStay. If you used a
wizard, it would have inserted a visible control for that field. Leave it
on the form, but set its visible property to false. Also hide the StayID
field, since users won't need to set it.

You'll also want to insert a subform control on the RoomStay subform. Use
ClientRoomStay as the data source for it. Its StayID will be set by the
LinkMaster/LinkChild relationship between it and RoomStay. Again, hide the
StayID field. Leave the IsMainClient check box as is.

Now things get a little tricky. If you inserted the subform with a wizard,
it probably gave you a control for ClientID. Select FormatChange To, and
change it to a combobox control instead. Open the properties sheet for the
combobox and go to the data tab. Click the ... on the row source line and
then show the Client table. Double-click ClientID, LastName, and FirstName
to move those fields to the query grid. Close the window, saving the
change.

Switch to the Format tab and set the column count to 3. Set the column
widths to 0"; 1"; 1".

Now click in the little box in the upper left of your ClientRoomStay
Subform. Change its default view to continuous form.

What you have will now allow you to select your clients from the combobox
and associate them with a particular stay in a room as well as specifying if
a client is the main client. You can manually enter clients in a different
form, or you can use the not in list event of the combobox to automatically
pop up a new client form if the name you enter in the combobox is not in the
database. I am not going through the steps for that here, as it has been
covered on many occasions.

HTH;

Amy
"andreainpanama" wrote in message
...
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

Thank you for such a detailed response but I must admit that I got lost. I
will try to get a little further along in my thinking process and post again.


The truth is that I think I am going to need some professional help with
this project. (someone to do it for me!) Any recommendations of how I can
get cheap development help on line?

"Amy Blankenship" wrote:

Try this:

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

Rooms
=======
RoomID
RoomNo
NumberofBeds
etc.

RoomStay
========
StayID
RoomID
StayBegin
StayEnd

ClientRoomStay
=========
StayID
ClientID
IsMainClient

Nationalities
=========
NationalityID
NationalityName
etc.

Then you want to make a Rooms form. Insert a new subform into your Rooms
form that uses RoomStay as a data source. That will need controls for the
stay beginning and the stay end, but the RoomID will be set by the
LinkMaster/LinkChild relationship between Rooms and RoomStay. If you used a
wizard, it would have inserted a visible control for that field. Leave it
on the form, but set its visible property to false. Also hide the StayID
field, since users won't need to set it.

You'll also want to insert a subform control on the RoomStay subform. Use
ClientRoomStay as the data source for it. Its StayID will be set by the
LinkMaster/LinkChild relationship between it and RoomStay. Again, hide the
StayID field. Leave the IsMainClient check box as is.

Now things get a little tricky. If you inserted the subform with a wizard,
it probably gave you a control for ClientID. Select FormatChange To, and
change it to a combobox control instead. Open the properties sheet for the
combobox and go to the data tab. Click the ... on the row source line and
then show the Client table. Double-click ClientID, LastName, and FirstName
to move those fields to the query grid. Close the window, saving the
change.

Switch to the Format tab and set the column count to 3. Set the column
widths to 0"; 1"; 1".

Now click in the little box in the upper left of your ClientRoomStay
Subform. Change its default view to continuous form.

What you have will now allow you to select your clients from the combobox
and associate them with a particular stay in a room as well as specifying if
a client is the main client. You can manually enter clients in a different
form, or you can use the not in list event of the combobox to automatically
pop up a new client form if the name you enter in the combobox is not in the
database. I am not going through the steps for that here, as it has been
covered on many occasions.

HTH;

Amy
"andreainpanama" wrote in message
...
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

I am not aware if there is something like an Access Jobs board. Maybe
someone else has more information on that.

"andreainpanama" wrote in message
...
Thank you for such a detailed response but I must admit that I got lost.
I
will try to get a little further along in my thinking process and post
again.


The truth is that I think I am going to need some professional help with
this project. (someone to do it for me!) Any recommendations of how I can
get cheap development help on line?

"Amy Blankenship" wrote:

Try this:

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

Rooms
=======
RoomID
RoomNo
NumberofBeds
etc.

RoomStay
========
StayID
RoomID
StayBegin
StayEnd

ClientRoomStay
=========
StayID
ClientID
IsMainClient

Nationalities
=========
NationalityID
NationalityName
etc.

Then you want to make a Rooms form. Insert a new subform into your Rooms
form that uses RoomStay as a data source. That will need controls for
the
stay beginning and the stay end, but the RoomID will be set by the
LinkMaster/LinkChild relationship between Rooms and RoomStay. If you
used a
wizard, it would have inserted a visible control for that field. Leave
it
on the form, but set its visible property to false. Also hide the StayID
field, since users won't need to set it.

You'll also want to insert a subform control on the RoomStay subform.
Use
ClientRoomStay as the data source for it. Its StayID will be set by the
LinkMaster/LinkChild relationship between it and RoomStay. Again, hide
the
StayID field. Leave the IsMainClient check box as is.

Now things get a little tricky. If you inserted the subform with a
wizard,
it probably gave you a control for ClientID. Select FormatChange To,
and
change it to a combobox control instead. Open the properties sheet for
the
combobox and go to the data tab. Click the ... on the row source line
and
then show the Client table. Double-click ClientID, LastName, and
FirstName
to move those fields to the query grid. Close the window, saving the
change.

Switch to the Format tab and set the column count to 3. Set the column
widths to 0"; 1"; 1".

Now click in the little box in the upper left of your ClientRoomStay
Subform. Change its default view to continuous form.

What you have will now allow you to select your clients from the combobox
and associate them with a particular stay in a room as well as specifying
if
a client is the main client. You can manually enter clients in a
different
form, or you can use the not in list event of the combobox to
automatically
pop up a new client form if the name you enter in the combobox is not in
the
database. I am not going through the steps for that here, as it has been
covered on many occasions.

HTH;

Amy
"andreainpanama" wrote in message
...
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

Rooms
=======
RoomID
RoomNo
NumberofBeds
etc.

Without commenting on the rest of your post, the above table suggestion
seems incomplete with no mention of indexes (although I am presuming the
invention RoomID represents a surrogate to stand in place of the Natural Key
for the purpose of Primary Key)

I trust you have advised to OP by private email that this table would
require two unique non nullable indexes to avoid duplicates.

Remember when you add these little ID fields you also need to ensure you
maintain data integrity.

Data integrity and consistency is of the utmost importance to the users. It
is a real problem in the real world; without a second unique non-null index
on the RoomNo field you could have 50 Room 101s. You may respond by saying
that you knew that, my suspicion is that the OP does not, but then if you
have already emailed them (or sent a correction to this newsgroup) about
your omission all is well.

RoomNo would appear to satisfy the requirements of a natural PK
(familiarity, minimality, stability, simplicity).

Please note very little would be gained by discussing Surrogate vs Natural
on this forum (there are justifications on some use of surrogates when the
natural PK fails some of the tests for suitability (familiarity, minimality,
stability, simplicity) however the automatic use of little ID fields
indicates an (let's be gentle (g)) unconsidered approach).

--
Slainte

Craig Alexander Morrison
Crawbridge Data (Scotland) Limited

"Amy Blankenship" wrote in message
...
Try this:

Craig Alexander Morrison wrote:
Without commenting on the rest of your post, the above table suggestion
seems incomplete with no mention of indexes

Personally, I would expect a post about table design to have no mention
of indexes. While I agree you point that a so-called 'surrogate'
without a corresponding natural key is worse than useless, I think the
fact that Jet implements certain constraints (UNIQUE, PRIMARY KEY) etc
via indexes is largely irrelevant.

Jamie.

--

.....replace index for key....




--
Slainte

Craig Alexander Morrison
Crawbridge Data (Scotland) Limited
"Jamie Collins" wrote in message
ups.com...
Craig Alexander Morrison wrote:
Without commenting on the rest of your post, the above table suggestion
seems incomplete with no mention of indexes

Personally, I would expect a post about table design to have no mention
of indexes. While I agree you point that a so-called 'surrogate'
without a corresponding natural key is worse than useless, I think the
fact that Jet implements certain constraints (UNIQUE, PRIMARY KEY) etc
via indexes is largely irrelevant.

Jamie.

--

I have been in the habit of calling an Index on the Primary Key the Primary
Index, an index on a Primary Key or indeed any Candidate Key as a Unique
Index. An index on any other field called a Secondary Index. The majority of
indexing is on key fields be they Primary, Foreign, Alternate or Candidate
keys.

I accept that I use Index and Key almost interchangably on this newsgroup,
but then we tend to be discussing physical implementations here rather than
Theory or indeed logical design. To some extent logical design has been
foreshortened especially if your target database is a relational one. The
first cut data design from a CLDD with the target being a Relational
Database Manager is done largely by making every key an index. The only
decisions being made were which index in the tables designated as
"Operational Masters" should be clustered and this was usually the index
with the least dependant occurences.

In the old days I used to design my databases on paper, black and
whiteboards, even a spell using Visio, 99.99% of all my "logical" designs
are now on DB2 or Microsoft Access 2 (yes 2, no 32 index limit)

It is not largely irrelevant, Jet should (and does) implement these keys via
indexes.

--
Slainte

Craig Alexander Morrison
Crawbridge Data (Scotland) Limited
"Jamie Collins" wrote in message
ups.com...
Craig Alexander Morrison wrote:
Without commenting on the rest of your post, the above table suggestion
seems incomplete with no mention of indexes

Personally, I would expect a post about table design to have no mention
of indexes. While I agree you point that a so-called 'surrogate'
without a corresponding natural key is worse than useless, I think the
fact that Jet implements certain constraints (UNIQUE, PRIMARY KEY) etc
via indexes is largely irrelevant.

Jamie.

--

It is not largely irrelevant, Jet should (and does) implement these keys
via
indexes.

I will retract this slightly (remove "should") :- Jet and most if not all
other vendors have chosen to implement primary keys via indices (some refer
to this as an accident).

Any discussion on any other physical implementation is largely theoretical.
This is not the place for that.

The fact that for the last 15 years of development I have slipped into Keys
are Indexes is a compromise one has to make to make a living delivering
solutions with the products available.

Hell I even use SQL and that is something I hoped would have been replaced
by something Relational.

--
Slainte

Craig Alexander Morrison
Crawbridge Data (Scotland) Limited

"Craig Alexander Morrison" wrote in
message ...
Rooms
=======
RoomID
RoomNo
NumberofBeds
etc.

Without commenting on the rest of your post, the above table suggestion
seems incomplete with no mention of indexes (although I am presuming the
invention RoomID represents a surrogate to stand in place of the Natural
Key
for the purpose of Primary Key)

Whether Andrea chooses to designate the RoomID as a PK is her choice.
However, I'd suggest she use it at a minimum to establish the relationships
with other tables, since it uses less space than the actual text room
number.

I trust you have advised to OP by private email that this table would
require two unique non nullable indexes to avoid duplicates.

Not really. It's her own hostel. I doubt she's overly concerned she might
accidentally enter the same room twice in the database :-).

Remember when you add these little ID fields you also need to ensure you
maintain data integrity.

Well, if you're a bank, yes. If you're a little hostel who just wants to
track clients more easily than a spread sheet, that's probably not your
primary concern.

Data integrity and consistency is of the utmost importance to the users.
It
is a real problem in the real world; without a second unique non-null
index
on the RoomNo field you could have 50 Room 101s.

In the real world, once the rooms have all been entered, most people stop.

You may respond by saying
that you knew that, my suspicion is that the OP does not, but then if you
have already emailed them (or sent a correction to this newsgroup) about
your omission all is well.

RoomNo would appear to satisfy the requirements of a natural PK
(familiarity, minimality, stability, simplicity).

Please note very little would be gained by discussing Surrogate vs Natural
on this forum (there are justifications on some use of surrogates when the
natural PK fails some of the tests for suitability (familiarity,
minimality,
stability, simplicity) however the automatic use of little ID fields
indicates an (let's be gentle (g)) unconsidered approach).

Or it indicates that in practice I've found that automatically adding an
autonumber to each table eventually comes in handy. For instance, what if
you repainted all the rooms and someone accidentally switched two of the
room numbers around? This is easy to fix with a surrogate key, harder with a
natural key. It's hardly unconsidered, considering g that there are a
large number of highly skilled developers that agree with me.
http://www.dbpd.com/vault/9805xtra.htm

I think it's really rude to hijack a user's simple request for help with a
theoretical debate that goes far beyond the question at hand and delves into
esoteric points that the user probably isn't to a point in her development
career where she is ready to care about them. If I'm wrong and she is as
concerned about the theory behind what she's doing as just getting the
database to work, she should read the article posted above for a more
complete discussion of the issue, rather than your one point harangue that
doesn't give much context for her to make an informed decision.

-Amy