Hello everyone! I would appreciate any help that guide me in the right direction.
Im currently working on a database project for managing base data and technical equipment of a big environmental monitoring network.
My agency is technically trapped in the early 2000s and flooded with hundrets of excel spreadsheets no one can overlook anymore. So im working on a solution to restructure and reorganising all of our important data. The goal is basically a database with some easy one-to-many relationships and a nice-to-use gui with some small dashboarding and evaluation functions, which is accessible by anyone within the corporate network.
I made a working prototype in ms access but i really have to say i hate access.
Its slow, laborious to build a working and good looking gui and overall lacks massivly in functionality of exporting and integration of external sources. I dont feel like diving into vba either. Since im pushing my careerpath into data anyway and work with R in sideprojects i had the idea to build the whole database/gui as a shiny application since R is far more flexible and modern than access and i love how easy it is to build good looking frontends with shiny.
But before i deep-dive into shiny-development i need some questions answered if this even makes sense to realize the task in Shiny.
The requirements are as follows:
- Integration of external data that is only accessible via REST-API (GET request) within the corporate network (Easy with httr)
- Multiuser capabilities
- GUI to enter, store, change and pull structured, relational data
- Selfhosted within the network
- Selfcontained databasebackend since the network and IT is heavily restricted (SQLite should work?)
- Merge external, queried data (not editable) with self stored (editable) in one frontend
- Easy export of data in multiple formats
A common dataset i need to store looks like:
- Station with some parameters (Station number, name, long/lat coordinates,...)
- Station can have multiple problems we need to track (1 to many)
- Station can have some technical equipment like dataloggers, modems... (1 to many)
- Each datalogger can have multiple sensors and a sensor can have multiple coordinates (both 1 to many)
The whole databasedesign is finished and working in access.
I read a lot but some things are still unclear to me.
- Can i somehow query a local server if shiny is hosted outside of the network but used within?
- Is shiny (with dplyr, DBI, pool) capable of managing relational data with multiple users at once - just like a access database would do?
- How much SQL do i need to know? Can i use dplyr instead to retreave, store and change data?
- Is it possible to have kind of a split-view gui like in access where i have a datatable view of all stations at the top and some details of the selected station in the bottom?
- Does all of this make sense and would you say shiny is a far better tool for this job than access?
I would really appreciate any opinion from you guys & girls!