Enum x database table ( advanced discussion) ...

[jader_james]

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

[jader_james]

+Steve Coile @Bellave_Jayaram @Stephen_Mattison

[jader_james]

@MultiTech_Visions @LeventK

[jader_james]

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.

[Steven_Coile]

Depends entirely on what you’re trying to do; there is no “best” answer.

[MultiTech]

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.

[Levent_KULACOGL]

@jader_james

Concur with +Steve Coile

It totally depends on what you’re trying to achieve

[jader_james]

@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

[jader_james]

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

[jader_james]

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!

[jader_james]

1 ROW (traffic road table) using ENUMS in appsheet and STRINGS in database: (each * is a column)

• ‘1834710214480242400’, (ID) * ‘1834709439555065300’,

(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’,

• ‘era dia e a iluminação solar estava prejudicada pelas condições climáticas’, (ENUM) * ‘com chuvas isoladas’, (ENUM) * ‘polidas’,

(ENUM)

… 1 ROW (traffic road table) using ENUMS/REFS in appsheet and INT in database: (each * is a column)

• ‘1834209975705773600’, (ID) ‘1834209282531647700’, (FK) * ‘8’, (FOR ALL ENUM OF TYPE REF, A WILL PLACE ENUM) * ‘2’,

(ENUM) * ‘2’,

(ENUM) * 1,2,5

(ENUMLIST) * ‘4’,

(ENUM) * ‘3’,

(ENUM) * ‘9’,

(ENUM) * ‘2’,

(ENUM) * ‘3’,

(ENUM) * ‘4’,

(ENUM) * ‘3’

(ENUM)

Translate

[MultiTech]

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