The web application I am working with has a huge Database that is a real mess, no foreign keys, no constraints, no indexes, no optimization, no normalization, nothing. Until now, the logic has been: If you need a field, add it, if you need a table, create it as fast as you can. At the beginning, I couldn't understand the severity of these mistakes, but having to deal all the time with obstacles created from a bad database, has made me decide to re-design the entire database. What i would need to know is:

  1. Where should i start, normalization, adding keys and indexes, or anywhere else?
  2. Since the cost of these changes will be huge for my work (I will have to redesign the entire code from scratch to adapt it), is it worth? I mean, what are the cons and pros of such a huge thing (I say huge, because I am a junior level in database matter). If anyone could guide me, would be awesome
  • 4
    First my sympathies. A mess that was made over several years is seldom less than several years work to clean up. You probably are not free to redesign the schema unless you redesign the applications at the same time, unless the code is not in use in a commercial environment, and even then, it sounds like you're asking a question which is more or less unanswerable on a Stackoverflow style format. Please don't take it badly if someone downvotes or votes to close your question.
    – Warren P
    Sep 28 '15 at 13:45
  • @WarrenP well, thank you for the honesty, actually I am pretty sure that one of the above mentioned will happen, so, I would like to specify something. The application is online, has its amount of customers, and if I have even thought of redesigning the database is because we would like to re-brand the entire product, and I am thinking about proposing an entire re-design in database and application code. Probably the question has no answer in SO, but even some advice would be appreciated. But however, each downvote is a lesson, right? :)
    – Ange1
    Sep 28 '15 at 13:51
  • 2
    The question is probably going to be marked as too broad. It will ultimately become a business decision, not a technical one. The decision will be weighed against losses in revenue vs. higher costs.
    – Queue Mann
    Sep 28 '15 at 13:58
  • 1
    It sounds like your company doesn't even want to use an SQL database or relational technology. If you work in a domain where a non-relational database might actually suit your domain and your requirements, consider Couchbase and MongoDB, as it sounds like your entire company and history has been to use an SQL database without regard for relational integrity, normalization, or any other thing that SQL technology excels at. For web -developers who don't want to employ DBAs, or to understand SQL best practices, maybe it's best to just use a document key-value store.
    – Warren P
    Sep 28 '15 at 14:01
  • 2
    @Ange1 To have any chance of anyone listening to you, you have to provide hard numbers. In other words, you need to establish baseline performance numbers. Sure, people can talk about database theory about when to normalize and when not to, but without knowing your specific system and all the use cases involved, it's impossible to identify that "this is a pro", "this is a con." That's why I say your question is too broad.
    – Queue Mann
    Sep 28 '15 at 14:19

Generally speaking when you want to fix a big-ball-of-mud architecture it is best to focus on one small aspect that you can fix relatively quickly with maximum payoff. What that thing is will vary depending on the system. Once you identify that thing then implement it and look for an opportunity to fix something else. Over time your big-ball-of-mud will get more and more under control. This is assuming that the rest of the team is on board with fixing the mess you're in. If they aren't committed to moving away from the status quo then you'll be fighting a battle you can't hope to win.

Adding a few indexes seems like a quick and easy win, so that will probably be a good place to start, but YMMV.

In parting keep in mind what Warren P mentioned in a comment:

A mess that was made over several years is seldom less than several years work to clean up.

You and the rest of the team need to be committed to this clean-up over the long term if there is any hope for success.

Responses to OP's comments:

Wouldn't it be better to start with database normalization? I mean in a development environment, in order to end up with a fully structured database? There are like 6 tables which HAVE TO be separated, in order to be more "elastic" for future requests.

Normalization is likely to touch many queries/statements across the system. That is a very intrusive change that will take lots of time and effort to show a benefit. Making small incremental steps will be easier, quicker, and steadily improve your life. Before considering normalizing I would move all data access into stored procedures. That will give you tangible benefits quickly, allow you to spread out the work, and make the normalization step easier.

[T]he entire team is ready, and we are talking about a 6 month - 9 month project of restructuring everything, the big bosses are still undecided however.

Read this post by Joel Spolsky before advocating stopping everything and pursuing the grand redesign. Blocking updates to the existing system while you restructure the old system over the next 6 to 9 months makes it hard for the business to succeed. Supporting the current product while concurrently improving the same code base is the smartest play IMO (in my opinion). In this way the code base improvements will just be a symbiotic extension of supporting the current product.

Note: I'm not suggesting you have one team doing a rewrite (version 2.0) while another is supporting the current system (version 1.x).

  • I have read the blogs you sent, and I think I will have to think a lot more now, in order to get the right decision. Even if we wanted, for now we could not afford having 2 teams taking care of the issue, so we have to separate the current team (as the best option). Thank you for everything!
    – Ange1
    Sep 28 '15 at 17:13

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