Martijn Tonies wrote:

> Hi,
>
>> I've just joined and I'd like to ask for your help.
>>
>> I'm migrating from MySQL to Firebird (Firebird seems to be sooo much
> better!) and I've run into a database design issue.
>> In the system I'm designing, multiple users access documents, but one
> document can only be edited by one person at a time.
>> Thus, I thought about creating a separate "resource reservation" table,
> where users can reserve resources for themselves - till they need it.
>> A typical reservation would consist of a SELECT then an INSERT operation.
> If the SELECT operation says that there is no other reservation on that
> SPECIFIC record, the user can use INSERT to reserve it.
>> The problem: between one user's SELECT and INSERT statements, another user
> could do an INSERT - the result would be 2 reservations on the same
> resource, which is a catastrophe!
>
> Make sure the table has a unique constraint on "document to edit" and you
> cannot insert 2 of the same in it. So, if the insert fails, someone else is
> editting the document.

Next normal step after that is to include a field - 'editor' - and put the
user_id of a person editing the document into it. May need a little
administrator help if the value is not removed if the person does not finish
editing properly, but it allows you to see who IS supposed to be editing a
document which can be useful.

--
Lester Caine - G8HFL
-----------------------------
Contact - http://home.lsces.co.uk/lsces/wiki/?page=contact
L.S.Caine Electronic Services - http://home.lsces.co.uk
MEDW - http://home.lsces.co.uk/ModelEngineersDigitalWorkshop/
Firebird - http://www.firebirdsql.org/index.php