Assume I have the following tables:
- Article (id[PK], home, category, userId[FK], locationId[FK])
- User (id[PK], name)
- Location (id[PK], address, name)
Now I want to create a new ArticleArchive, that will have a snapshot of a certain Article (which itself points to other foreign key tables) and also some new attributes
Now the issue by doing so like this: ArticleArchive (id[PK], school, country, archiveId[FK])
is that if i'll make changes/delete the original archive it would also be reflected in ArticleArchive,
Another issue, is that i want also a snapshot of Article's user, in case the user has changed,
So the only solution i can think about is to take all attributes from Article, and recursively from its foreign key tables as well and just make a big table. I guess this solution is "ok" because it won't require join tables, but is there more elegant solution to make snapshots?
Articlequite volatile whereas the referenced tables more 'static'? "Archive" usually means you merely want a backup of the tables off-line/you won't want to query the state as at some time in the past. If you do want to query history, include 'effective date' in some/all of the schemas. But now all your FKs need some subtle definitions. This is sometimes called '6th Normal Form' for Temporal Databases. wikip has an article.