Best logical way to restrict user from editing a record in the database on a website?

[EnVy_28]

Long time no post everyone...

 

So, I am currently building a private php system for a client, every staff in the clients company can access, retrieve, edit, and delete data from the database. Now the only problem with everyone be able to edit anything (according to them) is that when, for example, 2 users tried to edit the same record for example, record_id1, if user2 save his session of record_id1, then when user1 refresh his page, suddenly the data will changed.

 

NOTE: This is really not how the system works, but its close enough, LOL.

 

Now what the client wants is to restrict, users from saving a record_id1 IF, record_id1 is already being used by some other dude.

 

NOTE2: The way the user is able to use a record is like, for every page, there is a search id, which they will use to search for the id they want to edit.

 

NOTE3: I already planned something out, but Im just wondering if there is a better and easier way. What Im thinking is, when a user search for a record_id, I will save the username and record_id and the position in the db. So for example when user1 search for record_id1, the table would look like this:

 

--------------------------------------------

|   id  |  seq  |  username  |  record  |

--------------------------------------------

|   1   |    1    |    user1      |     1       |

--------------------------------------------

 

seq 1 means he is the first one to use the record1. So when user2 uses the same record number, I will just save user2 as seq 2 (second user) and so on. So the only way a user can save is when he is seq 1. 

 

But the problem with this approach is that, what if the user leave? logout? or just close the browser? what happens to the record? LOL. I suppose I can add AJAX call to check if the user is still online? But is that possible, can you know if other users are online in a website? I know this forum does show user whos online right? On the top page? How do you do that? No need to write a code or anything, I just wanna know how does that work?

[Ciccioo]

when a user loads the page (views a record), you can log the time at which that happened

to have more precision and a 'realtime' update you can periodically ping the server in ajax to tell the server that the user is still there, still active. this way, if the script pings every 5 seconds and the server didn't get any ping for, like, 15 seconds, you can assume that the page has been closed and release the lock on the record

the problem with this is that if user X has 40 tabs open, and in one of those there is a record open but he forgot about it, the whole team won't be able to access that record

 

when, for example, 2 users tried to edit the same record for example, record_id1, if user2 save his session of record_id1, then when user1 refresh his page, suddenly the data will changed.

imo that's the expected behaviour

hiding the records that are already in use could be too restricting, because maybe i just want to visualize the records without editing them. this might be acceptable in your specific use case though

the best thing is a google docs style feature that tells you who is currently working on the doc, or maybe notify you if you're not working on the latest version of the record. this way, if you're working in an office, you can just yell at the other person and decide who should work on the thing

 

the forum just logs activity when you load a page. if you opened a page in the last 15 minutes, then you're online

[Brenz]

In real world situations this isn't a problem. Staff should only be modifying data for a reason and the chances of two people working on the same persons account at the same time is unlikely and if anything all work for a specific account should be given to a single staff member

[dmegatool]

Maybe it shouldn't be "while editing" but "has been edited". That's how Drupal work. I guess when you press edit, it saves the state of the record. When you try to save, it compares if it's still the same in the DB. When it's not, you get a message like "Data has been changed in the DB and cannot be saved". You're back at where you were, editing the record. So from there you can reload and start again... Not too bad if you don't have too much modifications.

 

There's also functions that requires locks but I rarely see them so I wouldn't know. Maybe you could check how they manage things... 

[EnVy_28]

the problem with this is that if user X has 40 tabs open, and in one of those there is a record open but he forgot about it, the whole team won't be able to access that record

In the current system, that is not a problem, in fact the client wants that kind of functionality. If they want to edit something and someone is already editing it, the only way they should be able to edit is to call the person using it and just tell them to stop doing what they are doing. Thats what they want. Dont know why, theyre weird. 

[EnVy_28]

Maybe it shouldn't be "while editing" but "has been edited". That's how Drupal work. I guess when you press edit, it saves the state of the record. When you try to save, it compares if it's still the same in the DB. When it's not, you get a message like "Data has been changed in the DB and cannot be saved". You're back at where you were, editing the record. So from there you can reload and start again... Not too bad if you don't have too much modifications.

There is already a functionality inside the website which looks for changes by comparing the database's data and the user's data, basic that's how the current save button works, when you click save, if there are changes, the data will be save, if there is not, no saving of data will occur but will transition to the next page, there are 3 pages, the only way you will be able to go to the next page is when you click save on the previous page.

 

 

Oh and did I also mention that when user1 is editing a record, user2 should be able to look through the data, but not change it.