Advantage & disadvantage of Flag Delete or Moving it to physical Recycle Table

Hi All,

Need to expertise on Database Design.

How can we maintain the deleted records in SQL server.. Is it advisable to keep it on the same table with an delete flag.

Or is it advisable to move that record to a physical table prefix the table with Recycle_*.


Records may be no limit.

Please look at the example mentioned below..!!!

Employee

EmployeeId Name DeptID Salary DeleteFlag

E0001 Ugandhar D001 5000.00 True
E0002 Sunil D001 2500.00 False
E0003 Harish D002 6000.00 False
E0004 Anil D002 3000.00 True



Or

Employee

EmployeeId Name DeptID Salary

E0002 Sunil D001 2500.00
E0003 Harish D002 6000.00

Recycle_Employee

EmployeeId Name DeptID Salary
E0001 Ugandhar D001 5000.00
E0004 Anil D002 3000.00











Please Advice

Thanks :)

Ugandhar
ugan@dotcom-infoway.com