We are in a situation.
We have a legacy application and we are developing it all over now. However, we have to even redesign our DB architecture. In this process, we want both our applications to co-exist until the new one takes over and that might take an year or more. During this period, we would want the new application use the new schema, wherever possible, and old one FOR SURE always used the OLD schema. I need help:
In Understanding if what we doing is right If yes, what are the best practices and approaches If NO, what are the alternatives?
BTW, it is a ASP.NET MVC application development.
Thanks in advance.
I would recommend starting with a branch of your code - hopefully with a DVCS like Git or Mercurial.
Script your schema using something like Redgate SQL Compare and get your database schema in source control. Check out RoundhousE https://github.com/chucknorris/roundhouse or (shameless plug) AliaSQL https://github.com/ericdc1/AliaSQL to help with versioning your database.
Now in your new branch of the code make any DDL and DML changes needed to work with your new code. If you have intentions of running this side by side during development you have some hard choices to make if you are trying to change code and schema at the same time.