I don’t need to know the instant the change has been made (although that would be nice), It could be within a minute or even 5 minutes. I just need to know that something has been added or changed by another user recently.
I think I would have a few fields each relevent database table:
last_updated_by which records who last updated it… and if that is not the current user’s id then you can show the alert
last_updated_date which records the date/time - probably in UTC in case you have people in different time zones?
last_updated_action where you can record what happened in some way.
The obviously you need some logic when the user presses [Save] on an item to decide how to update the last_updated_action field.
Then to get the alert fields updated you can either have a scheduler item in your design which goes and gets that information say every minute, or you could put a refresh button in a prominent place so the user can decide when they would like that information updated.
And I’m guessing if different people can edit the same record, then you will need some kind of mechanism to lock out other users while they are doing that…
I do that with a locked_user field which shows which user is editing the record, and a locked_datetime field which shows when they first opened it to edit. Then you need some logic (I use flows! ) to manage what happens if a user tries to open a record for editing and someone else is editing it already… and a policy about what to do if it has been open for ages and ages. I have a 10 minute limit… so if a user has a record open for editing and they haven’t touched the keyboard for 10 minutes then that item is automatically saved and closed so someone else can edit it. Another use of the scheduler and some more flow logic.
I’m no expert,
but if I was to approach this I would use a separate ‘alerts’ table that looks up the id of your main record. Also a date time stamp, another lookup field for the user who created the alert, a text field with the alert text.
Then on any update server actions add an extra step that inserts the new ‘alert’ record, with the appropriate details, using conditional logic. Eg if images updated insert into text field “images updated by”.
Then to bind on your main table, search the alert table for the appropriate record id. Sort by newest date and bind the text field of the first record. Then also concatenate in the user name who is in the user field and the date also if you want.
(Note you could concatenate in the text field when you insert instead of when you bind if it is any easier)
Then just use a timed action to refresh the table every five mintutes.
It’s great to hear different perspectives on a database design problem!
My sense is that the alerts table works well if you have just one kind of data type (table) you are having to manage in this way… but if you are managing lots of different data types (contacts, invoices, payments), then you’d need an alerts table for each type (contact_alerts, invoice_alerts, etc)…
I have soooo many tables in my design that I just end up confusing myself if I then have a gazillion other tables which are aspects of the tables I already have, so I prefer just to put this kind of information in the main table of the data I am storing.
I think a lot of it comes down to how your mind works and making sure you are not wasting vast amounts of database storage and data transfer in the way you choose to work.
@tesla’s solution seems more data efficient to me, but has more tables to search for along the way, so I’m not clear as to whether it uses more or less database horsepower in use. My brain is so lost in the detail of sooo many aspects of my app that I reach of point of saying “oh what the hell, I’ll just do it this way!”.
As app entrepreneurs, we are called upon to be UI designers, HTML/CSS gurus, database designers, database maintenance gurus, vice presidents of marketing, sales, finance and customer support… I find I need to limit how much I try to get “right” in order to maintain my sanity and sleep at night…
Looks like the way to go Neil, that's how i do this type of thing to support record locking in a multi user environment. A data get then a "pulse" to recheck the data update fields although you may want to put the "Data_Modified" and "Data_Editor" in a related file as the "pulse" may be more efficient due to the smaller dataset.
If you need a real time information you could use UDFs and Triggers…
It is not a recommended approach as it will take a toll on performance and it’s a security risk.
So basically you would create an after update trigger that sends an http request to an API endpoint of your choice in your app. Once it hits your API endpoint you implement the logic you need in your app to show that information.
There are far better solutions for this of course
You could deploy a middleware that watches for changes and perform callbacks to your app. This way you can deploy watchers on demand. Check mysql-events. The beauty of this approach is that it reads the log files and not the database so it’s quite performant. You could mix this with web sockets to show realtime data. There is a Feature Request for websockets.
You might even want to check Firebase as it handles realtime quite better than mysql. I believe this was on the roadmap.
) to manage what happens if a user tries to open a record for editing and someone else is editing it already… and a policy about what to do if it has been open for ages and ages. I have a 10 minute limit… so if a user has a record open for editing and they haven’t touched the keyboard for 10 minutes then that item is automatically saved and closed so someone else can edit it. Another use of the scheduler and some more flow logic.