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

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.