Database model

[supernyv]

Discussion on the database design (RDBMS independent) to determine the best data model for the app.

[supernyv]

This is my proposed model, which I'm currently using for all input from and output to the database. @JorgeMiguelGomes

[Coding-with-Adam]

Thanks for sharing, @supernyv Make sure to convert the report --> answer_date to a TimeSTAMP.

Is there a reason for country region and sub-region?

[supernyv]

Hi @Coding-with-Adam , Alright. For the country region and sub-region, those are not absolutely necessary but in analytics it is common to group some data by country, by region (say Europe) and by subregion (say Eastern Europe). But it all depends on the needs of the owner of the app. If they find it useful, we could either add these two columns to the application page for the user to input or have it exclusively updated by the app admin for each country added by users. If not, I can simply remove them

[supernyv]

[supernyv]

The updated model as of now. In particul:

• User table with application_date and approval_date to setup the vetting process
• Report table with open_report_timestamp (in place of timestamp) and close_report_timestamp (instead of response_date)

[supernyv]

I ended up adding more field to the vetted_user table to avoid splitting it into two tables (one for actual users and one for applicants):

So, now the admin page will be developed with this model in mind.

[supernyv]

Kindly note that if you already have the database built and populated with data, no need to rerun the model (which would overwrite the existing database and replace with a blank new one), instead I have prepared update scripts for all the changes made so far, in the asset folder, but they need to be run in order of their creation date. Or if that's too complicated, you could simply forward engineer (run) the new model.

[supernyv]

Major update

[supernyv]

Hi @JorgeMiguelGomes Once you try the app, there will be one last decision to be made on the model: If we need to keep the history of changing user approval status. Illustration below: Setup 1 (Currently implemented):

1. A user applies -> The application is stored in vetted_user table with the application_decision as Pending.
2. An admin approves or rejects the application -> The application_decision for that user in the vetted_user table changes to Approved or Rejected according to the admin's decision.
3. If an admin deletes a user -> The application_decision for that user in the vetted_user table now changes to Deleted

Advantages of setup 1:

• Only one table for storing the up-to date information about each user.
• Simple and straight forward process for updating the user's status (Approved, Rejected, Deleted, Hidden)
  4. If an admin re-adds or hide a user -> The application_decision for that user in the vetted_user table changes to "Approved" or "Hidden" according to the admin's decision.

Disadvantages of setup 1:

• History of changes in the user's status is not stored. So, we loose information about, for example, when a user was first approved, how many times they have been approved and deleted, if they were rejected or hidden at some point in time, ...

Setup 2:

1. A user applies -> The application is stored in vetted_user table with the application_decision as Pending. Same as setup 1.
2. An admin approves or rejects the application -> The application_decision for that user in the vetted_user table changes to Approved or Rejected according to the admin's decision. Then another table, called user_status_history for example, is updated with the previous status of the user, their new status, the date of change and the author of the change.
3. If an admin deletes a user -> The application_decision for that user in the vetted_user table does not change (the user is approved or rejected only once), but the user_status_history table is updated similar to the above point 2.
4. If an admin re-adds or hide a user -> The same is done here, the vetted_user table does not change, only the user_status history is update similar to point 2.

Advantages of setup 2:

• We have the full history of all changes made to each user's status, by who, when, and optionally why.

Disadvantages of setup 2:

• We need an additional table in the database
• Will required heavy modifications of the admin decisions and the login process in the app to accommodate the additional table.