Applying normalization to a relational Database (Access & SQL Server) This question is pretty specific but I hope it can get answered on here. There is a wide range of information about normalization but I feel what I have read so far isn't as effective as just stating my situation.

I'm having trouble discerning what NF my tables are and applying normalization principles to my model. Also whether some tables should be left in 2NF and not 3NF, if it even exists and for what reason. I'm having difficulty transitioning from creating a regular database and normalizing the whole thing.

I believe the tables I have attached are all that is needed.

I will be building this in both Access and SQL Server, with that said I will be creating a form for making bookings and billings from Access that should be able to be input into the database.

Side question: I have been asked to create a report for a profit and loss statement, while this can be done in Excel is it possible to make a table in the database?

Thank you and please feel free to ask any questions relating to the topic

I have been asked to setup and ODBC driver to link with Access. 





EDIT: I have decided that the BIKE table be renamed to BIKE_HIRE and have a BIKE table with the actual details of the bike itself. Also to create the Income/Expense table, have it linked to the PAYROLL table. If anything else needs to be normalized please suggest it.

EDIT 2: Specifications: This may give further insight into the database, but I'll be working on it with the help, thanks !


The business is a small 5 room guest house home stay.
They have motorbike and car rentals and propose that only motorbike rentals be recorded in the database.
There is little staff which duties along with the manager are housekeeper, guest liaison, housekeeping, accounts.
Drivers are paid on the day and don't need to be included on the bill or database. (Details may be necessary)
Guests are given a form to fill for their details when they enter. 
Proposed that payments have different rates depending on days.
Client wants to be able to record expenses and income


Based on this, is it possible that maybe some entities are better left as 2NF?

Edit3: The solution for my case was simpler than possible. I left Payment unnormalised, and attached an Expense table onto Payroll, this was all I needed to do as this was a sample .beginner project for me
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!