Ms-access – I need help to migrate denormalized table content to normalized form- MS Access

If you import data and it seemingly disappears, you need to check the indexes on that table. You should do the following:

• Switch the table into Design View
• In the Table Tools Section, under the Design Tab, click on Indexes
• You will see a list of Indexes pop up

If any of the Indexes have one or more columns that are unique, those rows may have vanished upon import. You may need to either remove all indexes (do not touch the PrimaryKey) or Change the Unique Property on all Indexes from Yes to No.

Then, just reimport the data.

If all 235 rows are in the table already, go to your form's Property sheet. Click the Data Tab. Look at the Record Source and see if it is a table name or query.

You need to also go back to the Property Sheet, Click the Format Tab. Look over the Default View and set it to Single Form. This will view the table rows one row at a time. Click the Last Record Navigation Button. It had better say 235 of 235.

Sorry, this may not be An Answer, but I couldn't add a comment.

This is surely a 'solved problem' in that hotel and reservation systems are available commercially 'off the shelf'? Why develop your own? Even if you do decide for good reason that you need to, it might well be worth investigating the data models underlying a few commercial products, if you can find a way of doing that.

However, if you ARE developing your own, I think you need many-to-many relationships between BIKE and GUEST (as I think you have recognised in your additional note), and between RENTAL DRIVER and either BOOKING or GUEST. These would be modelled on the same basis as you have already done between BOOKING and ROOM, with an intermediate table - which I would suggest naming ROOM BOOKING rather than BOOKABLE for ROOMs, BIKE_HIRE (as you have suggested adding) or BIKE_BOOKING for BIKEs, and CAR_BOOKING for car and driver).

A room that is NOT available for booking could be 'booked' to something like deep clean, re-decoration, renovation, re-plumbing or whatever reason makes it unavailable for guests to book it - I assume your Status field would cover things like that.

One BOOKING can include multiple GUESTS (as you have noted). And surely one GUEST can have multiple BOOKINGs over time? And might rent a car and driver or a bike more than once in one visit of several days or week(s)? Indeed, one BOOKING for several GUESTs might need you to add the concept of (and a table for) GROUPs, about which you might or might not know individual names, or just the number of people in the GROUP - for example, if you were to cater for a wedding party.

I would seriously consider generalising the concept of BOOKING, so it can cover ANY type of BOOKING - use a field Booking Type with a lookup from a BOOKING TYPEs table. Most of the fields needed for a booking will be similar, and the others can either be included but not used on some types of booking, or you could use the BOOKING TYPE table to label fields in BOOKINGs appropriately for that type of BOOKING.

If you have business guests, they might need to book meeting rooms, projectors, flipchart stands, etc, and these can just be added as different BOOKING TYPEs without needing any more tables to be defined.

You only need to store two of the three elements of the duration of a BOOKING - StartDateTime, EndDateTime, and Duration, and calculate the third.

Some bookings (like Room bookings) might have a Duration measured in whole nights (unless of course you are running another type of hotel?), others might be in hours or half days (Bike bookings for example) depending on your booking policy.

Can EMPLOYEEs sometimes be GUESTs? Or Rental Drivers be Employees? If so, you might consider introducing a PERSON table, with a PERSON_TYPE of Guest or Employee or Rental Driver for example. Or the Employee type might be extended to the role the Employee plays - from Owner, or Chief Exec, through Manager to Porter, Cleaner and Maid (to give a few possible examples).

It might also be worthwhile to separate out ADDRESS (postal address and probably land line phone) and CONTACT DETAILs (other phone, cell/mobile, email address(es), etc) from EMPLOYEE or GUEST entities. That way, one address may cover related Employees living in the same place, or a Guest GROUP or Family, whereas CONTACT DETAILs may apply to individual EMPLOYEEs or GUESTs. Again, you could have one field for contact detail type (lookup), and another which can hold (in multiple rows) phone or cell/mobile number, fax number, email address, Skype contact, IM address or any other type of contact detail that might in future be useful.

Will you be catering for meals? In which case, you might want to include Dining Room or Meeting Room (if you have more than one of either) and TableNumber as Bookable resources.

And I think you might need a lot more fields and tables to cover PAYROLL. Days or shifts or hours worked? Hourly or weekly or monthly payment type, and corresponding rates? Tax band? Tax due, Tax paid? Or maybe payroll is handled quite separately?

Anyway, there are a few thoughts to consider. I've developed several types of database in my career, but am not a professional database developer nor administrator, nor do I have any specific knowledge of hotel reservation systems. But I HAVE helped to implement Theatre booking systems, and some of the concepts I suggest come from that experience. I have always found it worthwhile to try to generalise beyond one table for one specific type of entity, if similar ones can be added merely by extension with an extra field or two, and a SOMETHING_TYPE of lookup table to tell you what kind of SOMETHING you are dealing with.

Best wishes for your development plans.

A later realisation. I think you already have much of what I suggested. BUT I am rather thrown by your use of Arrival Date and Departure Date in your BOOKINGS table. That makes it look conceptually more like a Guest Stay than the kind of Booking I was suggesting. Maybe you need a new concept for stay, and a table to match?

And I think you also need a concept of Bill and Line Item on the Bill as places to keep financial data, and the basis for charging and payment processing.

Going back to another commentator's mention of Functional Definition, consider how you might handle cases such as one person making a reservation for two rooms for three guests, two sharing, with different arrival and departure dates for each guest. And one of them wants a bike on days 1 and 3 of their stay, a car and driver for day 2 for all three of them, and three bikes on day 4.

And by the way, they are splitting the Bill!