I'm trying to think of fine way to store historical data for my web application. The application has users that add photos, comment and grade them. All this is stored in relational database.

The users also have ability to delete own photos (and thus comments/grades) and own account.

I want to store those deleted entities as historical data. The aim of it is to distinguish categories of what people like to photograph and classify users towards those categories.

Should I store it in some NoSQL database? Or maybe in my origin database by marking it with is_deleted field? Maybe different table e.g. archived_users, archived_photos? Another database instance for only historical data?

Has anybody dealt with such task and is willing to share thoughts?

So far, I'm using is_deleted flag for photos and backup table (archived_users) for users. Why such distinction? To avoid violating unique constraint on username field. This solution has 3 problems to me.

  • It is incoherent to do same thing in 2 ways. Or maybe it's ok?
  • It is somehow strange moving user to another table (with associations to photos). It creates some superficial fields. Or maybe it's ok?
  • "Living" data is mixed with historical data. Could it be a performance problem when database is really huge?
  • 2
    Sharing your research helps everyone. Tell us what you've tried and why it didn’t meet your needs. This demonstrates that you’ve taken the time to try to help yourself, it saves us from reiterating obvious answers, and most of all it helps you get a more specific and relevant answer. Also see How to Ask – gnat Dec 9 '13 at 17:02
  • 2
    A 'deleted' flag is probably the way to go, and is generally a best practice. It doesn't sound like you have any series data, which is where you'd want to look at a separate datastore. You don't indicate any reason why you want to keep this deleted data though, and perhaps if you explain that there may be some subtleties that would cause the best solution to be something else. – renegade Dec 9 '13 at 17:51
  • 1
    @gnat: I've shared 3 ideas of how to approach the problem. Hope that counts. – lilly Dec 9 '13 at 21:53
  • 1
    @renegade: I've added some details in the question about the reason. – lilly Dec 9 '13 at 21:55

Using flags/indicators in your main database to show what's been deleted is a good way to start. And stick with that if you can.

Your other ideas, such as moving "deleted" data to separate archive tables/databases is probably only necessary if the amount of soft-deleted data is sooooo large that keeping it in the main database has a significant performance impact. Keep it simple, if you can.

  • Currently I'm using flags. Still I'm curious if there is something smarter :) – lilly Dec 9 '13 at 21:58
  • @a1ku: If you wanted to store more than just "deleted" data, you could start saving versions of records every time a user does an edit/update. There's been a number of questions on that topic already, I'm sure you'll find them. That might seem like a "smarter" solution, but certainly not easier! – FrustratedWithFormsDesigner Dec 9 '13 at 22:02

Not sure why you want to maintain data the user has explicitly deleted. Given the propensity of hacks, you are leaving yourself open to all sorts of trouble by maintaining the data.
If you really want to maintain it for "historical" purposes, export them to an offline database.

  • 1
    @a1ku: "The aim...what people like...and classify users towards those categories" You don't need historical data for that...that's what the active data is for. If someone deletes something, they presume it's gone, vanished, unreachable -- unless the NSA is involved. So, if you are going to maintain and use deleted items, I'd advise getting permission first. – grassBlade Dec 11 '13 at 21:41
  • 1
    While the points you're making aren't necessarily wrong, they don't answer the OP's question. As such, I have downvoted this answer. – MetaFight Dec 19 '13 at 4:02
  • @MetaFight: So, what you are saying is that if the OP asks for a decision on 2 bad choices, I shouldn't point that out? And, I did provide a 3rd alternative...saving the data offline, where, if necessary, the data could be retrieved and then restored. – grassBlade Dec 19 '13 at 14:25
  • I've reread your answer and, to be honest, I'm not sure why I downvoted now. Judging by the time of my comment it was around 4:30 am and there's a good chance I simply misunderstood you. I probably disagreed more with your first comment than with the answer itself. I tried to revert the downvote, but it was too long ago. sorry. – MetaFight Dec 19 '13 at 14:34
  • No problem. I'm not too keen on the voting thing anyway. I was more concerned that my answer was considered inappropriate for some reason I couldn't fathom – grassBlade Dec 20 '13 at 19:53

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.