More from the Backpacker Hostel in Panama andreainpanama 01.08.2006 21:35:02 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?

Re: More from the Backpacker Hostel in Panama John Vinson <jvinson[ at ]STOP_SPAM.WysardOfInfo.com> 01.08.2006 22:34:14 On Tue, 1 Aug 2006 14:35:02 -0700, andreainpanama <andreyajunkmail[ at ]yahoo.com> wrote: [Quoted Text] >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]

Re: More from the Backpacker Hostel in Panama andreainpanama 02.08.2006 01:07:01 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" wrote: [Quoted Text] > On Tue, 1 Aug 2006 14:35:02 -0700, andreainpanama > <andreyajunkmail[ at ]yahoo.com> wrote: > > >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 > > > > >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] >

Re: More from the Backpacker Hostel in Panama John Vinson <jvinson[ at ]STOP_SPAM.WysardOfInfo.com> 02.08.2006 02:39:11 On Tue, 1 Aug 2006 18:07:01 -0700, andreainpanama <andreyajunkmail[ at ]yahoo.com> wrote: [Quoted Text] >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]