4

Firstly, some context.

I'm working in a project with more than 10 years of development and without any documentation. My task now is to create a comprehensive documentation for the database (it is a SQLServer database), including a dictionary with all columns of each table.

After some analysis, I found some obsolete columns in the database with a lot of problems like the ones below:

  • Names without any semantic meaning (e.g "Value" column);
  • Columns with all lines set as "NULL" (probably not used anymore);
  • Columns created long ago for some test or for some specific task (e.g. "test_3" and "client3_sync" columns);

In short, the database is a mess and I'm thinking about some way to fix it. I found this link about database refactoring and I have two questions on this:

  1. Should I refactor this database? What are the problems in leaving this obsolete and unused columns intact? After all, they are not used.
  2. How should I proceed in the case of refactoring? Is this a standard approach? Why not just delete the useless columns?
||improve this question
  • 2
    If your task is to document, why are you contemplating a redesign? – Robert Harvey Dec 16 '16 at 16:34
  • 7
    So after 10 years they decide to document it? Go, find another job. – qwerty_so Dec 16 '16 at 16:41
  • 7
    A farmer's son once asked him about removing an old fence tucked away in an obscure part of the farm. The farmer replied, "Son, once you can tell me why the fence was put there originally, we can discuss removing it". – Dan Pichelman Dec 16 '16 at 17:00
  • 1
    What gives me a warning sign is that you did not write anything about how the database is accessed by the applications which connect to it. Gives me the impression you don't know it, or you think it is not so important for making the decision for refactoring. But in fact, that should be the primary condition for your decision. – Doc Brown Dec 16 '16 at 19:42
  • 1
    The farmer's son removed the old fence anyway to see if any sheep would scream and lo and behold, none did – Brad Thomas Dec 16 '16 at 20:10
2

The key to a refactoring is to not break the interface. A database has a second concern of not losing the data as well. If your sql server database is primarily tables, you can assign the actual tables to a different schema (probably one that is more descriptive than dbo) and then create a view with the old schema.TableName.

Now you have a way to buffer changes being incrementally made to the application from the old to the new system.

Example: Unused field elimination. Start with the part(s) of the app that modify data. If this field has no modification code, you can drop the field from the table (the one with all nulls), but in the view:

Select Null as OldFieldName from NewSchema.OldTableName;

I consider this view, to start being self-documenting code. If you're going to start documenting column names, why not start the process of giving them meaningful names?

Example: Rename Column It wouldn't be that difficult to rename the column in the table, but maintain the old name as an alias name in the view.

Select NewColumnName as OldColumnName from NewSchema.OldTableName;

No reason you couldn't do the same with tables.

The application can now be incrementally modified to transition from the old table names and columns (The View) to the new table and new columns (unless dropped). The view can then be dropped with the code in your source control history if anyone wants to see what the old table looked like.

Moving columns to a different table can be handled in the view, but with sql server a view is not considered updatable, if it attempts to modify data in more than one table at once. One way around this is to keep the old column, but put a trigger on the table to automatically copy any data from this column to the new column in the other table. After all the app code has been modified to use the new column, the old column and the trigger can be removed.

Remember, there's more to the database than the schema, so you have to account for data changes as well.

Taking responsibility for your database without breaking how it is being used is a tough job. I think this method gives you the change to have more control and understanding of the database as you give things appropriate names and coordinate a strategy with the dev team to get rid of things you no longer need.

||improve this answer
7

You certainly don't want to change a database without authorization and understanding of what is using that database. I've seen code implementations that hard-code the order and number of columns, and so much as removing a column would cause the whole darn thing to explode because now col[4] is something different, or there is no col[9] (and they don't check indexes before access).

I've commonly seen junk like this:

// DB: ID - FIRST_NAME- TEST_FLAG - LAST_NAME

cols = get_cols(result);

firstName = cols[1];
lastName = cols[3];

Delete the unused TEST_FLAG column, and kaboom! Or worse, if the last column isn't in use and you change a middle column suddenly things will be getting assigned wrong - like having private information assigned to public data fields. Switch the DB schema to something like this:

DB: ID - FIRST_NAME- TEST_FLAG - LAST_NAME - USERNAME - PASSWORD

...and uh oh, now everyone's password is being exposed if you have any publicly accessible list of usernames or something, because now cols are switched over 1.

Bad code, yes. Real code? Sadly, also yes. You don't want to be the one who crashes an entire system because you took an unauthorized guess.

Another, less terrible, example is with object-relational code, where TEST_FLAG could be mapped to an object, and even though it isn't used the code still expects it to be present and will error (maybe not gracefully) if there is a mismatch.

Now, there's nothing wrong with documenting this system and making notes for improvement, and then discussing a refactoring phase where you migrate to an improved system. But you'll need to extensively test the system, as this kind of nonsense can be present anywhere in any system that uses the database. Most automated/intelligent IDEs can't catch this kind of error either, as they aren't setup in ways that know the database schema.

All of this can be addressed with better, more resilient code, but a lot of developers have never learned to make code that doesn't break with the slightest change to a database schema.

||improve this answer
7

Your task is documenting. So stick to it.

Given the mileage of the database, the probability that your see - and disagree - with some aspects of it is very big.

So while you are on that task, try to came up with the documentation - actual state - and your considerations - how can it be improved - at the end of the task.

IMHO they are trying to know how things are right now to trace a strategy to improve it.

||improve this answer
  • Indeed, You are right, but I'm working in a small team and probably we will be responsible to define and implement the strategies for improve this product. I'm just thinking about it while finish the docs. – James Dec 16 '16 at 17:26
  • 2
    You have to split both your task and your question here in two. ;) - First document. After comment over the documentation. Keep in mind - as @BrianDHall said in answer bellow, you will have do go a littlle more deep in the codebase than just the database to have a well documented project. – linuxunil Dec 16 '16 at 18:03
4

Assumed you have permission to change the database, you should only refactor it there is enough budget, not only for refactoring itself, but for a reliable (!) test after the change. Also, estimate the risk for some downtime of your production systems when you discover something will not work any more after the change, and make sure your company can afford this.

These points are which make a refactoring of a ten years old db often much more expensive than expected. Deleting the database column is the easy part. Getting some confidence this change will probably not break anything is easy, too. Making sure you really did not break anything is hard!

||improve this answer
4

1) Should you refactor?

This depends very much on your workplace culture. Some organizations expect developers to take ownership of any problems they discover and take responsibility for finding a solution. Other would expect you to do only what you have been tasked to do (document the database) and absolutely no more. In any case, refactoring a database is a somewhat risky operation, so it is a good idea to at least inform and get buy-in from the teams which will be potentially affected.

Reading the other answers is seems the second kind of organization is the most common. My experience is start-ups and smaller organizations are often of the first type, while larger enterprises are more of the second type. I think this is a good opportunity for you to discover which kind of organization you are in. So ask your superior or your team what action would be expected to perform when you see such a problem - start fixing it, report it to someone else, or just ignore it and focus on the task you were assigned. Understanding these cultural factors will help you a lot going forward in your job.

Of course, if you don't have a technical minded superior which is able to make this call, then you have to make the call yourself, and thereby decide what kind of organization you want.

2) How should you proceed?

Establish an overview of all applications which use the database, including reports, scheduled SQL scripts and anything else which may be affected by the database.

Identify how these applications access the database. Through an ORM layer? Through direct SQL queries? Through stored procedures and views? Dynamic SQL? Is select * from prevalent?

Your approach will very much depend on the answer these questions.

If you have a single application accessing the DB through an ORM then it is easy: You can identify through code analysis if the code in any way depends on the obsolete columns, and if not, remove them. Renaming is a also fairly easy as you just need to update the mapping along with the database.

If you have multiple applications or applications generating SQL dynamically it gets more complicated. I would suggest creating a set of views representing how you would like the database to look (removing obsolete columns, renaming columns etc.) and then you can gradually migrate the applications to use these views. Then when all applications are migrated, you can change the underlying table to mirror the views. The type of database system you use will affect to what extent you can update or insert into such views.

I could go into much more detail, but without knowing more about your particular setup it would be overly broad. Maybe if you amended the question with answers to the above questions, you would get more useful answers.

||improve this answer

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.