Concurrent Users and Foreign Keys For this question, please assume the following:


I am using MySQL
I have a Location table: Location(ID, Name, Address, TravelDetails, ...)
I have an Event table: Event(ID, Name, LocationID, StartTime, ...)


LocationID is a Foreign Key to Location

The system is an ASP.NET MVC Web Application
The system has multiple concurrent users.


Here is an example scenario that explains my confusion:

In my application, I select "Create New Event". I am given a Drop Down List to select a Location. I am shown the Location Name, but the LocationID of the selected item is what is stored in the Event.

While I am creating my Event, someone else opens a Location to edit some of its details. They could change the Location Name and all other details so that the Location is now effectively a completely different Location (basically, the editor created a new Location without deleting the location first). Those changes don't propagate to my Event's Location Drop Down List until I refresh the page, so I don't have the most up-to-date list of Locations.

With the changes made to the Location, the identity of the Location I wanted to use for my Event could be completely different - it might not represent the same Location as it did originally. Now my Event could be completely wrong because the Location is different than I had intended.

My question is this:

How should I handle this? Is this a problem worth worrying about? Who is to blame? Is it my fault as the developer for not locking down fields such as Location Name (but what if there was a typo in the name...?) or implementing safety checks to inform the user if a Location he is trying to use was changed since he started creating his Event? Or is it the fault of the editor for not being "smart" about completely changing the original meaning/purpose of a Location?

I want to assume users will be smart when making edits, but that is always a bad assumption.
Some discipline is needed.  A location_id must always refer to a particular location, even if the name of the building changes or you make a correction to the address.

The UI must make it obvious whether to the user as to whether he is editing a "location" or creating a "new" location.

When providing the "pull down" in the UI, the id is hidden and uniquely identifies it to the rest of the data; the name, address, etc is useful for humans to know which 'location' it is.

The UI needs a straightforward way to change an even from one location to another -- in case the original association to an event was erroneous or in the case that the even is moved to a different venue.

Nothing if foolproof.

You need scripts that check for double-booking.  They should provide sufficient info to help the admin figure out whether it was a typo, a confusion of two similar location names, etc.