I have a SQL table called "EVENT" and a copy table called "PAST_EVENT". The EVENT table has a foreign key to it's corresponding PAST_EVENT. Given that:
I decided to have a duplicate of information in the EVENT table in the PAST_EVENT table because my application is looking for data about current and ongoing events (only needing to read from the EVENT table), OR it is looking for events that have ended (only needing to read from the PAST_EVENT table). But never both. My rationale is that making SQL queries on a subset of events is quicker than the alternative.
Alternative:
What if instead, I consolidate both tables into one table called EVENT. I would then add a database indexed boolean field, "hasEnded", in order to query for ongoing or ended events.
Which of the aforementioned strategies is more performant?
More Info (update):
"Ludicrous" is the word that comes to mind. In the interest of some definition of "efficiency", you want to duplicate the data and every modification to the data. That just does not seem efficient to me at all.
I would start with soft deletes -- simple a flag as to whether the event is deleted or not. That does a good job of defining the events.
Because the two modes that you need are either everything or just the non-deleted ones, you can then think about optimizing the storage if necessary. One option -- if your database supports them -- is a clustered index on the delete flag. Such clustering is usually not recommended on a binary flag. But if the non-deleted data is small, it can be a win for queries looking for that.
Another alternative is to use partitions. Some databases don't let you change the partition key -- which poses a challenge.
Finally, you could also have a delete trigger on the events table that would load the deleted events into another table. Queries on all events would require unioning them together.