Dealing with poorly designed databases is a simple and common fact of life for programmers.  It happens, sometimes due to lack of experience or education, or sometimes because business requirements were never analyzed properly or they changed.  We've all inherited poor database schemas that we've been forced to work with, and of course we've all created those poor schemas ourselves over the years.

For this discussion, I will classify the database model problems we encounter into the following two categories:

Physical Design Problems – The physical design of the schema – tables, columns, data types, constraints, etc – is causing inefficencies in performance and/or code.  The design is technically storing the data it needs to, but perhaps it is not normalized properly, or there's lots of duplicates, incorrect data types, columns that contain CSV data, and so on.  It "works", but you have to write large amounts of complicated SQL to do basic tasks.

Logical Design Problems – The logical design is incapable of storing the necessary data; we simply cannot model what we need to store.  For example, the model might allowed for only one address per Vendor, but we need to store multiple addresses for billing versus shipping; or perhaps we can only assign one consultant per Project but in reality we need to store many of them, all assigned particular roles.

(Of course, many data models have both problems! )

I actually feel that logical design problems are better to deal with.  Why? Because it forces us to stop development and to redesign the database and/or applications, since it simply is not working.  Sure, it can be a lot of effort, but in the end, if we did things well, the benefit is that we now have a good data model that stores what we need.

Physical design problems occurs very frequently, and it is a far worse situation.  It rarely gets addressed or fixed or changed, because it is getting the job done.  The result is that, over time, we keep piling on more and more code and hacks and tricks to get data in and out, and the performance and complexity of the code grows greatly, but it all still "works".  There is never really that motivation to fix things until performance or code maintenance is so bad we have no other choice.  At that point, of course, it is a huge project since we have to deal with large volumes of convoluted code.  If we had refactored the design properly earlier on, we'd be better off, but there was never truly that need or motivation to do it.

The reason why I bring this up is because we often do need to refactor our designs; and, in fact, in some cases we should do it even more often that we do (i.e., physical design problems).  When the logical design doesn't work, we have no choice: It needs to  change. If we try to use "tricks" to make a perfectly good schema work for an entirely different logical model, we end up with a really poor schema and now we've have both physical and logical problems with our model. (Only one place to store the ConsultantID for each Project?  Simple – change that column to a CSV list!) The worst of both worlds!

We often see people require help with writing SQL in the forums that ends up being very difficult because the database design is so poor; the best solution is always to simply to fix the design. But, of course, that rarely happens.  The reasons given are usually:

• We have lots of reports that access the schema and we'd need to rewrite them all
• We'd have to go through our applications and re-write all of the data access code
• There are DTS packages with SQL statements that would need to be rewritten
• Other external processes access the database schema, it would be impossible to track them all down