I'm designing an FM database for a multi-user environment, and there's an important function of the database that concerns me.
In short, the function enables the user to create a new parent record and its various child records based on a pre-existing template parent record and the corresponding child records of that template.
In detail, the way that it works is as follows:
The database first asks the user to identify the pre-existing target parent template, and then sets a variable with the unique id of that parent template (ID_ParentTemplate).
It then conducts a find among all the children of that targeted parent template in order to prepare the found set of template children for import.
It then creates a new parent record, and then sets a variable with the unique ID of that new parent record (ID_Parent) that will later be applied to the imported children of the targeted parent template so that they'll be adopted as new child records of the new parent record.
It then imports all of the records in the found set in Step 2 into a separate "nursery" table where through a looping method the ID_Parent is established for each newly-born imported child record and where various other housekeeping operations are additionally applied to them.
Once these housekeeping operations are complete, the database then imports THOSE prepared child records into the table for all the child records associated with the various parents in the database. Because the ID_Parent was established for each child record in Step 4, the pre-existing relationship between the parent and its children is automatically established. Note that in Step 4, the database first deletes ALL of the records then existing in the "nursery" before the import in that Step 4 occurs, so as not to create a mess in the "nursery" from prior operations.
My concern is what disasters, if any, may arise when and if users effect the above script at more or less the same time? I'm particularly concerned about the deletion that occurs in Step 2 that I referenced in Step 4. Couldn't it happen that a trainwreck occurs if one user effects the script just before or after another user does? Or is the order of operations sufficiently established that the first user will automatically come behind the second user and that the deletion of all the records and the subsequent looping script in Step 4 will not result in the application of prescribed attributions to the wrong set of child records, and the later importation of the wrong records?
Your thoughts would be much appreciated; many thanks in advance!