Win a copy of Micro Frontends in Action this week in the Server-Side JavaScript and NodeJS forum!
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other all forums
this forum made possible by our volunteer staff, including ...
  • Campbell Ritchie
  • Ron McLeod
  • Paul Clapham
  • Bear Bibeault
  • Junilu Lacar
  • Jeanne Boyarsky
  • Tim Cooke
  • Henry Wong
Saloon Keepers:
  • Tim Moores
  • Stephan van Hulst
  • Tim Holloway
  • salvin francis
  • Frits Walraven
  • Scott Selikoff
  • Piet Souris
  • Carey Brown

Guide For Best Practice for Database Design

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
author & internet detective
Posts: 40213
Eclipse IDE VI Editor Java
  • 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?
Montana has cold dark nights. Perfect for the heat from incandescent light. Tiny ad:
the value of filler advertising in 2020
    Bookmark Topic Watch Topic
  • New Topic