Here's my predicament. One of several programs I've recently inherited is built with a horrible database on the backend. The esteemed creators of it apparently did not appreciate relational concepts. A table for each and every client, named as a unique client ID. Eighty-three cryptically named fields. The code is all procedural with dozens of concatenated inline SQL statements.

As we weren't provided with an important ancillary application that runs off the same database, I've been tasked with recreating it from scratch. I'm a sole developer, which isn't even my primary responsibility as at least half of my time is taken up by operations stuff. There's an unavoidable deadline set for 30 days from now.

Despite my inexperience, I'm certain I could have designed this database and existing application much better than they were, but I don't really think it's realistic for me to alter the database, adjust the existing application, and be sure I didn't break anything while needing to create the additional application this quickly.

So let's assume I am stuck with the terrible database. Needing to work with such a bad structure, would anything I write that conforms to it just add to the heaping pile of technical debt to be shelved away until something completely breaks or new functionality is needed? How could I approach this situation and get something good out of it besides a hopefully functional application?

edit: In case anyone's interested, we ended up scrapping this horrible database and the application that ran on it. We outsourced the creation of the ancillary application (I wasn't involved in setting this up) to ultimately two different contractors who both ended up falling through on us, accomplishing nothing. I ended up having to rush out a horrific, partially functional hack of a fix in three days that's still in use today.

  • 2
    Just think how you would code against a library where the special case "2+2" did not return 4. It is not easy. – user1249 Jun 2 '11 at 14:47
  • 8
    so, what I'm hearing all of this is that you have 30 days to find another job. try careers.stackoverflow.com ;-) – Steven A. Lowe Jun 2 '11 at 17:27
  • possible duplicate of What is an Anti-Corruption layer, and how is it used? – gnat Dec 4 '13 at 11:38
  • @gnat: Not even close. – Robert Harvey Dec 5 '13 at 20:04

There's hope, but it's an uphill battle, especially if nobody realizes the database design is horrible. You can try to abstract the nastiness away with abstraction layers, but chances are it won't be worth the battle.

My advice would be to create enough abstractions over the database that the application itself is clean and properly designed; that way if you can ever fix the database, the application won't be affected since it doesn't care how the database was designed.

This is the approach I normally use when dealing with a database that is in place and, more often than not, designed with zero thought. A few choice applications of the Repository or Gateway patterns, with some service layers to talk to the gateway/repository, should help to quarantine the poor design.

  • 1
    +1 I basically said the same thing in my answer before fully reading yours however I don't see a way to delete my answer since yours covers the same material. – Ominus Jun 2 '11 at 14:52
  • As I have commented to others making this suggestion, it is a good suggestion but it is highly likely that if the database design is crap then the application code is likely crap as well. I don't see the point of going to this trouble if the application code should be refactored as well. – maple_shaft Jun 2 '11 at 14:59
  • 3
    @maple_shaft Agreed but the OP says that he's been asked to create, from scratch, a new application that will interact with the database. In a case like that it does make sense to create the new application properly. – Wayne Molina Jun 2 '11 at 15:06
  • 1
    @maple_shaft, the only crap part of the application will be the part that interacts with the crap database. That's the point of N-Tier architecture and SOC. – StuperUser Jun 2 '11 at 15:31
  • 1
    @maple_shaft The goal would be to put the database into a "black box" of sorts, and give the application an interface to it that is more ideal and not necessarily representative of the database design. – Michael Dean Jun 3 '11 at 2:38

Build an interface layer that handles all the DB stuff then write your app to interface with that. In the event that the database ever gets "fixed" you just replace/update your "interface". This approach has saved me a ton of time when dealing with a bad database or a database that was feeding other applications and couldn't be messed with.

  • How can you delete your own answers or is this not a possibility? – Ominus Jun 2 '11 at 14:53
  • You can delete your own answers. You keep seeing them with a tinted background and it will say something like "deleted by owner". Other than you only people with moderator powers will see it. – Marjan Venema Jun 2 '11 at 15:02
  • @Ominus: It should be possible, but why would you want to? You've got 3 upvotes! – FrustratedWithFormsDesigner Jun 2 '11 at 15:02
  • 1
    @Marjan: Moderators and anybody with >10K rep. – Jerry Coffin Jun 2 '11 at 15:03
  • 1
    Why would you want to delete this answer? I think it's an excellent solution. – Jim G. Jun 2 '11 at 15:27

Ouch... You inherited a nightmare mess, you have 30 days to make it usable for your organization, and half your day is taken up with operations tasks?

I am sure you could refactor but certainly not in that amount of time.

To answer your question I don't think you can actually write good code on such a design. The technical debt is too much already. If I were you I would hack in the features that I could, and press for a complete refactoring at a later date when you have more time and people on your team to better address it.

Just be careful pushing for refactoring. Sometimes a higher-up will make a decision to buy a products source code and ownership rights and they don't want to think that they completely wasted their money on garbage. This was the case for me at one job I had. Unfortunately managers make decisions on buying software like this and they never get technical involvement to evaluate what they are purchasing and see if it is maintainable and has a large amount of technical debt. In this case the bad decision is a political one and pushing for refactoring can put your job in jeopardy.

  • Despite everyone else's unfamiliarity with programming I've made clear how the quality of this codebase will affect maintainability. They're on board with a huge refactoring effort to get everything in a more suitable state so I'm not at risk of jeopardizing my job, but I don't think it's going to happen this month. – John Straka Jun 2 '11 at 15:01
  • 3
    Sometimes doing a succesful hack as your first experience with the project can give you the credibilty to refactor in later projects for the same application. Sad but true. First they have to believe you know what you are doing before they will consider any radical change. The poster is in a tough spot to be sure. – HLGEM Jun 2 '11 at 15:02
  • 1
    @John, This is good that they RECOGNIZE the need for refactoring. It is a sign of good long term management and the first step to actually refactoring. – maple_shaft Jun 2 '11 at 15:08
  • 3
    +1 for a great, realistic answer. You have a month, but really only half a month because you work half-time on operations. That's 11-15 days depending on whether you take off weekends. I hate saying it, but I agree that your best bet is to slam together something that works asap and make notes on how to improve or rewrite it later, especially since your management is onboard with refactoring. – Bob Murphy Jun 2 '11 at 17:42

Databases can be refactored just like other code. Fix the part that is affected by the code you need to write and write tests to make sure nothing else breaks. Do one small bit at a time just like any other refactoring. There is a good book on database refactoring that might help you get started on cleaning up the mess. http://www.amazon.com/Refactoring-Databases-Evolutionary-paperback-Addison-Wesley/dp/0321774515/ref=sr_1_1?ie=UTF8&qid=1307025831&sr=8-1

There are others as well, but I personally have read and worked with the techniques in this one.

And don't forget you can restructure the way you need to query the database by creating views to do the nasty transformational work for you into a sturcture that is easier to query.

  • This is all well and good but I have the strong suspicion that if the database design is a mess then the application code is probably worthless too. – maple_shaft Jun 2 '11 at 14:56
  • @maple_shaft, that could be although it is my experience that even good application developers design horrible databases. Either way only gradual refactoring will fix the mess, he can't just replace it outright even though I'm sure he woudl like to. – HLGEM Jun 2 '11 at 15:00
  • +1 @HLGEM, These are good points. Your advice is sound if the application code is well designed. Refactoring in parts is probably the best way to go but in my entire career I have never seen that work successfully. It may have been because of poor project management however, not because it is an unsound idea. – maple_shaft Jun 2 '11 at 15:03

To get the biggest bang for your buck, create updatable views to restore some of the "relationalness" and to provide more meaningful column names.

  • This would be a good start. If the database is denormalized, then I would add triggers or stored procedures to insulate the application from the denormalization. – kevin cline Sep 29 '11 at 16:18

One possibility would be to set up a second database that's structured (at least closer to) how you'd like it, and set up replication between the two databases. Then you can write your code against the new database and still leave the existing database (and application) intact, to be dealt with when you have more time.

Truthfully, it's still open to a lot of question whether you can do that in ~15 days of work. In particular, it's likely to depend on whether you need two-way replication (i.e., your new application will actually update the data) or only one way (your new application just lets people view the data). The latter case is (of course) dramatically easier to deal with.

If you need two-way replication, chances are that it's just not enough time to do the job. In particular, two-way replication that involves transforming the structure substantially is never trivial, and available tools often support it quite poorly (e.g., having to hand-write all the SQL for all the data transformations in both directions).

If you only need one-way, it's right at the point that it might border on being possible. It'll also depend quite a bit on whether you're allowed to spend any money or not -- there are quite a few data warehousing applications that are intended for exactly this sort of task that would probably make it quite a bit quicker and easier to manage -- but most of them are not cheap.

  • +1, This can be a good idea too as long as all application data access code is properly separated from other layers, and that data access code can be refactored to work with the new schema – maple_shaft Jun 2 '11 at 15:07

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.