SQL App Impersonator

In this SQL project, I simulate what goes on in the database when various actions are carried out by developers and app users. I use a very simple and easy to understand fictitious app called Digital Diary.

I start off by creating a database to house the tables holding the data in a structure.

I then created the first table of the database — a user table which stores all users that sign onto app and create a profile. The users’ table assigns a unique primary key to every user and captures their username, password,location and user_score. For the purpose of this project, I populate the table with arbitrary records.

Next, I create a table that records the diary entries made into the app by users, making sure to link each diary entry with the user making the entry through the user_id. Once again, this table is populated by arbitrary entries.

I query the tables to see how they are looking and confirm that they look how I meant them to look.

Users’ table

Diary entries

What happens when a user updates their title, entry or main_character ?

The change is made on the diary entry table which then reflects in the up.

What happens in the database when a user deletes their account ?

The user’s account details are completely removed from the diary_users table. The number of users reduces by 1 user.

What happens when an app update is made to improve user experience?

I updated the app to include a “feelings” segment where users can input their feelings. The default is set to ‘Unknown’ so that previous entries and entries that do not include feelings are stored as the default value in the table.

Now some of the users, seeing the update, opt to revisit previous entries and input their feelings in the designated segment.

Naturally, the change is reflected in the diary entry table.

What happens in the database when new users get on the app ie. create accounts

The new users are recorded in the diary_users table.

Our diary_users table count increases to 6, reflecting the change. Notice that the id number ‘4’ is skipped completely. Though their user deleted his account, nobody takes on that id.

One of the new users makes their first diary entry.

This is a pretty simple project to take on if you’re new to SQL and looking to practice. You can access my raw code on github using this link GitHub Link .