I'm wondering if it's a data modeling strategy to store data in a retrieval table (not sure if that's a thing).
I have 5-6 joins going through 10,000s of records that is only going to get more expensive. When a user performs an action (finalizing payroll for example) I was thinking of storing all the very important and commonly retrieved table data into a separate table called Tablename_History.
If I can simply access this new retrieval by
id and retrieve ONE record with all the important information, wouldn't that be a better option?
I obviously know of 3 issues with this strategy:
- Data is not live.
- Duplication of data.
- Adding new columns within the history table would be difficult to set defaults and I might have to do some versioning.
That being said, am I missing something?