Which database reads from/writes to table without user modification?

47 views
Skip to first unread message

Aster

unread,
May 12, 2019, 8:04:21 AM5/12/19
to MIT App Inventor Forum
Hi,
Which database is recommended for an app that will be used by different groups, the number of members in each group might be between 2-1000+? Each group would need to set up a multi-column member list that the app would read member information (such as name, address, phone#, etc.) and store member activity (such as time of request, location, etc.)  in multiple columns of another table. Thus, the app would read from the members database and write to an activity database.

The option I tried was Google Sheets because the activity stored needs multiple columns. However, the problem with Google Sheets is that code needs to be modified with the URL of the sheet for each group. I don't think that the average user would want to do this. 

Is there a database that does not require code modification that 
1. reads in member information in a table that the user sets up and
2. stores the members' activity in another table?

Thank you for any help.





TimAI2

unread,
May 12, 2019, 1:07:23 PM5/12/19
to MIT App Inventor Forum
"code needs to be modified with the URL of the sheet for each group"

why is this an issue? you should be able to program this in with blocks and a google apps script web app or some on sheet scripting

ABG

unread,
May 12, 2019, 5:46:40 PM5/12/19
to MIT App Inventor Forum
It's important to set up your data base design before you start coding.

You will need several tables ...

Users
  • unique user ID (email?) (primary key)
  • login secret or password hash
  • name, 
  • address, 
  • phone#, 
  • etc.
Groups
  • unique group name/ID (primary key)
  • Group owner user ID (foreign key)
Group memberships

  • user ID / Group ID (primary key, 2 foreign keys)
  • role
activity
  • datetimestamp (primary key)
  • user ID (foreign key)
  • group ID (foreign key)
  • location
  • activity 
 
 AI2 doesn't support many relational tables.

You will probably need something server based.

You get what you pay for,
AI2 spinoffs and extensions have better (less JavaScript) data base support.

See the Forums and AI2 Versions sections of FAQ

ABG

Aster

unread,
May 14, 2019, 8:50:06 AM5/14/19
to MIT App Inventor Forum
Thank you, ABG for your detailed and thoughtful response. Which AI2spinoff do you recommend for my program?

TimAI2: Thank you also for your reply. I realize that the programmer can code in the URL into the blocks and get the columns there. I watched I watched Allison John's youtube videos. While the programmer can follow Allison's directions, I do not think the user will want to do this. I think that the user will be able to make a Members Google Sheet but to modify the AI2 blocks so the sheets can be read from/written to might be difficult. 

A question for both ABG and TimAI2:
For my program, I would like the user to make a Google Sheets Members file that AI2 can read in without the user modifying the blocks. Then, I would like the program to make a Google Sheets file to write to. Is this possible without the user modifying the AI2 blocks? And if this is possible, will you please either walk me through the steps or point me to a resource?

If this is not possible with google sheets, do you have a recommendation for what I might use instead? (such as Firebase? something else?)

Thank you for your help. I hope I am being more clear about what I am trying to do.









ABG

unread,
May 14, 2019, 12:38:46 PM5/14/19
to MIT App Inventor Forum
For those looking for the Allison John's youtube videos, here's one of them:


Some parts of your request are against good app development practice.

 I would like the user to make a Google Sheets Members file 

How savvy are your users?  Even as a developer, I find the 
procedure to hook a Google Sheet into an AI2 app a form of surgery,
reaching into the guts of some html to grab an identifier.
No user should have to do that.

Normal practice is for the developer to set up just one data base (set of tables) for all users,
and for each user to get new rows in the appropriate tables as needed.

 
without the user modifying the blocks

Apps NEVER should allow users to modify their blocks.
If you don't believe me, walk into your local bank branch
with a screw driver and open up one of their ATM machines.


 I would like the program to make a Google Sheets file to write to.

This is breaking new ground, as far as I've seen.
Are you asking for this to avoid having to pay for online storage?
Or would it be enough to filter and extract data to a csv file on the phone?


  Which AI2 spinoff do you recommend for my program?

 That depends on your funding, your expected app lifetime,
and your level of skill.  

ABG

TimAI2

unread,
May 14, 2019, 12:39:55 PM5/14/19
to MIT App Inventor Forum

Aster

unread,
May 15, 2019, 8:47:06 AM5/15/19
to MIT App Inventor Forum
Hello ABG and TimAI2,

Some parts of your request are against good app development practice.

 I would like the user to make a Google Sheets Members file 

How savvy are your users?  Even as a developer, I find the 
procedure to hook a Google Sheet into an AI2 app a form of surgery,
reaching into the guts of some html to grab an identifier.
No user should have to do that.


That is what I am saying! I do not think that a normal user would want to or be able to hook up a Google Sheet into an AI2 app.
That is why I am asking if there is a way to automate the process of hooking up a Google Sheet to an AI2 app OR is there 
another spreadsheet/table/database I can use that would be easier for the user to set up.

 

Normal practice is for the developer to set up just one data base (set of tables) for all users,
and for each user to get new rows in the appropriate tables as needed.

I did not know this! Thanks for letting me know.


 I would like the program to make a Google Sheets file to write to.

This is breaking new ground, as far as I've seen.
Are you asking for this to avoid having to pay for online storage?
Or would it be enough to filter and extract data to a csv file on the phone?


I am asking because I know about Google Sheets but do not know about for-fee online storage. Which for-fee
online storage would you recommend?

It is enough to extract data to a csv file but the data cannot reside on the phone because it would need to be accessed
by each organization. I thought each organization would have their own files but it is too difficult with Google Sheets
because of the way the URL needs to be parsed and added to the blocks.

If instead of Google Sheets, the member data was stored as a csv file, where would you recommend that the csv file be
stored? CloudDB? 



  Which AI2 spinoff do you recommend for my program?

 That depends on your funding, your expected app lifetime,
and your level of skill.  


Would you mind just throwing out some names and I will research them?


TimAI2: Thank you for the links. I will read them.

 

TimAI2

unread,
May 15, 2019, 9:47:41 AM5/15/19
to MIT App Inventor Forum
"it is too difficult with Google Sheets because of the way the URL needs to be parsed and added to the blocks"

???

ABG

unread,
May 15, 2019, 2:44:53 PM5/15/19
to MIT App Inventor Forum
I suggest checking out Thunkable support for AirTable at
and getting a free account at airtable.com to let you
play with their different sample data base designs.

AppyBuilder also supports Airtable:

However, AirTable is a freemium service.

Also, do a web search for 'Introduction to SQL'
and 'Introduction to Data Base Design'
to get a feel for multi-table design.
I have no data on commercial MySQL host providers,
but there are extensions that let you use MySQL.

If you do your app right,
users can't see your choice of database,
can't see other users' data,
and can only access their data under intermediary action of your app.

ABG
Reply all
Reply to author
Forward
0 new messages