Hi all, in this question I'm trying to understand - if there is any use-case for splitting up big entities into smaller ‘sub’ entities and keeping field/column numbers down in a table, specifically in Xano.
Context
Recently I’ve been immersing myself in learning as much as I can about database design and best practices. Along the way, I came across what seems to be a Bubble specific concept, brought up a few times in the old Xano forum called ‘satellite data’.
For those who don’t know, it's just the splitting up of an entity into two ‘sub’ tables that have different ‘data weights’. One of the tables is a ‘satellite data’ table, which is a ‘lightweight’ table in terms of data, just containing the columns/fields that you’d want to have displayed or accessible, in objects listed in a search.
For example the entity ‘Blog’ could be split into ‘Blog’ and ‘Blog details’ where the ‘Blog’ table only contains a few necessary fields for a blog search result like - blog title, date, author etc. The rest of the blog’s data points such as ‘content’ are stored in the ‘Blog details’ table, which can be ‘data heavy’ - so having many columns and/or a large amount of data for each cell. The two tables are linked with a table relationship so one blog object corresponds to a blog details object.
The idea is that you get better performance on the frontend when displaying searches, since the objects that are sent to the frontend/users browser and rendered are quite ‘light’. When you click on one of the ‘light’ objects listed in the search and transition to a new page or screen, the related and corresponding ‘full’, ‘data-heavy’ object is loaded - so you’re only loading one 'data-heavy’ object at a given time instead of a whole list of them. I believe this is a thing because Bubble doesn't give you the option to specify the fields you want to include in the objects you send from the database.
Best explanation on ‘satellite data’ here (Fast Database. Cheat code for your Bubble app performance: Interview with Petter Amlie.) - (from 46:09 - 49:06 mins and 50:38 - 53:35 mins)
My question
Am I correct in thinking that, Xano allows you to define the fields that are included in objects you send over to the frontend - meaning that in Xano, one wouldn’t need a ‘satellite data’ table?
Another use-case of the ‘satellite data’ table is to have faster table searches for an entity (since the table is ‘data-light’), but again, in Xano I’m aware that you can create indexes / index specific columns - which as far as I know, achieves the same goal of fast performant table searches and achieves it much better right?
So the ultimate question is, bearing in mind all of what I’ve said - is it ok to just have one database table for an entity, with a lot of columns and ‘data-heavy’, since with Xano I have control over the scope of fields included in objects sent from the database to the frontend, and indexes will allow me to achieve fast searches in database tables?
Does it really matter how expansive and ‘data-heavy’ a table is when it comes to Xano?
In this video from Backendless it was said that you should try to keep table column count quite low. (🔴 Building A Scalable #No-Code Database From The Ground Up with #Backendless - (From 25:30 - 28:30 mins and 34:30 - 35 mins)
But again, keeping the column count low is talked about in the context of sending all the data an object has when you send it somewhere. If in Xano you have fine control over this, is this point still relevant?
What is the best practice thing to do here?
(And this is in the context of a normalised database, so I'm aware that in satisfying the third normal form - all the columns/fields of a table must be dependent on the tables primary key and nothing but the primary key, so of course any fields that don’t satisfy this wouldn’t be included in the table, but a table could still have a lot of fields/columns regardless.)
Thanks, and again apologies for the long post, its because if I'm vague with my question I probably wont get the answer I'm looking for.