Multi users + helper tables + editing conundrum

@Paul_Danyliuk 's awesome work on best practices for creating business docs are incredibly valuable. His not so little web-series on making a template is a great 8 hours to learn tonnes about data structure.

I’ve spoken briefly to paul on a couple of occasions about our own business doc. Its complicated. I built it 18-24 months ago when I knew very little about coda.

For simplicity, lets just talk about a simple project database.

Our current one works well enough, but it frequently breaks due to staff working on the database directly rather than with the use of secondary “helper” tables. We accept this - but when I re-build the entire thing (a massive undertaking for me since I still don’t really know all the dangerous pitfalls) we want to solve this as good as we can.

Very early on I developed a UX which allowed for people to see different things / their view mucking up the view of someone else (ie, selected project)

However, when it comes to helper tables, this becomes more complex.

It makes sense that first and foremost each user gets one row in each helper table.

However, what happens if two users select the same project info to edit. It is copied to the helper table where they can edit, but it will be the SECOND persons who hits save who’s edits actually end up being saved. The first person will save (and they’ll be copied back to the main database) but those changes won’t (cant) be seen by the second person editing.

I really don’t see any other way around this other than causing a project that is being edited to be “locked” for all other users in some sort of read-only mode.

And then run some sort of automation which unlocks / cancels editing after a time period so that someone doesn’t just leave the page after being distracted, only for that project to be un-editable.

Does this make sense? How are others approaching the multi-user case for helper tables?

Many thanks. Brendan.

2 Likes

Hi @Brendan_Woithe :slight_smile:
Question, you made a multi user doc because multiple people have to use it at the same time without interfeering with each other use of the app, and that’s fine i’ve got some experience in this, it works, and i think i got the concept of “helping tables” (you mean a table with one row per user who “create” their own view/ux right?)

Then, now you have a problem in managing the edit of one of those rows by multiple users, but, what if that row in the helper table is not associated with just one user but multiple ones? so they could edit it seamlessly, like normal coda, and then the rest should be the same?

I’m sorry if this is wrong but i’m not really sure about the “table helper” concept! If you explain it to me a little deeper i would be glad to give it another try :slight_smile:

Just replying quickly as a placeholder so when I get time later I’ll explain the needs better.

It goes to the core of what it means to have a database system like coda being used in a reasonably robust / safe way by a group of users. Ie : users never directly edit the main database, they only edit a copy of it, and all edits are logged.

Why don’t you just warn both users that they are editing the same project and that the changes may not be saved?
With a simple formula you can check that and then you can display the message in the helper table pop-up .
Maybe, you are over engineering your problem.

Oh - if its over-engineered I’d love to know…
We’ve run into problems until now because things are under-engineered :slight_smile:

Here’s our V1 doc UI

We have a helper table that saves which project each user is currently editing. That helper table contains the UI for the buttons as well. Essentially there are 8 different “pages” each showing different parts of our project management system. Its all run from a single big table plus other tables (ie, there’s budget lines that belong to a single project line, ditto deliverables, tasks etc)

I am going to rebuild to be far more robust. Too many things go wrong when users directly edit a database. There’s also no easy record for who has changed what.

Instead, when you load a project, instead of editing a row directly in the database, you copy all the info from the database to a second table and record the info on which row you are editing inside the helper table. This secondary table is where you input the info (check that everything has been input correctly even using formulas) and you then choose to save (commit the data) or cancel (which means the row in the database won’t be over-written with your changes. If you commit changes, these will also be saved to a log database (which saves all changes say over the last 60 days or so - we can choose how long to save depending on how big it gets. )

No one physically touches the main data. Its all done with secondary tables.

However, this becomes tricky when there’s more than one person using the project management system at the same time. For one, the secondary tables need to behave like the helper table for the UI. There’ll be a row in it for each person. Otherwise there’s conflicts whenever two people hit edit (no matter which project/line of the database they are working on)

My thinking is, we can have any number of people “view” a row in the database, but only one person at a time can edit a particular line (but by running the secondary table as a helper table, each person can be editing a DIFFERENT project at the same time). So we will change the UI to include a button that you need to hit to edit once you’ve loaded the project to view in the secondary table. When you hit that row, it will stop others from editing just that row.

A slightly more complex system is to have separate edit tables for each “page” of the project. I can imagine it’s shitty to need to hit edit and save for each page, and that should be just a one time thing for each project. Also, thats a lot of data flying around the place / feels more complicated than it need be. I do know that we do often work with 2 people editing a project now (say when you are on the phone to someone else who is helping with bookings / working out the project brief etc) but I think we can live with that if there is no other solution.

The overall idea should work - but what if someone opens a row to edit and forgets to save or cancel? Then that project becomes “stuck”. Potentially we put a 10 minute time limit and use an automation, although I feel like that is messy. I can’t think of any other way of doing it right now. Does anyone else have any other ideas?

Happy to hear ideas.

Hi @Brendan_Woithe :slight_smile:

For sure your project is a big one!
And when it’s like that it’s hard to find an “easy solution”, because off course in time the doc have grown and have become more and more complicated, i’ll write what i would do if i was in your place but then off course possible solutions are way more than one :slight_smile:

I think the problem is linked with the “helper table” that you use, as you said those help you in managing better the edit to the datas, so no one make mistake in the main table, that’s perfect but it create the problem you’re refferring to when more than one user is using them!

For similar problem, i had defined in my doc “groups of users”, so rows in helper tables are not “single user” (like @mario) but a group of user (like @mario @Brendan_Woithe etc), and when you filter them instead of user()=xyz you say “user().contains(xyz)”

This would allow you to have 2 user that have the same ui

Now, those updates or edits have to be sent back to the original table, off course with a button, so the pathway is

Edit button
(edit are made here)
Save button

If you have 2 users that have to modify it i would say that the pathway is the same:

User1 press “edit” (the row is loaded in the helper table)
User1 and User2 make their edits
User1 Press the button “upload the edit in the table” or “save”

Like that, just User1 is able to re-press the button to save it, instead User2 is just gonna make his edit but without the ability to save it!

Then, i would add another button that is “cancel edit” that is gonna delete the edits (i think you got this one, but this idea is just half of it!)
Then, this button check before being pressable if you actually have made any edit to the row, how? by making a second “temp” copy of the original row, and with a check like “is column1 = originalcolumn1content”? if all are true no edit have been done and the button is unavailable

This just to give the edit option more context, so users could be more familiar with the concept of “you’re not editing nothing, you’re just working on a something to send later” :slight_smile:

I’ve lied btw, i wouldnt do it like that, way over engineered sorry :joy: :heart:
I would prefer to stick to coda “normal” usage, in the past my “over engineered” work broke in some coda updates, so from that moment i prefer to stay closer to what coda is as now, so in this case it would mean to not have helper table :slight_smile:

P.s. i use also the helper table concept in many occasion, but in scenarios like that it would seems a little “too much work” for me…

Probably i would have solved with locking and publishing mode…

ah thanks for this @Mario

Its interesting - but we have used locking and publishing for the last 18 months, and I would say there are issues that come along with the data that are pretty important at least once a month. There’s also no audit trail, and we’ve got to the position where some of our projects require it for proper management. Finally, when freelancers come in, the system needs to be robust enough that they can’t delete data but can edit certain data etc. This really is only doable with our own helper tables for each user.

(btw : I really like your hybrid helper table approach, but I don’t think it would work. Since we have 8 pages for the one project, if you are sharing a row with another user they would see the same page UI that you are on. Users need to be able to be on their own page! )

My current thinking is simply locking a project to edit for others, and setting a time limit for an edit. I don’t see any other way. But perhaps I didn’t understand your idea properly.

Re coda updates breaking things - I see that as just part of the cost of using coda - but I do know what you mean.

I’m not going to get to things this week - but I might try mock something up early next week to better demonstrate what I’m talking about.

1 Like

i see your points!
A possible approach could be that each user have a “edit project” page and a “help edit project” page, the second one is activated when user1 desire user2 to help him, this could be using a dropdown like “share this edit with” and then the users or there could be a button “share this edit with” that then ask for who you want to share with

Like this user2 can help user1 to edit his project but user2 can also work on his own edits, just in another page

Then, if your doc design move pages for each user that sees that project (i mean something like the ui change for each of the 8 pages of the project so both users would be affected by this), this can be addressed using another helper table who keep track of in which page is each user, or something similar

I mean, the concept is that the user that start the edit have to finish the edit, but what if 2 users edit it in the same time, so both of them create a copy in the helper table, the problem is not here, it become a problem when they want to save it 'cause one is gonna overwrite the other, so maybe looking at the deal like this you could save both of them but marking them as “possible duplicate” (i imagine a red line as conditional format on both of them)

Like this to fix the situation a manual control is needed but no central db issues should be present

Also, you could see which column or data actually changed and point it out in a sort of “duplicate errors manager” that could be another page

Like this they do not actually work together on the same edit but the problem is managed ( :slight_smile: /:frowning: )

For what regard the first option, so the user2 that is also able to edit but not to save it, using another project page, so he can keep editing others projects, i think it is the more straightforward if you care about multi user editing at the same time (if not your lock/unlock and time limit make sense!) it could be implemented also with the notification system like “Brendan invited you to edit project x with him” so it’s clear that user2 is just a limited editor of the project

Another last minute thought could be to implement a “in line waiting” for editing a project, so if someone else is editing it but someone else wants to edit it, the first person will receive a notification asking to save it 'cause xyz desire to edit it, this locking it in the meantime…

Those are my best guess as now but i’m actually super curious about the final implementation! :smiley:

1 Like

Yeah - am thinking about it a lot.

We are going to simplify some parts of our full coda system - pulling some of the more specialised parts into other online services.

Given the issues around calendars (and so interesting that one of the top 10 posts by active users in mine a year or two ago about calendar shotcomings) we are going to do all our bookings and invoicing from a system called foxoms. Its not perfect by any stretch, but the next step up is something called Media Pulse which is $1k a month ish - even for our tiny business. Specialised database services in the media industry don’t come cheap. (There’s farmers wife as well, but its not exactly a system for 2022!!!)

Anyway - going thru documentation today on foxoms, I saw the following, which kinda relates to some of the stuff I’m saying. I think its just inherent in databases of certain designs. (There’s obviously ways around it, but it is very complex)

1 Like

This topic was automatically closed 90 days after the last reply. New replies are no longer allowed.