Database with webapp for input and query
An opportunity to learn more by helping others:
A university biology professor has a project involving several students from a mixture of ~40+ universities. Over the next 3+ yrs, the students are collecting data on a single type of animal in their local areas and currently entering it into a google form, stored in a google sheet.
They would like to store the data in a database that could be setup with a few relations. It sounds like they have pretty simple needs with 4 tables and the potential for ~10,000 records of text (currently a couple hundred), ~10 fields each, and no images (not sure how much data that might estimate to be). They potentially have access to server space through the central university, but not sure how to access that yet. They do not have funds in the project's budget for monthly costs of hosting/accessing the data other than paying for a web domain to host the user interface at $50/3yrs.
It seems there are many services available to help them create a webapp using one of the no-code offerings (bubble.io, honeycode, zoho creator, etc), unfortunately their budget doesnt allow for any additional expenses to host that data.
My background is in Corporate Executive Software Sales and I have beginner level familiarity with database design and programming. I have been wanting to learn more app & database creation to 'scratch an itch' for more entrepreneurial ventures. I offered my (limited) free time to potentially help identify a path to build something with them and we will all learn along the way.
If you were to approach this situation, would you try to:
- Learn SQL and determine how to get the data available to the server on campus , build the UI and host (time intensive, greatest barrier, low to no cost)
- Find a no-code option to build the UI with integrated database that can get them started (less time, urge them to find some funding)
- Other ideas
Any thoughts?
I think the approach you described makes sense. Build some SQL tables with relationships (I like MySQL because it's everywhere and there's lots of community support).
For the web UI, let me recommend my tool -- DataSpeeder -- which will five you a fully-function web GUI right out of the box. No building needed.
You can see a video demo here: https://www.dataspeeder.com
Python and Django/Flask framework could be a choice with Postgres/MySQL for the database. Django's ORM (Object Relational Mapper) will enable accessing the database using Python classes without much SQL. you can use Django's templates to build simple html, javascript web pages (some CSS, bootstrap can help make it look better). There are many options. This is just my view to build a simple web app - may be less time consuming if you have some basic python familiarity already.
Thank you, @JohnL. Something to explore.
Its possible to build a webapp based on an existing google sheet, this tutorial may help as well:
http://tech.honestbee.com/articles/integrations/2017-02/building-web-app-using-google-sheet
If cost is a factor then the best choice is option 1 .
If you can find out what the computing resource the university can potentially provide I could comment further. What database type is it using eg mySQL, Postgres or some other open source database and what operating system is it running , probably some form of linux.
It looks like they already have some ideas about the database tables and relationships between them, has some preliminary database design taken place already ?
What you would initially need is a breakdown of all the information they want to store . It's probably the column headers of the existing google sheet which should describe the data items.
When you have this information then you can design the database tables based on the data in the google sheet. The data in the google sheet can be viewed as a single table and the task here would be to split the data into a number of tables and relationships / indexes that can be used to design and implement the database
When the university database server is available then the database can be created. You would then create the tables / indexes in the database
The next step would be to map / import the data in the google sheet into the database you created. This would involve a bit of programming to split it up and write it to the database using sql commands. This is hard to estimate without seeing the data.
After the database is up and running with data in it then you can start thinking about the webapp, its probably going to be focused on data input forms , search query forms , producing reports and maybe exporting data to CSV (Comma separated value) files for further analysis with externals tools.
If the students are granted space then they could
run the webapp and database on the same box or
the webapp and database could be on different machines.