If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
Hostel Database Setup
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 |
#3
|
|||
|
|||
Hostel Database Setup
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 |
#4
|
|||
|
|||
Hostel Database Setup
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. -- |
#5
|
|||
|
|||
Hostel Database Setup
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: |
#6
|
|||
|
|||
Hostel Database Setup
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. -- |
#7
|
|||
|
|||
Hostel Database Setup
.....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. -- |
#8
|
|||
|
|||
Hostel Database Setup
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. -- |
#9
|
|||
|
|||
Hostel Database Setup
Hostels and Hotels often have a suprising number of "John Smiths"
David F. Cox "Jamie Collins" wrote in message oups.com... 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. -- |
#10
|
|||
|
|||
Hostel Database Setup
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 |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
How do I setup an interactively editable database? | Hunt4868 | New Users | 2 | January 13th, 2006 04:08 AM |
Toolbars, Drop-Down Menus | Rick | New Users | 1 | September 21st, 2005 11:17 AM |
Access Error Message when opening database | eah | General Discussion | 3 | January 26th, 2005 11:04 AM |
Productkey problem when installing office 2003 on network | Stefan Schreurs | Setup, Installing & Configuration | 1 | June 1st, 2004 11:16 PM |
Office XP | Ed Lester | Setup, Installing & Configuration | 1 | May 27th, 2004 09:30 AM |