In many databases that I seem to be working with these days, I can't just delete the record for some reason, including later, when they can be displayed later (say, a product that no longer exists) or just keeping a story of what happened.
So my question is how best to end the recording.
I often added a column date_expired, which is a datetime field. I usually ask where date_expired = 0or date_expired = 0 OR date_expired > NOW(), depending on whether the data expired in the future. Similarly, I also added a field call expired_flag. If this parameter is set to true / 1, the entry is considered to have expired. This is probably the easiest way, although you need to remember to include the expire clause at any time when you need only the current elements.
Another method I've seen is to move the record to the archive table, but this can become quite messy when there are a large number of tables that require history tables. It also makes it easier to find a value (like a country), since you need to first make a left join (for example) and then run a second query to find the actual value (or repeat the query with the left join changed).
Another option that I have not yet seen, and I have completely tried myself, is to have a table containing either all the data from all expired records, or some form of it — some kind of history table. In this case, the search will be even more complicated, since you will need to search for a possibly massive table and then analyze the data.
Are there other solutions or are their modifications better?
MySQL ( PHP), , .