More from the Backpacker Hostel in Panama

[andreainpanama]

After 2 months of experimenting and bothering you all with my questions, I am
starting my database project all over again. The small backpackers hostel in
Panama. I have decided to "clean my slate" and try to incorporate all my new
understanding in a completely new set up. I am trying to normalize and
simplify. I realize now that as a database USER, I had jumped ahead with
form design (I like the pretty colors and fonts!), which was almost forcing
me to organize my tables around my form setup. I was also designing my
tables and forms to take the place of reports. Now I am trying to focus on
correct table setup and then I will deal with all the rest. (Part of my
problem is that I am doing this all, using the Spanish language version of
Access, and Spanish is not my first language!)

This is my plan.

GuestTable
GuestID, unique, access assigned autonumber, primary key
LastName
FirstName
Nationality
More fields that only pertain to personal information of a guest

StayTable
StayID, unique, access assigned autonumber, primary key
GuestID, unique access assigned, foreign key
StayArrivalDate
StayDepartureDate
PriceFields (calculated fields like pricepernight X numberofnights and
touristtax etc.)
More fields that only apply to the actual stay. This is very confusing to
me and is posing me problems because of my conceptions. See below for a list
of my confusions.

RoomTable
RoomID, unique, primary key, it is the room name, eg, The Blue Room
RoomDescription
RoomPrice
More fields that only pertain to the actual room

NationalityTable
Nationality (Only One field,simply a list of every country in the
world)Primary Key

Questions/Concepts in setting up relationships (this is what gets me in
circles)
Does every guest have a stay and every stay has an assigned room?
Or does every guest have a room and every room has a stay?

Once I understand the answer to that basic question, then I think I will be
more confident of the next issues:

Each guest can have many stays say in the course of a year, thus the need
for unique stayIDs. Each room can have more than one guest in a given stay,
eg, my sister, mom and myself share The Blue Room, during our March 1st stay,
which is assigned stayid#312. So we each have individual unique GuestID's
that all connect to one StayID. Then the StayTable would need a RoomID
field. And then do I connect my three guestsIDs to each other to show that
we all had the same stay and room, or should I just make sure that my each of
us has the exact same stayID associated with our individual GuestID.

Is my confusion making any sense to anyone?

 

[John Vinson]

After 2 months of experimenting and bothering you all with my questions, I am
starting my database project all over again. The small backpackers hostel in
Panama. I have decided to "clean my slate" and try to incorporate all my new
understanding in a completely new set up. I am trying to normalize and
simplify. I realize now that as a database USER, I had jumped ahead with
form design (I like the pretty colors and fonts!), which was almost forcing
me to organize my tables around my form setup. I was also designing my
tables and forms to take the place of reports. Now I am trying to focus on
correct table setup and then I will deal with all the rest. (Part of my
problem is that I am doing this all, using the Spanish language version of
Access, and Spanish is not my first language!)

Buen suerte!

This is my plan.

GuestTable
GuestID, unique, access assigned autonumber, primary key
LastName
FirstName
Nationality
More fields that only pertain to personal information of a guest

StayTable
StayID, unique, access assigned autonumber, primary key
GuestID, unique access assigned, foreign key

This should almost surely NOT be unique - unless you want to either
prevent repeat visits, or require you to enter the same guest
information twice. GuestID should be a NONunique foreign key I'd
guess.

StayArrivalDate
StayDepartureDate
PriceFields (calculated fields like pricepernight X numberofnights and
touristtax etc.)

This *might* be one of the cases where you actually want to store
calculated data (since you're capturing the price as of the time of
the visit, and that price might change in the future).

More fields that only apply to the actual stay. This is very confusing to
me and is posing me problems because of my conceptions. See below for a list
of my confusions.

RoomTable
RoomID, unique, primary key, it is the room name, eg, The Blue Room
RoomDescription
RoomPrice
More fields that only pertain to the actual room

NationalityTable
Nationality (Only One field,simply a list of every country in the
world)Primary Key

Questions/Concepts in setting up relationships (this is what gets me in
circles)
Does every guest have a stay and every stay has an assigned room?

Every guest would have one *or more* stays, I'd presume (again, don't
exclude repeat business unnecessarily!) A Stay has an assigned room,
but the Guests table should not; a guest might request a room change,
or (again) come back on another trip because they liked the place so
well.

Or does every guest have a room and every room has a stay?

Every room would presumably have MANY stays, over time.

Once I understand the answer to that basic question, then I think I will be
more confident of the next issues:

Each guest can have many stays say in the course of a year, thus the need
for unique stayIDs. Each room can have more than one guest in a given stay,
eg, my sister, mom and myself share The Blue Room, during our March 1st stay,
which is assigned stayid#312. So we each have individual unique GuestID's
that all connect to one StayID. Then the StayTable would need a RoomID
field. And then do I connect my three guestsIDs to each other to show that
we all had the same stay and room, or should I just make sure that my each of
us has the exact same stayID associated with our individual GuestID.

Is my confusion making any sense to anyone?

I think you need one more table, since there is in fact a many to many
relationship between Guests and Stays: RoomOccupancy maybe, with
fields for StayID and GuestID.

John W. Vinson[MVP]

 

[andreainpanama]

Can you clarify what you mean by this comment? Are you referring to the
GuestID in my stay table?

"This should almost surely NOT be unique - unless you want to either prevent
repeat visits, or require you to enter the same guest information twice.
GuestID should be a NON unique foreign key I'd guess."

My thinking was that somehow my guest table would pick up the GuestID from
my Guest Table, so I wouldn't have to reenter the guestinfo.

 

[John Vinson]

Can you clarify what you mean by this comment? Are you referring to the
GuestID in my stay table?
Yes.

"This should almost surely NOT be unique - unless you want to either prevent
repeat visits, or require you to enter the same guest information twice.
GuestID should be a NON unique foreign key I'd guess."

My thinking was that somehow my guest table would pick up the GuestID from
my Guest Table, so I wouldn't have to reenter the guestinfo.

Typically you would use a Form based on the Guest table (or, perhaps,
if it works better for you on the Rooms table) with a Subform based on
the Stay table. You'ld insert the guestID using the Master/Child link
field in the first case, or with a Combo Box in the second.

My point was the *UNIQUE* character of the GuestID field. While that
field should be unique in the Guests table, it should be non-unique in
the Stays table (or the resolver table that I suggested later on).

John W. Vinson[MVP]