relationship help (1 Viewer)

[Portlett]

Im doing a hotel booking system and need some help with my relationships.
I have 3 tables

Bookings - First name, last name, date of arrival, length of stay, room, booking reference, customer ID

Rooms - room, type, facilities, floor, price, customer ID

Customers - first name, last name, street address, city, county, post code, telephone number, customer id, room.


Any reccomendations for what the relationships should be if im doing a booking form would be great.

 

[ejstefl]

You should probably post this in the tables forum, but here's my take:

Rooms: RoomID, type, facilities, floor, price
Customers: CustomerID, first name, last name, address, city, county, post code, telephone number
Bookings: BookingsID, RoomID, CustomerID, date of arrival, date of departure

Bolds are primary keys, italics are foreign keys.

You might want to store price in bookings as well, if the rates can change.

 

[x0reset]

First, what ARE the relationships?

One customer can have one room? Many rooms? Can one room have more than one customer? One customer can have one booking? Many bookings? Can more than one customer have the same booking? Can one booking have many rooms? Many rooms have many bookings?

Do a search for "One to Many" and "Many to Many" relationships and decide what your relationships actually ARE. After that, the table structure is easy.

Your current table structure suggests a Many to Many relationship between customers and rooms (each room can have many customers, and each customer can have many rooms) with bookings as the joining table (each customer can have many bookings, and each room can have many bookings). Is this correct?

 

[Ethereal]

also try to name your fields without spaces, It saves hastle later on

 

[Portlett]

Thanks for the help, here is what im trying to do. Any further suggestions would be much appreciated

Required:

1. Design a database to do the following:

Keep track of customers, their permanent addresses and their telephone numbers
Keep track of each room and the facilities it offers
Keep track of the price of each room type
Provide a form for the receptionist to make a booking for a customer.
Record the length of a stay in a specific room by a visitor.

Bear in mind that:
o A customer may make several bookings, not always in the same room.
o A room may be occupied by more than one person.

 

[x0reset]

Well, here is where I would start:

lkpRooms
----------
RoomID
RoomDescription
RoomTypeID
More room fields...

lkpRoomTypes
---------------
RoomTypeID
RoomTypeDescription
RoomTypePrice
More room type fields...

lkpFacilities
------------
FacilityID
FacilityDescription

lnkRoomTypeFacilities
----------------------
RoomTypeID
FacilityID

tblCustomers
--------------
CustomerID
CustomerFirstName
CustomerLastName
CustomerAddress
More customer fields...

traBookings
-------------
CustomerID
RoomID
NumberofGuests
CheckInTime
CheckOutTime

This assumes that facilities and prices can vary for Room Types, and not for individual rooms. There are many to many relationships between RoomTypes and Facilities (lnkRoomTypeFacilities), and Customers and Rooms (traBookings).