Updating Linking Tables

Question

I've currently adding a bit of functionality that manages holiday lettings on top of a CMS that runs on PHP and MySQL.

The CMS stores the property details on a couple of tables, and I'm adding a third table (letting_times) that will contain information about when people are staying at the property. Basic functionality would allow the user to add new times when a guest is staying, edit the times that the guest is staying and remove the booking if the guest no longer wants to stay at the property.

Right now the best way that I can think of updating the times that the property is occupied is to delete all the times contained in the letting_times database and reinsert them again. The only other way that I can think to do this would be to include the table's primary key and do an update if that is present and has a value, otherwise do an insert, but this would not delete rows of data if they are removed.

Is there a better way of doing this?

Answer 1

Here's my recommendation for the LETTING_TIMES table:

• PROPERTY_ID, pk & fk
• EFFECTIVE_DATE, pk, DATE, not null
• EXPIRY_DATE, DATE, not null

Setting the pk to be a composite key (PROPERTY_ID and EFFECTIVE_DATE) allows you to have more than one record for a given property while stopping them from being on the same day. There isn't an easy way to stop [sub?]lets from overlapping, but this would alleviate having to delete all times contained for a property & re-adding.