I'm new to mobile app development. My experience is limited to small databases (in house) for one company.
I'm developing a (mobile) field testing app where there will be many users (possibly a few thousand) working for many companies (possibly a few hundred). They will need to:
create / submit test reports
See test reports from other workers in their company
When creating a report, the worker will be able to pull in equipment, labor etc. from a common company specific master list.
A separate (web) app will allow viewing the reports and archiving them as .pdfs. Once archived a flag will be set on the row to hide it from the mobile workers.
A couple questions:
I'm planning on one database with a "company ID" (UUID) column. Every company (tenant) will be in the same DB. I intend to use Supabase. I believe this is called "Single database, shared schema"
It seems strange to me to mix up different companies data in the same table, but I understand this is how many production apps work. None of the data we are storing is particularly sensitive. Is this a standard method?
I see two methods to sort the data:
Any CRUD actions will reference a company ID (a UUID separate from user ID), which would could live in a persisted app state on the device. Most tables would have a company ID column.
Only use the user ID, and have the backend query a table to associate a user with a company.
This app would be closed unless a company paid for a membership, at which point a company UUID would be issued and team members could join once the UUID is shared with them (in the first case) or the user was set up in the backend (for the second case).
I feel like the second option would require way more JOIN/foreign key operations as nearly every CRUD operation will require a lookup to figure out which company the user is associated with. As most core functionality is company-centric (client lookup, job locations, material lists), etc. I feel that the first method would be better/faster in the long run even though it requires managing the on-device company ID.
Thoughts?
Thank YOu.