I am building a website which is a survey, with around 15 question on each page. I store all my questions, my answers (every answer for each question (which are dropdowns)), and a question_answer table which associates questions with answers.
Here’s a look at my tables :
Question :
Only an id, and a label for the question.
Answer :
Question_answer :
After completing a page, the user has to click on a ‘Next page’ button, which sends the data to my db using the Supabase Update method in a workflow, then navigates to the next page.
The problem is that I MUST use individual UPDATES for each row (every question/answer association in Question_answer table is created with a trigger when a new project is created), however it takes some time before the 15 rows get updated, and my redirection is triggered, which is not great for UX, I don’t want the user to wait 3 seconds before seeing anything changing on the screen.
Do you have any idea how I can send those requests after/while being redirected ?
Should I change my whole architecture to upsert the rows instead of updating them individually ?
Is there a way to update many rows all at once (using the rest api of supabase) ?
I don’t understand your workflow but I’m pretty sure you’re doing it wrong. I need more info in order to be able to answer you.
Thanks for your answer, here are some images to understand what I’m trying to do
Image: general explanation of what I’m trying to do
Image: the workflow that is triggered on click on the NEXT button
Instead of having only 6 questions on a page, I have at least double the amount, having at least 12 to 15 questions on each page. Each update action taking 0.1s, before the next is triggered.
Images: how my update actions need to be configured
The table being projet_question_reponse (referred as question_answer in my question), id_question, which I have to set manually for each question, and id_reponse (referred as id_answer in my question) set to the value of my select element
As of right now, the workflow works as expected, all the rows are filled perfectly, the only issue being that it takes more than 1 second to update all the rows, THEN to redirect to the next page.
Note : I would love to upload a video here, to show the delay but I can’t figure how.
Hi, I think you have a few things wrong as I expected.
It all boils down to probably what is a wrong database design. What I’d probably do, is for each form have answers be in a table called question_answer where all the available answers would reside. You don’t need to pre-fill them. It would be something along the lines of this.
This way, when you do a LEFT join, you’ll get all of the questions, with their answers or a NULL as an answer, meaning that there is no answer. This way you eliminate the need for pre-filling the questions.
SELECT id, text, qa.text as answer FROM question q
-- LEFT join is important here
LEFT JOIN question_answer qa ON qa.question_id = q.id
WHERE user_id = 33;
the result will look something like this, where the record number 4 has no answer in the question_answer table, and thus it has a NULL in place of the answer:
As for the bulk updates, I really think you should either consider creating a stored SQL procedure and use that to bulk update all of the rows at once, or at least use UPSERTs. Your approach right now is very bad - it causes a lot of latency and is based on a fixed number of answers, which will become unsustainable in the long run. With an UPSERT, or a stored procedure, this should take under 500 ms to happen for however many answers.
I think I addressed both of your issues, now all you have to do is deep dive into what needs to be done, study how it’s supposed to be done and do it. I’m hoping helped at least a little. If you’d need some even more in depth help, I also do 1:1 coaching sessions for exactly founders and builders like you who wanna ste up their knowledge to the next level and or solve riddles like these.
Thank you, I refactored my db, and started using a Supabase function to make all the updates from the db , works fine and no more delay 
1 Like
