Enum x database table ( advanced discussion)
*Enum (with items in a enum type) Is it more fast for sync time?
Is it more expensive for database size?
*Table (in database with id and description) Is it less fast for sync time? To have a table in this appsheet framework give us a big overhead for create the table in app, create the ux views, references, virtual columns etc?
Is it so better for database size? โฆ I was thinkingโฆif I to choice enums and I have 30 enum columns with variable size columns (any with a little size and others with 200 characters per example), to persist this like a enum will store 200 bytes for each insertion in that enum I talkedโฆbut with database table only 1 byte for reference the id column โฆ Third option is to use enum in appsheet and enum in MySQL like the type of column, then I would have a good performance and good practice in database
All of this depends of the overhead that add tables in framework, considering there are a lot of tables in this situation (30 tables can be modeled like enums, any short and others long text)
And for generate .pdf: there are a lot of forms to catch data values: [column], [a].[b], lookup(โฆ), So if a to use databasE table I will need to fetch data values in a formula, if I use enum with or without enum in MySQL, the select always will return the value of the enum (โblueโ instead โ1โ)โฆand one time more this can affect the performance
+Steve Coile @Bellave_Jayaram @Stephen_Mattison
Yes, of course๐, if I was in other platform I donโt would consider any use of pure enum instead lookup database tableโฆthere is good practices adopted by dbas and one of it is to use lookup tables instead enumsโฆhere I am in doubt because the speed of sync change in seconds ( now with enums( type ref) the sync time is 25 secondsโฆand with enum(text) the sync time is 15 secondsโฆthen the difference is decreasingโฆI hope to otimizate more a bit.
I am decided to use lookup tables until the performance show me that I am totally wrong, because I will prevent redundancy, prevent the fast increase of the data, anomalies and have maintenance betterโฆ
Too I think that the data have a timelife, when in the next weeks I will visit the same data any times and generate a PDFโฆbut in months from here these data donโt will be more important in ordinary casesโฆso I can focus on decrease the database size and consume more resources to generate pdf and views in appโฆI am of the rdbms era, and so Im not familiar with nosql or others string based persistency, but I can hear about other options
I hope that appsheet can otimize the use of the read only tables, and include one field for table in in enum of type ref etc.
Depends entirely on what youโre trying to do; there is no โbestโ answer.
As Steve said, it really depends on what youโre trying to do.
As a point of reference on large database tables, consider this.
Iโve got an app for tracking patient data, and at the moment weโre sitting at about 15,000 records in each data point table; these would be things like patient weight data, lab results, etc.
Currently weโre sitting at around 1,500 patients records - so when the app loads the device loading all of this data (which we reduce down as much as possible through security filters and SQL views) the app has to organize which weights go to which patient, and the same for each other table.
So for patient one, it looks through the 15,000 records to find their weights; then through the 15,000 for their labs; so on and so forth.
When loading the entire database, with all data points and all data sets, load time sits around 30 seconds.
Thatโs the time that the app is sitting there โLoadingโ when it first opens - sorting and organizing all the data.
This, I must point out, is an extreme case.
When loading only a single facilityโs data set, load time sits around anything normal for an app with some data. 5-10 seconds.
Also, load time is dependent on the device doing the loading.
My Google Pixel loads things slightly slower than my work PC, which works much faster than the Dell desktops the nurses and doctors are using in hospitals. (^_^)
So it depends on what youโre trying to do, what youโre trying to build.
If you can give us some insight as to your scenario or use case, then we might better steer you in the right direction.
@MultiTech_Visions the app is for crime scene, so i have a lot of โstatesโ of the things to register:
table victim: -type: physic or juridic -gender M or F -time since death: 1 to 2 hours, 2 to 3 hours, โฆ -position: on the floor, on the โฆ, on the โฆ, โฆ -attributes: low haired, โฆ, โฆ (enumlist) -effects: burned, โฆ, โฆ, (enumlist)
table road of transit (where stay the more big enums) interaction type: by side, in front, โฆ via type: โ100 characters textโ, โ100 characters textโ, โฆ defects of the road: broked, โฆ , โฆ, (enumlist) โฆ โฆ โฆ โฆ(MUCH MORE COLUMNS AND MUCH MORE TABLES, AND SOME OF THEM ARE REALLY BIG, 100 characters) โฆ โฆ โฆ
there are more less 10 secundary tables associated with a EVENT, depending of the event type the user will fill maybe 5 tables of these, depending if is a traffic accident, murder (fill the relateds victims associated), etc
the values of these columns will be almost stable, maybe one or other correction, but i dont need to add more option on all the time, only corrections and add when someone report that this is necessary
to maintenance is much better save these values like tables in mysql, but the appsheet will create tables and unnecessary overheads for its
and in same time i dons want to increase the database size without need, saving redundant values and increasing the size oh data sended from database to app
if the DELTA SYNC works with mysql, maybe the overhead was decreased because the phone of the client already have the domain data that is read only, and dont would need to fetch and compare a lot of timesโฆ
and other question, o change my REFS to ENUM(REF) to remove the related virtual columns auto generated, but i dont know if i will loss performance by dont can more to do [a].[b] to generate reports, i will need to make: LOOKUP(LOOKUP(โฆ from now on
Each criminal expert only will see the data generated by himself, using security filters, but same with a almost zeroed database the sync time
turn me worryโฆI already see it in 1 minute, 40 seconds and now is at 20 secondsโฆI can use from 40 to 70 tablesโฆand this make difference in performance analyserโฆfrom 1 minute to 20 second, eliminating 30 tables, eliminating the refs, etcโฆ
But I can see that the maintenance of the app are so bad with enums and I need to fill enums with my own hands, manuallyโฆ
And too my database was desnormalized, with a lot of strings generated by enums and before I haved only number for reference, the database was more clean and more fit!
1 ROW (traffic road table) using ENUMS in appsheet and STRINGS in database: (each * is a column)
(FK) * โsemi-frontalโ, (ENUM) * โArterial (Normalmente liga regiรตes e bairros da cidade, com interseรงรตes em nรญvel (cruzamentos), presenรงa de semรกforos e faixas de pedestres, acesso aos lotes lindeiros e com a velocidade mรกxima de 60Km/h e a mรญnima de 30 Km/h)โ,
(ENUM - big text) * โestrada de terraโ,
(ENUM) * โvala(s) , recalque(s)โ, (ENUMLIST) * โdesnivel com traรงado retilรญneoโ,
โ* Possui sinalizaรงรฃo horizontal, com marcas longitudinais (linhas), transversais, de canalizaรงรฃo, delimitaรงรฃo e controle de parada e/ou estacionamento; e vertical, atravรฉs de placas de advertรชncia, sinalizaรงรฃo e indicaรงรฃoโ,
(ENUM - big text) * โlogradouro simples com faixas de trรกfego em sentido รบnico, sem demarcaรงรฃo, com ausรชncia de linhas longitudinais seccionadas, que regulassem as ultrapassagens, mudanรงas de faixa e o cruzamento a partir de outras viasโ(ENUM - big text) * , โhaviam restriรงรตes ambientais a visibilidadeโ,
(ENUM)
โฆ 1 ROW (traffic road table) using ENUMS/REFS in appsheet and INT in database: (each * is a column)
(ENUM) * โ2โ,
(ENUM) * 1,2,5
(ENUMLIST) * โ4โ,
(ENUM) * โ3โ,
(ENUM) * โ9โ,
(ENUM) * โ2โ,
(ENUM) * โ3โ,
(ENUM) * โ4โ,
(ENUM) * โ3โ
(ENUM)
Translate
Full marks for detail.
From the look of it, it seems like the data is lending itself to a parent/child data structure of using REFS.
There is another strategy you could use, a hybrid of the two.
First you talk about using an ENUM column - and entering the items for each enum manually.
Second you talk about using additional tables and REFs - thus reducing the amount of update work youโll have to do.
If you use the valid_if or Suggested Values features available, you can use your other tables (giving your users the ability to modify their own lists) but doesnโt generate the ref-link between the tables (a source of slow down later with big data).
help.appsheet.com - Dropdown from Valid_If Dropdown from Valid_If help.appsheet.com
User | Count |
---|---|
43 | |
27 | |
24 | |
22 | |
13 |