Leaderboard

OK, you have my sympathy.

Agree with chhorn: put in the person table, unless you want the historic record of Avatars. Hopefully you are using the InnoDB engine for all your MySQL tables. If not, you can run ALTER TABLE on them to change the engine from the default MyISAM. With InnoDB, when you SELECT a row from an InnoDB table by the primary key, the read activity includes all the data anyways, since InnoDB has clustered indexes. This type of micro-optimization isn't really that useful to consider when thinking about DB design, but you might as well sleep well at night knowing that there's no significant performance cost to putting it into the person table.

There are many different ways to handle this. Personally I would probably use Redis hashes for this, or a mongoDb collection, but if you don't have those options, then one of the simplest and cleanest options is to use SQLLite3. There is full support for Sqllite via a node library, and it has essentially no footprint other than the underlying c libraries. Tables can be persisted to the filesystem or used in memory. I see no reason not to persist them to a file. You would need a simple table keyed by robot name with the following fields: robot (text) user (text) from (integer) to (integer) The from and to columns would be timestamp values. Personally, it would be nicer to use websockets rather than polling, but either way, the client code will query the db for a particular robot where the current timestamp > from and < to. If any rows come back then, you check if the user is the current user. If not, then display to the user that "user x currently has this robot". The entire table could also be queried to show which robots are currently assigned to which users. If a user wants to assign themselves an available robot, then then simply insert a new row with the current time as from, and some reasonable future time that syncs with your client "checkin" ajax code. 5 or 10 minutes might be a good starting point. The "checkin" code would query current user's robot row, and update the from, extending it by 5 or 10 minutes at each checkin. The advantage of this is that any allocations will expire if the user doesn't release the robot properly or their page dies. Sqllite will handle concurrency and locking for you via transaction support (you wrap your select and insert/update activity in a transaction). To release an allocation, you'll just need a routine that deletes the row for that robot. You might also want another table for the robots themselves, and even use a standard relational 1-M model with an id for key, but that is up to you. Somehow the code needs to know the fulll list of available robots, and you might as well use sqllite for both problems. Just to keep the database pruned and small, I'd suggest writing a small program that runs from cron, and cleans up any expired robot allocation rows ( current timestamp > to). You could run this in whatever iteration you would like. There might be some value in having a historic record of robot allocations, so cleaning out old rows removes that, whenever you actually run the pruning.

This is my take on your question. Have a sign in page. This will give the user an identification. On that page list all things that are to be worked on. Identify which items are in use and which are open. Maybe back ground color etc.. The user selects what he/she wants to work on first - that assigns this page to her. Then have user pick which he wants to be notified when available. Add him to the list. You can work thing out from there.