TinyDB format (Which one is better)?

General Discussion
1

TinyDB only:
Conditions:

  1. Multi-spreadsheets (e.g. Data1, Data2, Date3……).
  2. Daily record on all multi-spreadsheets.
  3. Assume ten-thousands record (read, write new, update existing and delete existing record).
    I uploaded a photo to display two types of format. I think using SPACE PIPE SPACE “ | “ as a separator because I think Pipe | is rare to be used for daily recording.
    For myself,
    • Format 1 is good for update and delete existing record.
    • Format 2 is good for extract information. Usually Item is likely as a fixed List. I could use “For each loop” and store them (Each item as their own list) into memory. Each row of data corresponding to the same index. I feel this is easier, using less memory and faster for coding.

What do you think? Which one is better for keeping record? Is it better to use multi TinyDB (Each TinyDB for one spreadsheet)?

TinyDB_Format
TinyDB_Format1123×794 29.3 KB

2

For other user who is interesting TinyDB topic, I summarised few excellent suggestions from few PowerUsers (Special thanks to SteveJG, Taifun, ABG and TimAI2)

Multi-TinyDB: Quoted from Taifun

yes, you can use the TinyDBX Extension:
https://groups.google.com/d/msg/mitappinventortest/7n0CPHIfZSc/BoebXhvYCAAJ

TinyDB Example (Tree directory to keep record and use procedure for repeating similar action): Quoted from ABG

if you want to go the TinyDB route, here’s a sample app that builds up complex tags to store different kinds of data structures …

If you are accumulating daily data and you might want to tabulate and summarize it, SQLite is better for that.

3

SQL extensions: quoted from SteveJG

If you decide upon using sqlite you might consider these capable (free) extensions below.
This one works great for me


and here is another one

4

Think about all the processing you are going to have to do just to get one spreadsheet into a tinydb
Then you will need a list of all the tags to find the data you want

I would try

  • Get data from spreadsheet
  • Make a list of lists (table) with it
  • Save that to one tag in tinydb
  • Call back the data when you need it to a list of lists
  • Work with that data
  • Save back to the one tag
5

It depends on how you want to query the data after it has built up for a while.

Do you want to summarize, filter and sort it?
Do you want to accumulate the data across app users?

6

there is another paid one, which is my sqlite extension here App Inventor Extensions: SQlite | Pura Vida Apps

probably interesting to read:

why you might want to consider buying rather than taking the "free lunch"

  1. my extension is available since August 2016 and is therefore very stable
  2. it follows the KISS principle - Keep it simple, stupid
    KISS principle - Wikipedia
  3. I'm offering support in case there are issues and provide updates and bugfixes

Taifun

Trying to push the limits! Snippets, Tutorials and Extensions from Pura Vida Apps by Taifun.

1 Like
7

Now would be a good time to search the web for articles on Data Normalization.

8

Thanks for comments.

Thanks Taifun. I agreed your comments. There is no free lunch in the world. When we want to get something free, we need to pay certain amount of money. We may paid more than what we get for free. Paid can be money, time, energy.

Thanks ABG. I did an hour google and reading for Data Normalization. It is essential when over ten tables. This is another topic I need to spend time to read in the future.

I got an experience. Some apps like Expense Manager. My data has more than 5 years record. Crashed happens all the time when I keep put few new data and amend data (like correct the wrong input amount and upload it back). I need to login again and amend data. That is why I would like to try to create a simple app.

Anyway, my topic is focuing on TinyDB. How to use TinyDB to create better result.

Format 1 will create lots of Tags and keep adding tag because each tag represents a row of data. Each tag has fixed number of items/ lists. In this example 6 items.

Format 2 will create fixed number of Tags and keeping adding values. In this example, there are 6 tags only.

Which one is better for overall usage? Keep adding new data, search existing data, amend and update the existing data and delete data.

9

When you have that many years of data, you can get a performance boost by partitioning your data by year into separate sections (SQL tables/databases or TinyDB NameSpaces.)
Updating a small data collection needs less resources than updating a large one.