I have a database I need some advice on how to proceed in this situation. Assuming I have a table called Tasks. Each task has a property called CreatedBy where I need to store information about the user that created that task.
I can store for example the Id of that user and when I fetch the task I fetch info about the user so that specific task Createdby field value will be John Smith for example instead of the id. All good so far. What or how should I handle the scenario where let's say the user John Smith has been removed/deleted from the system and the task still exists, how do I show the name of the user who created the task? I still need to show John Smith in there rather than just the id.
In this situation, I would use 'soft deletes' for the User table. Add a column to your User table and name it 'Active' with a default value of 1. When you wish to delete a user, set the value to 0. This will allow you continue enforcing referential integrity for your users.