• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Guide For Best Practice for Database Design

 
Suresh Khant
Ranch Hand
Posts: 118
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi All,

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 :


Method 2



Method 3
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 .

Please guide me for the best database design


I am using Mysql and java
 
Jeanne Boyarsky
author & internet detective
Marshal
Posts: 35279
384
Eclipse IDE Java VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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?
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic