Help with table structure of kennel database

[CraigDolphin]

Hi all.

For fun I'm embarking on a little db project (need a life methinks).

Basically it's for a combined dog kennel and dog training facility. Now, I'm aware that there's been a lot of hotel booking questions on this forum which are usually greeted with 'look at the sample database section' response: which I did and came up empty. I've searched and found some useful threads also. One of which led me to a data model which can be seen at
http://www.databasedev.co.uk/image/hotel_room_booking_data_model_image.gif.

Now, I don't want to completely re-invent the wheel if I can help it, and I'm sure I can adapt the model somewhat to suit this scenario even though I'm not a guru like some here. But there's something about this model that makes me a little 'iffy' and I was wondering if someone could help me determine if I'm right that I ought to move some of the fields in tblBookings to tblLINK_BookingsRooms.

Basically, imagine if a dog owner books in two dogs for two different durations. For example, Rover and Rex are both scheduled to arrive on March 1st, but Rover is staying one night while Rex is staying two nights.

Since the duration of the stay would be dependent on the dog and the booking, not solely the booking itself, wouldn't it be more appropriate to place the dteBookedStartDate and dteBookedEndDate fields in the LINK table?

I should clarify that a 'room' can only contain one dog at a time.

 

[The_Doc_Man]

This is off the top of my head because I can't follow links from my site. The security folks gork when I do that.

First and foremost, a booking depends on the room and the date. But only the room is permanent.

If you have a repeat-customer (and repeat-dog) table, then your booking details are a many-to-many relationship implemented by a junction table that includes date, room, and dog. Assuming dogs don't change owners THAT much, you could leave owner as a FK to the dogs table.

To search for conflicts, you search the JUNCTION table for given dates and find rooms that AREN'T occupied. Searching the forum for "Bookings" should give you lots of hints on that topic independent of who (or what) sleeps in the room.

 

[CraigDolphin]

Hi Doc_Man,

thanks for taking the time to reply. I have attached a jpeg of my relationships table and maybe you'd be kind enough to glance over it?

The table names are different than the original datamodel (also attached as a gif)and there's an extra complication: the business has two areas that must be modelled

1. The kennel-boarding side of the business
2. Dog training classes side of the business

For now at least, I'm concerned about the fields BookingKennelGuestStartDate and BookingKennelGuestEndDate that I have placed in the table 'BookingKennelGuest'.

The original data model I was following had the equivalents of these two fields placed in the equivalent of the Booking table. My concern is that if two dogs (Guests) are booked at the same time (one 'booking') but for differing stay durations then placement of these fields in the Booking table would be a problem.

If I am right, is this going to foul up my ability to track room bookings on dates etc down the road? Or am I violating some rule of normalization by moving these fields?

As for your advice on how to find conflicts etc, it is much appreciated. I'm doing this primarily as a learning exercise for myself and this kind of db is an interesting change from the stuff I've been working on lately.

 

[The_Doc_Man]

Two guests booked at the same time MUST be treated as two bookings since they are not allowed to share rooms. It doesn't matter HOW many bookings occur at once, each is an independent event because each depends on a non-sharable resource.

 

[CraigDolphin]

Two guests booked at the same time MUST be treated as two bookings since they are not allowed to share rooms.

Hmm. So you can't have a single entry in the booking table linked to two entries in the BookingKennelGuest table? The primary key of that table combines the Booking ID the KennelID (RoomID) and the GuestID (dog id) so I thought that meant I could book more than one guest at a time so long as they are placed in separate rooms(kennels).

Maybe I need to spend some more time thinking about your response and reading up more on the issue of non-sharable resources and tables. I appreciate the advice Doc_Man

 

[CraigDolphin]

Perhaps this might be better.

I have added an intermediary table between the Booking table and the BookingGuestKennel table. Thus, a single booking can have more than guest, with their individual start and end dates stored in the BookingGuest table, while the kennelID and occupancy dates are stored in the BookingGuestKennel table (along with the foreign key BookingGuestID to link things back to the Booking and guest information).

The combination primary key of KennelID and OccupiedKennelDate should prevent accidental double-bookings of the same kennel on the same night.

Or does this still miss the point?

(I removed the training-related tables to reduce clutter)

 

[The_Doc_Man]

The idea is that from the ROOM's viewpoint, all bookings are separate events. You have said it yourself, that a dog owner could book two different dogs for two different time spans.

You need two terms that are similar but have some minor nuance of difference to make this work as you are describing. Perhaps I'll go overboard and call the thing you are trying to grasp where one owner has multiple dogs an INVOICE. The thing that relates to one dog in one room is a BOOKING.

An INVOICE can relate to several BOOKINGS via common invoice number on the BOOKINGS entries. INVOICE/BOOKINGS//ONE/MANY.

An INVOICE is probably how you would bill the owner anyway, right? Don't confuse the reservation of the room with the phone call that was the vehicle for making the reservation. That's why I'm taking the approach I'm taking Note that this INVOICE concept lets you make as many bookings per invoice as you want, from 1 dog for a single day to a whole kennel full for a whole year. Makes no difference as long as the unique things are kept unique and the common things are kept common. (An offshoot of normalization rules.)

 

[CraigDolphin]

Ah. Okay. Thanks for the education Doc_Man. I'll have another crack at it tonight. I want to spend the time up-front to ensure the design is right before I start working on the rest of it.

 

[melindaregner]

Pretty old posts..
So, How'd it turn out? Can you post the finished structure?
Thanks!