I've been building an application that has a 6-monthly update schedule, i.e. a cut of the data for H1 & H2 of each year. The previous version of this application implemented this by making a copy of an MS Access database and starting again from scratch. Obviously this leads to multiple copies of a database lying around a network, with all the attendant problems you'd expect.
The new design includes a Dataset table, which holds a list of data cuts (e.g. 2014 H1, 2014 H2... etc.).
What is the best way to implement this into the larger design? I can think of two ways to do it:
1 - Add a 'Dataset' field to all fact table records, i.e. prices, volumes etc. are associated with a particular reporting period.
2 - Add a new table acting as a junction between dimension tables and the data cut, i.e. each asset (e.g. product, stock item etc.) in the database is assigned a new Id for each cut.
I can see benefits and drawbacks to each approach: then first leads to a more complex key in many tables, and the second leads to more tables but potentially greater efficiency.
Is there a standard method/best practice of implementing this, or is there a better approach entirely that I'm unaware of?