Joins - One PK to many FK - Acceptable?

[Dega]

Hi,

Attached is a .pdf where I have one table with it's PK pointing to several other tables FK.



It looks easier to query the tables this way but is this going to create problems for me?

I also will also need to add a few more tables later on.

The Customer will always be the contact for the Booking (billing & reservation) and any Guests should be referenced this way.

If this is not acceptable can someone advise me on how to redo the relationships?

Thanks

[TheShabz]

First off, I think you'd want to make a many to many relationship between customer and room. I would remove the customer FK from tblRoom, and create a RoomID FK in tblBookings. keep the 1-M relationship between customer and booking, and and a 1-M from Room to Booking. This way, you leave open the possibility that a customer can have multiple reservations. If not, that room will forever be tied to that one customer (as you have it currently).

Second, I would also add an "isAvailable" field to the Room table so when you query, you can search for only rooms which are not booked.

Third, what is the difference between Customer and Guest? If its merely that a guest is a customer that has an active booking, I'd simply add a field to Customer called "isGuest" as a yes/no or as a -1/0 field.

Hope that helps.

[Dega]

Ok ...

Thanks for the advice and I will start changing it.

So you are saying drop the Guests table and just add a field to the Customer table "is guest".

I new I had this overly complicated for nothing ...

Will work on this tonight and thank you.

[Dega]

First off, I think you'd want to make a many to many relationship between customer and room ...

I did what you suggested but now I don't have a FK in rooms for a many to many join.

Thanks

[Dega]

Aside from my last post on how to make a relationship between customer and room I have to group customers (Guest table has been deleted).

One to four people will stay in a room, and I need a table (lastname, GroupID) that will generate the ID so those who book at a later time can be designated part of this group.

I think I will need a lookup table ?

That's about all I need to get started in the right direction hopefully.

Thanks

[Dega]

I made a "junction table" that should represent a many to many relationship between Customers and Rooms?

Please look at the attached .pdf (anyone?)

Uncertain what to do with the Group table as one to four persons could share a room.

Thanks for any help.

[maximus]

can you tell me what you want to do actually. I am not an expert but I think I can help if you will tell me what is it that you want to do.

[Dega]

Thanks

This is a small tour company reservation (motorcoach) database.


tblCustomers: All relevant info for the customer (name, address, DOB, etc)

tblBookings: All releveant booking info: (tour name, tour departure date, duration, tour Price, groupID?, customerID)

tblRooms: Room particulars: ( single bed, two beds, smoking or non smoking, handicapped room, near elevators, ground floor, etc)

Note tblRooms: We do not keep inventory of room availability or control rooms in any way. This information will be passed to the hotel in the way of a access report when the tour motorcoach nears full occupancy.

tblHotelGroup: Everyone should have a groupID wether travelling single or up to four people sharing one room. The last name of the person making the booking is used as a group name.

tblMotorcoachGroup: Many people bring a dozen of their friends on a tour and these people like to sit near each other on the coach.

tblPaymentInfo: Credit card info could be added later.
tblAircraftGroup: Could be added later. (aircraft seating, details)

_________________

It sounds simple but these joins are giving me a pain ...

Thanks for any help.