November 16, 2013 at 3:13 am
Hi,
Recently, I have started work on a database of a online product based website. We add thousands of products every month and after a specified time, we remove some products. For this purpose, we don't delete this product from database, we just change its flag from 1 to 0. Because we need these deleted products for reports, we don't delete from database. We just change the flag. And this process applies on each section (e.g. category, product etc).
Now problem is, now we have millions of these deleted records in several tables with several dependencies. So, each query contain a extra condition to check the flag for each table.
As database is growing, I am afraid because of this approach. I am not sure that this is correct approach to handle deleted data. I know, all websites use this type of functionality but how they manage their deleted (removed) data?
Can someone suggest me proper approach to handle this type of process?
thanks,
Anuj
November 16, 2013 at 5:16 am
One option would be to move the data into separate tables and performa a UNION ALL query für your reports.
You also might want to look into indexed filtered views if such a concept would be an option for you.
November 16, 2013 at 6:09 am
thanks for your reply.
Every social networking site face this situation. E.g. : when we delete any photo or or when we delete our profile etc.
They doesn't delete data physically. I am not sure, they also use this type of flag condition or they move data. Because this is an OLTP, so we have to minimize where clause. And this flag is essential condition.
Apart from this flag process, Is there any other way/approach to achieve this goal?
November 16, 2013 at 6:22 am
Apart from this flag process, Is there any other way/approach to achieve this goal?
See my previous post:
...to move the data into separate tables and performa a UNION ALL query für your reports...
Did you even bother to read it? :angry:
November 16, 2013 at 10:49 am
What I might suggest is similar to Lutz.
Say you have this information in the Profile table. You can create an archive table called ProfileArchive. Copy all data here.
Next, you rename the existing table to ProfileCurrent. Obviously this during a maintenance time as you'll break the app. Now, you can create a view called Profile that does the union Lutz mentioned. Periodically you can move the data from Current to Archive as appropriate. You could also create a filtered index looking for active status' only.
That should minimize code changes.
November 16, 2013 at 9:31 pm
Lutz, Steve,
thanks for your valuable suggestions.
I think, union all will be a solution for me.
thanks again.
November 17, 2013 at 4:58 am
we normally archive data either through table partition or we make use of filtered index.
November 17, 2013 at 5:08 am
thanks praveen,
Do you archive data by using this type of flag column?
November 17, 2013 at 7:03 am
if you want to archive data by horizontally partitioning the table then you have to go for table partition, but for table partition you need to have good candidate key to partition like date column or id column also partitioned file group need to be placed on separate disk for better performance.
in your case i suppose filtered index is one solution, as mentioned by other experts other solution would be to move all deleted data to new table, this option would give you better performance if you place this table on different disk.
November 17, 2013 at 7:51 am
thanks praveen !!
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply