I have a database that was originally created by a couple of people for a small business unit (about 8 staff and 10 customers) in MS Access. The original 'developer' was a non-geek who had been sent on a two day ms access course by a previous employer. It was poorly designed but worked well enough, as the unit grew there was the need to have more than one person administrating the data. At this time I had joined the unit and suggested using SQL server as the back end and keeping the access front end allowing multiple users, three or four at that time.
It now has approx 40 users servicing 50+ business customers, and is essentially a complete mess. But we need this tool to keep track of our jobs queue, who is doing what, when it is due, who for and every thing. Without it we could not function.
So I now want to throw it away and rebuild from scratch and do it properly, and ditch the access front end in the process. While doing this I need to keep the current system running, develop the new one and some how keep the data in sync and run them in parallel for a while until the higher powers are satisfied it's not going to cause any problems.
Does any one have an suggestions or pointer on how to keep the data in sync given that I will be taking several giant tables and normalizing them into sensible tables in the new schema?
It is not a huge database, the main table listing all jobs grows by about 1500 records per month for example, and currently has about 100,000 rows.
My thoughts were to add a row version column to the tables, and then hack something together to monitor it and translate to the new schema.
Is there a better way?