We help IT Professionals succeed at work.

Data model for holiday apartment rentals

leppis75
leppis75 asked
on
Could somebody make a sanity check for this data model, that I've created. I'm about to start building a booking system for a small company, that rents out holiday apartments.

The requirements on the client side are, that the user should prior booking be able to see the available dates in a calendar format. As well as booking the apartment, the user should be able to book some extra services, such as airport pick-up. The customer has to pay x % of the total amount as booking fee, and the rest upon arrival.

On the admin side, the user should be able to add different rate periods. E.g. season A would include Christmas, New Year and Easter.

Data model
Comment
Watch Question

Commented:
Thanks for the opportunity to comment on your design.  Please accept my observations and criticisms in the constructive sense they are intended.

1. Perhaps you intend to flesh it out later, but Apartment is conspicuously lacking detail to describe the apt.  I would expect:
    Address
    #Bedrooms
    #Bathrooms
    Sleeping capacity
    Size
    Narrative Description
    Amentities
    etc.
 
2. I don't see any place where you are recording the booking fee.  Seems like the service table and rate period table should hold the booking fee.

3.  I see no representation for the apt owner or any relationships between the apt owner and the rental service.  

4.  I believe RatePeriodDate represents a day that an Apt is rentable within a RatePeriod.  In that regard, I see no reason for a surrogage key on that table:
RatePeriodID + Date is an excellent primary key for that table.

5. Rather than a 1:M between Apartment and AP_Booking, I would put a 1:M between Ap_Booking and RatePeriodDate.  In other words, Add a BookingID (nullable) to the RatePeriodDate table.   In words, your model conveys this business relationship:

   An apt may have many bookings

my suggestion conveys this, instead:

   A booking may cover many RatePeriodDate(s).

Maintaining the relationship between Booking and RatePeriod at the individual date level will simplify many queries that you need to do.
   






Author

Commented:
Thanks dqmq. I realize now, that I should have given a little bit more background information about the company. They're not just administering the apartments, but they own the apartments themselves, and we're talking about a very small number of apartments, and the people using the booking system as admins, are the owners themselves.

1. The apartment table has so little information about the apartment, because the apartment data, that is visible for the user, is stored in the CMS (WordPress), which at least for now is separate from the booking system. However, I've just realized, that of course I should probably include at least maximum number of guests and the length of minimum stay. I also added the address to make it easier for the admin user to recognize the apartment.

2. You're right, I'd forgotten to include the rate field. I've now put it in the RatePeriod table.

3. This is because the owners themselves are the ones renting and administering the apartments.

4. I've removed the unnecessary key from RatePeriodDate.

5. Thanks for this tip. Anything that makes my queries easier, is very welcome :-)

I've attached a new version of the model. Could you check, that I've followed your instructions correctly.

I'm particularly uncertain about the relationship between apartment and the rate tables. The structure should convey this:

"One apartment can have many rates depending on the period, and a period can either consist of consecutive days or it can be spread out, e.g. some days around Christmas and some days around Easter may belong to the same period."

Would you say, that this is now the case?
database2.png

Commented:
You followed my instructions to the letter, with one exception.  I mentioned that the key to Rate_Period_Date should be: Rate_Period_ID + Date.  Otherwise, you will be unable to book two apartments on the same date.

However, having dwelled on it a bit, I now have a better suggestion.  As your design stands now, you are limited to one booking per day (across all apartments).  Too strict.  If you add Rate_Period_Id to the key as I suggested, then you can book the same date multiple times for the same Apartment.  An improvement, for certain, but we can do better.

Here's how.

Move Apartment_ID to the primary key of Rate_Period table so the key is then Apartment_ID + Rate_Period_ID.   Since the table now represents a rate period for an apartment, let's be consistent and also rename the table ap_rate_period.  

Next, add Apartment_id to the primary key of Rate_Period_Date table. With the primary key = apartment_id + date, your structure very nicely enforces a single booking per day for each apartment. Sweet.   Of course, you still want a relationship to ap_rate_period based on the columns rate_period_id + apartment_id.  Accordingly, the table should probably now be called ap_rate_period_date or ap_booking_date (I kinda like the latter).

Beyond that, I want to propose a further enhancement, but will wait until you respond to the above suggestions.

Finally, I question whether you still need start date and end date in the booking table. The actual dates booked (or unbooked, for that matter) can be determined from rate_period_date.  

 

Author

Commented:
I don't think I've ever used multiple columns as a primary key before, but this the most advanced database related assignment I've ever had. I wasn't sure how to mark it in the model, so I just underlined everything, that belongs to a primary key.

Should I now remove the apartment_id foreign key from ap_booking? I'm not sure, but I'm thinking, that it might be redundant.

I'm afraid, I'm so much of a beginner, that I can't really challenge you about your suggestions. I trust that they're good :-)
database3.png
Commented:
>I don't think I've ever used multiple columns as a primary key before, but this the most advanced database related assignment I've ever had. I wasn't sure how to mark it in the model, so I just underlined everything, that belongs to a primary key.

That works. Designation of keys that enforce uniqueness (as defined by your business rules) is one of the most important parts of the design. Single-column keys with no business meaning are called "surrogate keys".  Given they don't have business meaning, neither does their uniqueness assertion.  For example, consider your Apartment table.  As designed, it permits two apartments with the exact same name.  If that is not what you intended, the surrogate key is inadequte. Sometimes, you want  additional columns in the primary key or even other unique keys in addtion to the primary key.


>Should I now remove the apartment_id foreign key from ap_booking? I'm not sure, but I'm thinking, that it might be redundant.

Yes.

Also, remove rate_period_Id from the Primary Key of AP_Booking_Date.   Again, think about what uniqueness you want that key to enforce.  I suggest it is Apartment + Date, not Apartment + Rate Period + Date (which would permit the same booking date in two different rate periods for the same apartment).

Keep the Rate_Period_ID column, however, it is still needed for the foreign key to ap_rate_period.


>I'm afraid, I'm so much of a beginner, that I can't really challenge you about your suggestions. I trust that they're good :-)

You are a smart beginner to put so much thought into your model and to seek advice from the expert community. Don't be afraid to ask questions or challenge--we both get smarter that way.

Author

Commented:
Thank you so much for your patience and for sharing your knowledge. Here's the hopefully final edition of the model. It's nice to be able to move on to the next phase knowing, that the database model has been reviewed by someone, who knows what he's talking about.
database4.png

Author

Commented:
This answer exceeded my expectations. Thanks again.

Commented:
Very good.  Note that ap_booking_date.Apartment_id is part of the primary key and is also part of the foreign key to ap_rate_period.

Good luck