According to my humble experience I have noticed the following related to database while creating an application :
1 . Most of the applications have a table called "user table"
2. Application should have audit or log table which contains all the actions done by the users ,
3. Delete operations should not really happened to keep in the integrity of the system but instead of that we have to keep a flag
my question what is the best practices to have a user table : is like
Method 1 :
we will use the audit table which contains all the actions and the ids of the users ad the following
or all above 3 methods are not the best approach .
There isn't one universally best design. I would rule out choice #2 because it doesn't have a clear primary key. Normally, the user_id is the primary key. But since you have multiple actions for each user, the key would have to be composite (or an artificial key.) This seems overly complicated.
Between #1 and #3, it depends on the requirements. Is user id a foreign key? If so, you can't just delete records, which rules out #3. For example, I can't just delete the row with your user id. What would happen to this post?