considering SQL database hosted in cloud...wh...

Sansao_Negao
Participant II

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!

1 30 1,471
30 REPLIES 30

RezaRaoofi
Participant V

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.

Sansao_Negao
Participant II

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.

RezaRaoofi
Participant V

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.

Sansao_Negao
Participant II

@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?!

RezaRaoofi
Participant V

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.

RezaRaoofi
Participant V

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.

Sansao_Negao
Participant II

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???

RezaRaoofi
Participant V

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

Sansao_Negao
Participant II

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

Sansao_Negao
Participant II

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

RezaRaoofi
Participant V

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.

Sansao_Negao
Participant II

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.

Sansao_Negao
Participant II

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.

Sansao_Negao
Participant II

Sansao_Negao
Participant II

+Praveen Seshadri

MultiTech
Participant V

@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.

Sansao_Negao
Participant II

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.

RezaRaoofi
Participant V

@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.

MultiTech
Participant V

@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.

RezaRaoofi
Participant V

@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!

MultiTech
Participant V

@RezaRaoofi Thanks!

You’re too kind sir. (^_^)

RezaRaoofi
Participant V

@Sansao_Negao Then I totally recommend using Security Filters for those 100,000 row tables based on recent date or something that makes sense.

Sansao_Negao
Participant II

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

Sansao_Negao
Participant II

@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)

RezaRaoofi
Participant V

I would go with separate tables with their key (tinyint) being saved as Ref.

RezaRaoofi
Participant V

Have you considered security filter and still getting 5 seconds delay difference?

Sansao_Negao
Participant II

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)

Sansao_Negao
Participant II

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

JPAlpano
Participant V

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.

Top Labels in this Space