I am in the process of creating an android app for our clients that would replace the paper-trail in their facilities with electronic inspections.
Although I have coding experience, I have little knowledge on how I would structure the data received through these electronic inspections.
We will set up electronic inspections that need to be completed by users for each client. I already have that set up. Now I need to find out how I would process the data received during these inspections.
The need for the project is as follows:
We have multiple clients all working on the same platform but with their own users (with their own profiles). Users must be able to log into their profiles offline and only get access to inspections that are specified for their profiles. Once they complete an inspection, the data needs to be saved into a local database on the tablet.
The app will have a synchronization function that synchronizes the local database into an online database.
Now what I am unsure about is how I would structure the tables for these databases. Since every "Inspection Type" has it's own set of questions, putting them all in one database table will mean I would have to add a column for each question? Is it better to then create a table for every "Inspection Type"? Since we will be adding, removing Inspection Types in the future I am unsure if this is a good approach? Especially since I also need to write a web application that would be used to retrieve the data (via Excel format downloads as well as live dashboards on the client's data).
Also, we will have a lot of clients on the same platform, so should I keep their data separate by using a database for each client? I read that it makes maintaining the database a lot harder.
Another worry I have is that within a client, when a user completes an Inspection on one tablet and synchronizes it with the online database, the user must be able to view all their inspections within a certain time-frame on any of the client's tablets.
Any help/advice on how I would approach the structuring of the data would be greatly appreciated.