Hi,
(I asked a close question about this topic in the past, but given the evolutions of Glide, I re-ask it).
I am building a self-diagnosis tool where people can assess themselves per Task.
These Tasks are setup with the following structure: Category A > Sub Category Aa > Task Aa1.
There are a dozen Categories, a hundred Sub Categories and a thousand Tasks.
The assessment will be something like ‘high, medium, low’ + ‘comments’.
Constraints (remembering some questions of @Jeff_Hager on this topic) :
- Users must be able to come back on their assessment and modify it,
- I should be able to make analysis on the whole set of users input (ex. comparaison to the average),
- Multiple users should be able to performer the dizg osis at the same time,
- I can use form or customer form,
- I can have all questions on 1 page or not
Database structure :
Given the number of Task my view (without your expertise in such structuration) is to have 1 Task per Row: easier to input, to maintain, to assign calcilations etc.
This means 1 row per User per Task.
Drawback: rows consumption.
Do you have an alternative view on how to structure this database ?
A y other good pièces of advice ?
Many thanks in advance
Hello there 
10x100x1000 is 1 million tasks, 1 million rows. Multiplied by the number of users. Pre-Big Tables, I would not have liked those numbers. Now, I would think this might be a good use case for Big Tables and I’d go there with caution (I have zero experience with them). Big Tables have limitations, so already I feel like your app would be limited either in scaling or features (due to the limitations of Big Table), and this by design.
This sounds like you are going to need to do rollups on a sizeable data set. You can do rollups in a Big Table along basic column (not computed columns).
- I would go down the route of Big Tables.
- Category + Sub-categories. If these have the same attributes (basic columns), I would keep them in one table with parent and child columns. If the attributes are different (things in things), I would have a categories table and a separate sub-categories table. Then probably a separate tasks table. Tables would be connected with relations.
You could make sure you understand the limitations of Big Tables if you go down that route.
1 Like
Many thanks @nathanaelb,
the point that you mentionned on “category + sub category” is a question that I often have.
I basically always create 2 different tables with a relation link, because I don’t know when it is unnecessary. cf. your “things in things”.
Best
That’s a great point. I’m not a programmer by trade, programmers probably have clear criteria to determine when to have one single table for items, and when to have different tables.
The way I see it practically in Glide:
-
If items (or objects) are related to each other and they are clearly the same object and there would be subcategories, then one table. Basically I could categorize the objects as a tree. Example: Employees at a company. They are all people, I can create a tree structure to show hierarchy, this hierarchy is built with parent and child columns. Other examples: Tasks. Products. Supplies.
-
If items or objects are related to each other and they are clearly not the same object and there would be groups of objects, then multiple tables. I think the visual structure is more like a network or fractal (not a neat tree). Example: Employees and their locations. Clearly, a person is not the same as a location. In theory, you could define the same attributes (a name, description, image, etc.), put both people and locations in the same table and say they belong in the same table because they have the same attributes (a name, description, image, etc.), but really, they are not the same.
This viewpoint is flimsy, because it’s obvious for people and locations, but it could be less obvious for other objects. There’s probably a better explanation as to why people and locations are actually not the same 
3 Likes
Good point of view!
Thanks
Best
1 Like