Mysql – Concurrent Users and Foreign Keys

concurrencyforeign keyMySQL

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.

Best Answer

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.

Related Solutions

Mysql – Database Design: What is the best structure for storing site offices and locations

Here is some vanilla SQL:

CREATE TABLE RoomTypes
(
 RoomType VARCHAR(12) NOT NULL,
 UNIQUE (RoomType)
);

CREATE TABLE Zones
(
 Zone VARCHAR(10) NOT NULL,
 UNIQUE (Zone)
);

CREATE TABLE Rooms
(
 RoomType VARCHAR(12) NOT NULL,
 Zone VARCHAR(10) NOT NULL, 
 Name VARCHAR(30) NOT NULL, 
 UNIQUE (RoomType, Zone, Name), 
 FOREIGN KEY (RoomType) REFERENCES RoomTypes (RoomType),
 FOREIGN KEY (Zone) REFERENCES Zones (Zone)
);

CREATE TABLE NumberedRooms 
(
 RoomType VARCHAR(12) NOT NULL,
 Zone VARCHAR(10) NOT NULL, 
 Name VARCHAR(30) NOT NULL, 
 CHECK (RoomType = 'Numbered'),
 UNIQUE (Name), 
 UNIQUE (RoomType, Zone, Name),
 FOREIGN KEY (RoomType, Zone, Name) 
    REFERENCES Rooms (RoomType, Zone, Name)
);

CREATE TABLE AncillaryRooms 
(
 RoomType VARCHAR(12) NOT NULL,
 Zone VARCHAR(10) NOT NULL, 
 Name VARCHAR(30) NOT NULL, 
 CHECK (RoomType = 'Ancillary'),
 UNIQUE (Zone, Name), 
 UNIQUE (RoomType, Zone, Name),
 FOREIGN KEY (RoomType, Zone, Name) 
    REFERENCES Rooms (RoomType, Zone, Name)
);

The CHECK constraints will not be tested by mySQL e.g. do the tests yourself using triggers. Consider adding other tests e.g. that attribute Name in table NumberedRooms represents an integer.

The idea that every row in the supertype table Rooms will have exactly one row in the union of AncillaryRooms and NumberedRooms. This is merely implied e.g. have 'helper' procs to add rows to both super- and subtype tables as a single operation and use triggers to ensure it is done.

Note NumberedRooms has a simple key Name alone, whereas AncillaryRooms has a compound key on (Zone, Name). All three (non-lookup) tables have a key on (RoomType, Zone, Name) throughout, allowing further subtype tables to referencing them and maintain integrity by further testing for valid RoomType values.

Mysql – Determining Homogeneity by Common Foreign Keys

For the table A (with B_id) and table B (with id)

Count of Primary Keys By Foreign Key

SELECT FK,COUNT(1) PKCount FROM
(SELECT IFNULL(B.id,0) FK
FROM A LEFT JOIN B ON A.B_id = B.id) K
GROUP BY FK;

If FK is 0, then PKCOUNT is a count of Primary Keys that do not have a Foreign Key

To be 100% homogenous, FK should never show up as zero(0) (In a Perfect World).

Count of Foreign Keys Used By Primary Keys

SELECT B.id,IFNULL(FK_Count,0) FKCount
FROM B LEFT JOIN 
(SELECT B_id id,COUNT(1) FK_Count FROM A GROUP BY B_id) K
USING (id) GROUP BY B.id;

If FKCount is 0, then B.id is a Foreign Key Not Used By Any Primary Key

To be 100% homogenous, FKCount should never show up as zero(0) (In a Perfect World).