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
userto 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?
Best Answer
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.