Upgrade strategies for bad DB schema designs

Question

I've shown up at a new job and discovered database which is in dire need of some help. There are many many things wrong with it, including

• No foreign keys...anywhere. They're faked by using ints and managing the relationship in code.
• Practically every field can be NULL, which isn't really true
• Naming conventions for tables and columns are practically non-existent
• Varchars which are storing concatenated strings of relational information

Folks can argue, "It works", which it is. But moving forward, it's a total pain to manage all of this with code and opens us up to bugs IMO. Basically, the DB is being used as a flat file since it's not doing a whole lot of work.

I want to fix this. The issues I see now are:

1. We have a lot of data (migration, possibly tricky)
2. All of the DB logic is in code (with migration comes big code changes)

I'm also tempted to do something "radical" like moving to a schema-free DB.

What are some good strategies when faced with an existing DB built upon a poorly designed schema?

Answer 1

Enforce Foreign Keys: If a relationship exists in the domain, then it should have a Foreign Key.

Renaming existing tables/columns is fraught with danger, especially if there are many systems accessing the Database directly. Gotchas include tasks that run only periodically; these are often missed.

Of Interest: Scott Ambler's article: Introduction To Database Refactoring

and Catalog of Database Refactorings

Answer 2

I'd say leave it alone until you really understand it. Then make sure you don't start with one of the Things You Should Never Do.

Answer 3

First see how bad the code is related to the DB if it is all mixed in no DAO layer you shouldn't think about a rewrite but if there is a DAO layer then it would be time to rewrite that layer and DB along with it. If possible make the migration tool based on using the two DAOs.

But my guess is there is no DAO so you need to find what areas of the code you are going to be changing and what parts of the DB that relates to hopefully you can cut it up into smaller parts that can be updated as you maintain. Biggest deal is to get FKs in there and start checking for proper indexes there is a good chance they aren't being done correctly.

I wouldn't worry too much about naming until the rest of the db is under control. As for the NULLs if the program chokes on a value being NULL don't let it be NULL but if the program can handle it I wouldn't worry about it at this point in the future if it is doing a default value move that to the DB but that is way down the line from the sound of things.

Do something about the Varchars sooner rather then later. If anything make that the first pure background fix to the program.

The other thing to do is estimate the effort of each areas change and then add that price to the cost of new development on that section of code. That way you can fix the parts as you add new features.

Answer 4

Views are commonly used to transition between changing data models because of the encapsulation. A view looks like a table, but does not exist as a finite object in the database - you can change what column is being returned for a given column alias as desired. This allows you to setup your codebase to use a view, so you can move from the old table structure to the new one without the application needing to be updated. But it means the view has to return the data in the existing format. For example - your current data model has:

SELECT t.column --a list of concatenated strings, assuming comma separated
  FROM TABLE t

...so the first version of the view would be the query above, but once you created the new table that uses 3NF, the query for the view would use:

SELECT GROUP_CONCAT(t.column SEPARATOR ',')
  FROM NEW_TABLE t

...and the application code would never know that anything changed.

The problem with MySQL is that the view support is limited - you can't use variables within it, nor can they have subqueries.

The reality to the changes you wish to make is effectively rewriting the application from the ground up. Moving logic from the codebase into the data model will drastically change how the application gets the data. Model-View-Controller (MVC) is ideal to implement with changes like these, to minimize the cost of future changes like these.

Answer 5

1. Create a completely new schema and make sure that it is fully normalized and contains any unique, check and not null constraints etc that are required and that appropriate data types are used.
2. Prepopulate each table that fills the parent role in a foreign key relationship with a single 'Unknown' record.
3. Create an ETL (Extract Transform Load) process (I can recommend SSIS (SQL Server Integration Services) but there are plenty of others) that you can use to refill the new schema from the existing one on a regular basis. Use the 'Unknown' record as the parent of any orphaned records - there will be plenty ;). You will need to put some thought into how you will consolidate duplicate records - this will probably need to be on a case by case basis.
4. Use as many iterations as are necessary to refine your new schema (ensure that the ETL Process is maintained and run regularly).
5. Create views over the new schema that match the existing schema as closely as possible.
6. Incrementally modify any clients to use the new schema making temporary use of the views where necessary. You should be able to gradually turn off parts of the ETL process and eventually disable it completely.

Answer 6

Read Scott Ambler's book on Refactoring Databases. It covers a good many techniques for how to go about improving a database - including the transitional measures needed to allow both old and new programs to work with the changing design.