I am currently working on a large project to move an MS Access application to SQL server. Our team has been having discussions on the best way to design a part of the new database so I figured I would reach out for feedback.
In the current MS Access design, we load data daily which is then processed in the UI. Since we are limited to the 2GB in size for Access, we do the following.
- Morning Import - data from previous day is deleted first, then new data is loaded
- During the day users process data
- End of day, we take database snapshot (basically direct copies of the Access DB which are stored for users to be able to go back in time for research)
In the new version, all data inserted into each table will have a Business Date assigned to it which is the date it was added to the table. Then on the front-end, the UI will have a search by date to get the records from that day.
Right now we are contemplating the following:
- Create 2 databases - one for the
current day and then an Archive. The
data that is normally deleted daily
would be moved to the Archive DB
each morning. For the front-end we
would have to
UNION
the 2 databases to get the final result set - Create 2 schemas in the same database. One for the Current and
then an Archive. Again we would
have to
union
the data but it would be in the same database - just leave the data in the current day tables, we wouldn't need a Union on the data for the UI searches.
Let me add that we are required to hold at least 6 years of data that is loaded to the database, so the table sizes will grow pretty large.
I am just trying to figure out the best approach to this sort of problem. I am sure we are not the only ones in this type of situation. I am open to any suggestions, thoughts on how we should proceed.