1

I need your wisdom. I am in the process of doing a major overhaul of a system from classic asp to mvc and, in the process, am refactoring a major table (call it MAIN) in sql server 2008 r2. The table has over a hundred columns (I am not responsible for this) and I'm moving the structure to 7 tables for performance, flexibility, and maintainability. However, new and old code references the MAIN table in many old and new pages. I want to transition to the new tables smoothly and gradually, and it's an active, core site for the business. Please help me out.

Once I've transferred the existing data,

I've thought of:

1.) Using standard insert, update, and delete triggers on both the MAIN table and the seven NEW tables. I would have to include a catch (http://stackoverflow.com/questions/2237499/sql-server-trigger-loop) for infinite trigger loops.

2.) Creating a view emulating the MAIN table, referencing the NEW tables. I would use an INSTEAD OF trigger on the view to insert and update the tables via the view. This would allow me to drop the MAIN table immediately.

Which of these makes more sense? Is either supremely stupid? Both? I'm definitely not a dba, but I need to make this happen right the first time.

Thank you.

cdutcher
  • 258
  • 4
  • 8

1 Answers1

0

It sounds like your app is directly accessing the database table, which is not a good idea as you are now finding out.

Have you considering writing stored procs to handle the CRUD operations, which will abstract away the database design? Then you can call the stored procs from your app and pass all the data as parameters. The stored proc will do all the work of updating all the new tables (you should use a transaction of course) and you can stop using your old table immediately.

I would think that this would be easier than trying to write a complicated view, and definitely gives you more visibility than using triggers.

DeanOC
  • 7,142
  • 6
  • 42
  • 56
  • That would definitely be a nicer scenario. It could be that switching the table design after all of the code refactoring (classic asp to mvc ) is the way to go. I thought there would be value to coding to the NEW tables in the new code and leaving the old code largely as is (just swapping the MAIN table for a MAIN view). – cdutcher Jul 05 '12 at 04:00