Database structure for an app used to capture data in warehouses

Question

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.

Answer 1

Assuming a relational database, it sounds like you can create a table listing the different inspection types, and a table listing the questions per inspection type (with the inspection type as a Foreign Key).

Something like:

    InspectionTypes
-----------------
TypeID  TypeName
-----------------
1       Type A
2       Type B
3       Type C



InspectionQuestions
----------------------------------------
QuestionID  InspectionType  QuestionText
----------------------------------------
1           1               Is this working?
2           1               Is that working?
3           2               Is the other thing working?
4           2               Are those working?

Then you can add/remove inspection types via some sort of admin form (generally removals would be via "soft deletion": an Active flag added to the rows, for example)

And finally, you can get a list of questions for a specific type by querying with the type as a parameter:

SELECT * FROM InspectionQuestions WHERE InspectionType = @Type;

The parameter can come from the UI (user selections X type, or perhaps pre-selected via some other logic, etc).

This way you don't have to make changes to the DB structure for updates to questions/inspection types: you only have to change the data.

Addressing some of your specific ideas:

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?

The "one column for each X" is a huge red flag in relational tables, fraught with problems on many levels. If you're considering this in any case, reconsider!

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?

This too is a poor approach, for the reason you cited. In relational database design, the database structure should only change if some driving business rule changes it (e.g., questions are now multiple choice, add a new table of possible answers for each question).

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).

Properly normalized data (such as in my above example/suggestion) can be queried and "massaged" accordingly for most any desired output. This is not typically a concern while designing the data structure itself.

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.

Search for multi- vs single-tenant database scenarios. I prefer single-tenant databases. Maintenance will be slightly more time consuming, but far, far less than if you have multiple clients in a single database (you'll likely end up spending more time trying to fully segregate clients within a database than you will maintaining single databases for each client, and there's other, much more significant considerations that tend to veer towards single-tenancy, IMO).

Final note: most of your questions revolve around a concept called Database Normalization. You may do well to study the topic a bit. For a quick ref, one of my favorites has been: https://dymeng.com/put-stuff-where-it-belongs/ (however, this is highly distilled... I recommend an "actual" study of the topic as well). Note that none of my answer assumes you're considering a NoSQL solution (which is a very different ballgame, and probably not strictly necessary: I'm of the mind that people should start with relational systems, then move to NoSQL as they determine where the better fits are)