Expected database model is inconsistent in real-time

In this question, I was facing an issue where I was writing an update for a deployed application to bring the database up to date with the newer version we are deploying. Basic outline as follows:

• Began with currently deployed version of application
• Added new functionality that used existing database
• Added new database tables and relationships
• Added new functionality that depended on the new databse structur开发者_Python百科e
• Testing complete, ready for deployment

The issue here is that the currently deployed application has been in use for a few months and has a lot of data that would need to be preserved, so simply replacing the old with the new was not viable (at least not for the database, but of course it works for the code). So I used the following steps to write a script in SQL for the updated version of the application to run the first time it starts up to make the necessary changes to the database without touching existing data (aside from populating the new tables):

• Use VS2010's "Generate database from model" functionality to create a .sql (the model was originally created using the "Generate model from database" functionality)
• Remove all parts of the .sql that act on the existing tables, except for those that add FKs between new and old tables
• Use the resulting script to build the new database

Sounds pretty clean and done, right? Wrong. The mapping from the model to the database was all wrong for the new tables. Long story short, the database that generated the model had tables named in the plural (and the mapping was correct and the application worked), and the database generated by the model created tables in the plural (identical names to what the tables where the DB generated the model, but the model did not map to them). The solution ended up being to change the script to name the tables in the singular, and then everything worked flawlessly.

What happened here? The code remained untouched, no changes were made to the model, and the old tables continued to work fine the entire time, yet somewhere in the process of

• Generate script
• Delete "new" tables and constraints (those that don't yet exist in the deployed version)
• Run script to re-add the tables

the mapping decided to be to singularly named tables (User instead of Users, Address instead of Addresses, etc).

Can anyone explain to me how/why this would happen this way?

You might want to look at some of the tools that redgate supply - good tools for comparing two DB structures and generating a script to update.

http://www.red-gate.com/?utm_source=google&utm_medium=cpc&utm_content=brand_aware&utm_campaign=redgate&gclid=CIamkumgw6sCFcYPfAodnGVjsQ