considering SQL database hosted in cloudโฆwhat is the better:
Have
a lot of fields: ENUM populated with VARCHAR and save varchar in database (sync time in better because the Appsheet server fetch less tables in data source, but database size is bigger (the same VARCHAR is stored thousands times and maybe processing time is lessโฆand cloud storage is more cheaper than processing, but with enums the maintenance can be complicated to delete, update items etc)
Or use a lot of ENUM TYPE REF (filled with valid if of a TABLE) and save int in database and maintenance the database short but maybe increase processing time?
In the cloud we pay for CPUs, storage and bandwidthโฆhelp!
How many data rows and tables are you talking about that you are concerned about CPU processing time and Storage?
When it come to SQL databases I think the best approach is to go with design and business/real life model of entities and their relationships; make them a separate table with Ref column referring to them if that is really an entity in the business model where it can have its own relationship with other entities and perhaps having its own attributes (columns) down the road.
Then if you want to cut on bandwith/CPU/Sync times, find a criteria to filter down the downloaded rows to AppSheet using proper Security Filters, instead of sacrificing your normalized relational database design.
Thank for your response @RezaRaoofi, I have 20 tables can be modeled like database SQL table or like enum in Appsheet framework and 30 other dynamic tablesโฆfor that 20 tables they have 2-20 values for choiceโฆ
For dynamic tables, my main table will insert 1000 rows per year for user, with 100 userโs I will have 100.000 rows per year
So that 20 tables have relationship with only 1 or two tables ( sex, color, types, constants, and other can be change with time itโs hard to known now)โฆ
But I perceive delay with tables, maybe 5 seconds when I changed 20 tables from enum text to enum ref table.
If you are planning on having 30 to 50 tables in your app, you might need to consider breaking it into more than one app rather than trying to save text instead of Ref in those tables.
@RezaRaoofi I agree with break the app in future, but when you have enums in a app you choice a database table over Appsheet text enum, right? Only for confirme us conversation?!
In the second paragraph of my first comment, I was trying to imply that you need to first think about the main entities in your model in the business requirements; that has the higher priority and will identify the rest, rather than messing with your database model for things like sync time, and CPU usage.
Not all lists are actually an entity in your system, if they are, then you should consider a table for them. Then use Ref column for relationship between them and other tables, and of course the Key ID will be stored in the Ref column, as opposed to other text columns like name, description, etc.
Sometimes those lists are only really some status values or fixed descriptions; for example City and Country names in some tables could be easily considered Enum, but in another system the same country and city could be really an entity that has a whole bunch of attributes of their own and relationship with other entities, then in that case they have to be considered as tables, with separate column for each attribute, and you would need to analyze its relationship with other tables too.
I wouldnโt worry much about AppSheet and saving 0.5 seconds here and there, because you are now multiplying that by 30 tables x 100,000 rows, etc.
Down the road when/if your app slows down you can save much more by using security filters and breaking one app with too many tables into smaller apps, than by changing tables into Enum.
Thanks @RezaRaoofi, you are awesomeโฆwhat does me to think more about this is the cloud environment, where we can see in a lot of moments people using noSql or Denormalized tables filling the database with a lot of VARCHAR because the STORAGE is more cheaper than PROCESSING in CLOUD ( I will use Amazon RDS and Amazon S3),
in our case: we will use a lot of Lookups to renderize the app in my device and a lot of lookup to generate each report(pdf) of the userโฆhow you can see I am trying to analyze the complete environment of the Appsheet + cloud and understand the best practices in this wayโฆlike this example for use table or VARCHARโฆin a report (template pdf) or this will be
[status]
or will be
[lookup(fk_status, status, id_status, ds_status)]
And this is for ยฑ20 tables and for each report of each userโฆ understand the complexity of decision?
And if I save ints over VARCHAR maybe the traffic egress would be more little or not if Appsheet server query the same table for each sync for each user, because the data source is cached in Appsheet server only for 5 minutes, then use a table instead a VARCHAR enum can let us to query the same tables hundreds/thousands times in a day or weekโฆspending bandwidth and others metrics like memory and cpu.
Have anyone of the Appsheet can explain if the table is queried all the time and we must to use enum type VARCHAR to dont do it?! You can mark anyone of the team here @RezaRaoofi???
Yes, AppSheet does sync all tables, if they are on a cloud SQL database; those rules you read in documentation about checking to see if a table has been changed works only for cloud spreadsheets like Google Sheet, otherwise AppSheet cannot recognize when a database table inside an Amazon RDS instance has been changed or not, so it will requery all SQL tables upon each sync.
So I understand your concern about egress due to several syncs per user per device. The only controlling tool you have in your hand is security filters. You can use that along with User Settings page; once each user enters data in that page and saves, AppSheet initiates a sync that would query all tables and this way you could feed the Security Filter criteria from values entered in User Settings.
Regarding denormalization, I agree that cheap storage is encouraging it, specially for free data sources like Google Sheet everybody can save the whole text instead of a small key ID, so these days I often normalize not to save storage, but for flexibility in future updates, for example if a lookup tableโs description column changes, I wouldnโt need to update all those VARCHARS on the many side of a one-to-many relationship.
Regarding the lookup() formula you mentioned, I always try to use De-reference on Ref columns:
https://help.appsheet.com/expressions/expression-types/dereference-expressions
I definitely will improve my user settings to filter a lot of lookup tables using security filters after talk to you, thanks
Maybe I can start with normalization and denormalize over demand, when I need thisโฆ
Ref columns is not a option for lookup tables (read only almost ever), I only use it for tables that can be add on app,not for tables read only because it generate virtual columns (mandatory) and increase the sync time, see this:
plus.google.com - WOW, just shaved a whole second off of my sync times by changing a lot of my โฆ
WOW, just shaved a whole second off of my sync times by changing a lot of my โฆ plus.google.com
Because of this I started to use enum type ref for read only tables (my enums) and started to talk with you about VARCHAR x int
Yea, that link was related to a hack workaround that is undocumented and mostly promoted by the same person, I personally do not like those unorthodox methods!
I agree with the part you said: โฆMaybe I can start with normalization and denormalize over demand, when I need thisโฆ
If those lookup tables are read-only, then you could denormalize them and just use simple Enum and save text value in the table (instead of int); this way in reports you wonโt need join in SQL or using De-reference in AppSheet.
Good comments @RezaRaoofi, but I think that Appsheet donโt do joins in SQL ( when I use Lookups they convert to select(any( and execute this in memory of Appsheet server because they donโt generate foreign keys in SQL databases when
we migrate from sheets to SQL, I include fks for security.
Delay when sync ( Appsheet framework donโt manage very well the performance for tables, they are catch in data source for each sync, same if the data source donโt change) because of this I am asking for myself if i must change the tables for enum base Type Text.
@Levent_KULACOGLU @Aleksi_Alkio @MultiTech_Visions +Praveen Seshadri
+Praveen Seshadri
@Sansao_Negao @RezaRaoofi Good discussion.
Iโll add a little from my experience with large apps.
Iโve got a client thatโs a tele-health company, and theyโve got an app thatโs full of complexity and automation - and bloated with data.
My original design was to use de-refs to facilitate easy data pull, but it had an unexpected side-effect: creating a reference from one table to another creates a REF_ROWS() that cannot be deleted.
(The best you can do is change the formula you โList(โ").
But after removing the references, and using lookups instead, I was able to reduce the sync time.
This is due to the fact that when you sync your app, all the REF_ROWS() are calculated - and if youโve got 50k records thats a lot of sorting.
And if the references are just there for easy data pulling, NOT an actual entity-entity relational reference, these extra VCs are complete unnecessary and only add to the sync time.
LOOKUP() Iโve found processes extremely fast.
While a de-ref is really (I mean REALLY) helpful with simplifying your workload as an AppSheet dev, when you get a data bloated app it can be more of a hindrance than a help.
Unless the connection between two tables is an actual relational connection, I wouldnโt use a reference .
If your app is going to get large and contain a bunch of references, you could be looking at a longer loading time is all Iโm saying.
Thanks. Youโve answered my question without me having to ask the questions. Forums are great.
Very useful @MultiTech_Visions I agree with you about the virtual columnsโฆ One last questionโฆfor typic enums like sex(male,female), color (blue, โฆ), Etc
And for enumlists like theme (technology, human resources, etc)
You prefer to save in a cloud database SQL INTS (using tables for color, sex) or VARCHAR?!
Considering the normalization, the redundancy, the cloud architecture and pricing, and the Appsheet architecture of syncing.
@Sansao_Negao I have not had a chance to read through all new comments on this thread yet, but when I said de-normalize and then you would not need join in SQL, I did not mean appsheet would use SQL join; I meant generally speaking your job would be easier in the future, because you will not need to write join queries on the SQL side for reports, neither you would need De-referencing on the AppSheet side. So de-normalize where you can and when those look up tables are mostly read-only.
@RezaRaoofi Iโll admitโฆ I had to google โde-normalizeโ (^_^)
I have no formal training in database design, 100% #CitizenDeveloper here, and had a vague idea of what that term meant - but not the specifics.
hmmโฆ something to look into.
@MultiTech_Visions Well, I believe you have done a great job being โCitizen Developerโ and all! Titles donโt matter; what matters is your patience and passion for learning and teaching others which you certainly have a great deal of both!
@RezaRaoofi Thanks!
Youโre too kind sir. (^_^)
@Sansao_Negao Then I totally recommend using Security Filters for those 100,000 row tables based on recent date or something that makes sense.
And when is a enum list, save โ1, 2, 7โ or โblue, strong, resistentโ for each row in table, two are VARCHAR but one is bigger than other etc
@RezaRaoofi yes I am reading about it (security filter) only in doubt about save VARCHAR or TINYINT for each enumeration in my table (20 enumerations)
I would go with separate tables with their key (tinyint) being saved as Ref.
Have you considered security filter and still getting 5 seconds delay difference?
The security filter will only have effect when the data increase, currently I have only 10 rows because my app is a prototypeโฆbut in manager I can see the performance analyser and see that fetch data increase almost 0.5 for each tableโฆand exists threads for catch these data (example 3) then have 30 tables or have 50 tables can impact in 5 seconds because in each sync the appSheet do these steps (see the image)
Appsheet server have to check the table to see if they are modifiedโฆand this impact in each sync, understand?! My data model is good, but for Appsheet architecture I have doubts
Can we combine two tables into one vertically? (Append).
Iโm not talking about joins where I can use Ref to get values from another table.
I mean like adding the rows from another table as rows to the current table.
User | Count |
---|---|
32 | |
31 | |
30 | |
19 | |
17 |