1

I have a Table with the following Schema

CREATE TABLE [dbo].[ProjectNotifications](
[ProjectNotificationID] [int] IDENTITY(1,1) NOT NULL,
[UserID] [int] NOT NULL,
[DomainValueID] [int] NOT NULL,
[StatusId] [int] NULL,


CONSTRAINT [PK_ProjectNotification] PRIMARY KEY CLUSTERED 
(
    [ProjectNotificationID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

This table will probably have more Inserts and Updates. So, while brain storming we came up with an Idea of a Flag column called Status which indicates if the row is active or not. Lets say that as Status

Now, we need to evaluate if updating the flag column to a NULL or deleting it entirely will serve the purpose. we are curious to know which is better design.

If we go with updating the status column instead of Deleting a row, Will that doesn't mob the data with unused rows going forward?

Programmerzzz
  • 999
  • 9
  • 39
  • Logical deletes, as in your case highlighted by the 'Status' column, are preferable over physical deletes. This helps with audits of information and keeps accountability. – DeeKayy90 May 12 '15 at 22:31
  • But that doesn't clog the DB with more information which might or might not be used? – Programmerzzz May 12 '15 at 22:34
  • Of course, it will use page data. However, are you talking about millions of rows, or just a few hundred thousand? – DeeKayy90 May 12 '15 at 22:35
  • Yes as of now few hundreds and Thousands perhaps, as time goes on they go on accumulating right? – Programmerzzz May 12 '15 at 22:36
  • Here's an alternative suggestion then: move the deleted row to an audit table. For example, you have dbo.Table1, and dbo.Table1_Audit. On delete from dbo.Table1, move that row to dbo.Table1_Audit, and delete it from dbo.Table1. – DeeKayy90 May 12 '15 at 22:38
  • 1
    I would recommend you read these: http://stackoverflow.com/questions/2549839/are-soft-deletes-a-good-idea http://stackoverflow.com/questions/378331/physical-vs-logical-soft-delete-of-database-record http://richarddingwall.name/2009/11/20/the-trouble-with-soft-delete/ – DeeKayy90 May 12 '15 at 22:40

0 Answers0