Being tied to a legacy database schema that no more reflects your computer data model is every developer's nightmare. Yet with the talk of refactoring code for maintainability I haven't heard a lot of refactoring outdated database schemas.

What exactly are some tips about how to transition to some better schema having to break all of the code that depends on that old one? I'll propose a particular problem I'm needing to illustrate my point but you can give suggestions about other techniques which have proven useful - individuals will probably prove useful too.

My example:

My opportunity receives and ships items. Now an item receipt along with a product shipment possess some completely different data connected together therefore the original database designers produced another table for receipts as well as for deliveries.

During my twelve months dealing with this technique I have started to the realization the current schema does not create a lick of sense. In the end, both a receipt along with a shipment are essentially a transaction, both involve altering the quantity of an item, in mind just the +/- sign differs. Indeed, we regularly must find the quantity the product has transformed during a period of time, an issue that this design is completely intractable.

Clearly the right design is always to possess a single Transactions table using the Id as being a foreign key of whether ReceiptInfo or perhaps a ShipmentInfo table. Regrettably, the incorrect schema was already in production for many many has 100s of saved methods, and 1000's of lines of code wiped off from it. How then can one transition the schema to operate properly?

Here is a whole catalogue of database refactorings:

This book (Refactoring Databases) is a God-send in my experience when confronted with legacy database schemas, including after i needed to cope with almost the identical problem for the inventory database.

Also, getting something in position to trace changes towards the database schema (like a number of alter scripts that's saved int he source control repository) helps hugely in determining code-to-database dependencies.

This is a very hard factor to operate around A few quick options after refactoring the database are:

  • Create sights that match the initial schema but pull in the new schema You might need triggers here so any updates towards the sights can be treated.
  • Produce the new schema and set in triggers on both sides to keep sleep issues.
  • Saved methods and sights are the friend here. Even when the machine does not rely on them, change it out for their services, then refactor the database underneath.

    Your receipts and deliveries then become sights.

    Beware, receipts and deliveries are really two completely different monsters in many systems I've labored with. Receipts are associated with providers, while deliveries are associated with clients (or customer/ship-to locations). In the inventory level, they're frequently symbolized exactly the same.

    Is data access restricted to saved methods? Otherwise, the job might be extremely difficult. If that's the case, you just need to make certain your computer data migration scripts work nicely shifting in the old towards the new schema, after which make certain your saved methods recognition theur inputs and results.

    Hopefully not one of them have "choose *" queries. When they do, use 'sp_help tablename' to find the complete listing of posts, copy that out and replace each * using the complete column list, simply to make certain you do not break client code.

    I would suggest making the alterations progressively, and do plenty of integration testing. It's difficult to perform a significant redesign without presenting a couple of bugs.

    The very first factor would be to produce the table schema. I already did that for any Legacy database using Enterprise Architect. You are able to choose the DB and it'll create you every tables/fields. Then, you will have to split my way through groups. Exemple all of your receives and ships items together, client stuff in some other category. Once things are obvious up, you'll have the ability to refactor area by creating new table, new releashionship and new fields. Obviously, this can need large amount of change if all is utilized without Saved Procedure.