Trying to figure out the best way to structure data in Glide tables on a page using many columns. The page I am trying to make contains over 300 structured individual questions that are now being split into a custom form.
And with this the question arises. What loads faster? Putting all 300 columns into one table or splitting out the questions into different tables (let’s say 30 each), and then using relations/computed columns to link between a child table and a parent table to relay information on status and completion. My instinct is to say that one table will make the computed columns faster but will take longer to load and will be more complex to maintain.
yeah … sadly not. There’s too much going on with it that requires it to be split up into separate columns. Which is causing this mighty mess of trying to figure out the best structure to bring at least a semblance of usability.
A table represents an object, so I would stick to using one table for one object. Splitting one table into multiple that really should be one single table doesn’t feel right.
Performance
I recall David saying in the forum that Glide Tables will handle as many columns as you want. From a performance perspective, I don’t think 300 columns would affect performance negatively. So perhaps you don’t need to split up you tables.
Data structure
A column represents an attribute of a record (row). In your setup, what would be a record, a user maybe? So your 300 columns in the user profile table would represent answers?
Like Darren, I wonder if you could adopt a different data structure.
In the data editor, you could try the following:
A users table with RowID. A questions table with RowID, Questions as a basic text, Answers as basic text USC, AnsweringUserID as basic text USC to house the user RowID, DateTimeSubmit as datetime USC.
You could put these columns in the Users table and avoid a relation, but personally I like keeping my Users table as streamlined as possible.
In the layout builder, a flow with 300 questions feels challenging. A few ideas:
A details screen with the Questions table as the data source. Set a filter on the screen for Question 1 as “RowID is [the RowID of question 1]”. A text entry component that writes to the Answers column. A button with a custom action that writes the data to the USCs and then shows a new screen for question 2. Repeat 300 times by changing the filter to “RowID is [the RowID of question n]” and setting the custom action to go to the next screen for the next question. This feels complex, error-prone, and difficult to maintain.
One single details screen (instead of a sequence of 300 screens), showing and hiding 300 text entry components and 300 buttons. I don’t think the performance of such a screen would be good. The visibility conditions on the components feels harder to setup that the RowID filter in 1). So this feels worse than the first idea.
Maybe the 1st idea needs to be developed a bit and the 2nd idea definitely trashed
A standard Database normalization will tell you that many tables with few columns is the right way to follow.
But, the new questions is: How many few columns are optimal? All depends on you needs and APP. In my case, when I saw that my table is growing and holding more than 25+ columns, I start checking my logic/structure out to simplify my work (maintenance and debugging in future).
But also, we live in Glide’s world and sometimes we need to save rows and create smart workarounds to survive
Each record would be a response depending on how it is structured. I appreciate your taking the time to structure this with a table/screenshot! It’s very helpful visualizing your detailed approach here. Agree that the first solution makes more sense but causes potential for error. Seems unavoidable but the reasoning makes a lot of sense.
Good point, I don’t know the Glide’s internal architecture (backend) but most cases in other software, the more columns a table has, the slower its performance. Take a look at my example using a Google Sheet as API to understand what I try to explain.
In my tests, I had 45k rows with 8 columns and its performance to reply an API request was 4-6 secs (a good number for me!). Later, when I tested using 35 columns with the same 45k rows, the replies lasted 9-12 secs (I didn’t like the number anymore )
I don’t want to imagine, how long a reply would last if the table (GS) had 100 columns or 300!!
sadly not. There’s too much going on with it that requires it to be split up into separate columns. Which is causing this mighty mess of trying to figure out the best structure to bring at least a semblance of usability.
)