Hi, I'm creating a booking system for a ski resort and I am struggling with the normalization. Any help would be really appreciated.
Here's what I have so far:
No of Adults
No of Children
No of Infants
No. of Stars
This is my first attempt at designing a database so I know I am probably way off. My main concern is the Extras table. Do I need this table or could I include this data in another table? Also, should I include the room cost in the Hotel Rooms table or the booking table? The user needs to be able to search for available rooms; what data do I need to include for this?
I'm sure I have made many more errors so any help would be much appreciated.
So, a Customer has one and only one Booking ID? What happens when they come back next year, do you create a new Customer record, or re-use the same Booking ID they had last year? Probably neither...
You need to identify the key in each of your relations. If you decide that Customer ID is the key for Customer (which seems reasonable) then your normalisation is saying that Customer ID -> Booking ID, i.e. for each Customer ID there is exactly one Booking ID.
Oh I see. For the rooms I was going to add a field called Occupied or Booked Until; is this unnecessary?
One more question: There are three different levels of ski passes - beginner, intermediate and advanced - and there can be more than one pass per booking. What would be the most efficient way of doing this? Would I create a separate table for Ski Passes or is there a way I can include this in the Extras table?
Sorry for all the newbie questions but this is the final one. Thanks for the help.
At the moment your Booking table books the hotel as a whole. You need a table that records a room booking, either as well or instead. A "Booked Until" attribute won't work: imagine I book a room now for 1-7 March 2010. You can't say the room is "booked until" 7th March 2010!