Apologies in advance if this is a dumb question.

Background: I am a student intern, I've been given the task of adding a new feature the companies client administration system. Basically it involves "purging" subscriptions, they want selected subscriptions to be removed from the client admin system but remain in the database for recording purposes. We currently have around 600,000 subscriptions. They wish to "purge" 10,000 subscriptions right away and another 30,000 over the next few months, so around 6% of the subscriptions table.

What I am wondering is what is the best way to accomplish this. I have come up with two options;

a) Add a new table to the database to store the "purged" subscriptions with 3 columns, purgedId, subscriptionId which is a fk referencing the subscription, and purgedDate. Then in any sql that references the subscription table left outer join the purged table and use "where purgedId = null".

b) Add a new column to the subscription table to store the purgedDate and add "where purgedDate = null" to all of my subscription sql.

Option a) seems like a waste, to add a whole new table to store essentially one attribute. Option b) seems like a waste, to add a column that is only used by 6% of the rows in the table.

On another note, is an outer left join the best way to exclude the purged subscriptions?

Thanks in advance for any assistance you are able to provide and once again sorry if this is an elementary question.

  • Does purging mean truly removing the data, archiving the data elsewhere in case it's needed, or simply keeping the data from coming up in a search? Apr 9, 2012 at 17:04
  • simply keeping the data from coming up in a search.
    – curriegrr
    Apr 9, 2012 at 19:50

1 Answer 1


I'd go with option 3, which will require less overall work:

  • Make a lookup table that contains a list of purged records and dates, like in your first option
  • Rename your current table to something like dbo.Tablename_master
  • Create a VIEW named dbo.Tablename which consists of:


SELECT <fields>
FROM dbo.Tablename_Master
WHERE PrimaryKeyField NOT IN (SELECT PrimaryKeyField FROM PurgeTable)

Now you don't need to modify any existing code, since all references to your original table go through the view instead. This will be complicated if you have any existing constraints but they should be easy to work around.

  • 1
    Exactly what I was going to suggest but you beat me to it.
    – HLGEM
    Apr 9, 2012 at 18:15
  • Thank you very much, you saved me a ton of needless work.
    – curriegrr
    Apr 9, 2012 at 20:06

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.