Guide For Best Practice for Database Design

[techless brainless]

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 :
user table



user_id Long
user_name vachar(50)
first_name vacarchar(50)
last_name vacarchar(50)
created_by Long
created_date date
updated_by Long
updated_date date
deleted tinyint
deleted_by Long
deleted_date date




or
Method 2

user_id Long
user_name vachar(50)
first_name vacarchar(50)
last_name vacarchar(50)
action_type tinyint COMMENT '1 create\n 2 update\n 3 delete\n'
action_date date



or Method 3
we will use the audit table which contains all the actions and the ids of the users ad the following

user table
user_id Long
user_name vachar(50)
first_name vacarchar(50)
last_name vacarchar(50)

action table
action_id Long
action_name varchar(500)

example for action_name 'create User' and 'Update User' and 'Delete User' ....etc


audit table
audit_id Long
user_id Long (foreign key of user_id in user table)
action_id Long (foreign key of action_id in action table)
description varchar(500)



or all above 3 methods are not the best approach .

Please guide me for the best database design


I am using Mysql and java



Edited 2 time(s). Last edit at 05/25/2013 03:43PM by techless brainless.