There is an application that involves billing enterprise customers for the services their customers utilize. The database schema does not properly reflect the business model. For instance, on a given account, services may become activated and deactivated. There is only a column to represent when the service was deactivated and the code in place just updates that same row if the service is reactivated rather than adding a new row. Ideally, a row would be added when a service activates and updated when deactivated, and there would be a column to represent the activation date.

As it stands, there is no solid billing history for these enterprise customers, but there needs to be from here on out. Do we set a "cutoff" for the legacy model that was corrupted and wipe the table clean? Do we add the appropriate column and start working with the table properly and just check for rows that have a 'activated date' of null to detect legacy? There must be several ways of handling this, but I have no experience with this kind of situation and would like to hear out some various approaches.

  • "I'm pretty sure the code in place just updates that same row if the service is reactivated rather than adding a new row" Sounds like step one is making really sure that it's just updating the field rather than creating a new row. – Zareth May 24 '13 at 5:41
  • 1
    The way you asking implies you want to change big parts of the architecture just because one (minor?) feature is missing. Does not seem to make much sense. Can you clarify? – Doc Brown May 24 '13 at 6:15
  • 1
    "Ideally, a row would be added when a service activates and updated when deactivated." - I don't understand this - why don't you fix it when you already know where the problem is? – Doc Brown May 24 '13 at 6:18
  • 3
    This is all a bit of a rant and there's very little clarification about what's wrong (and what makes that wrong), as well as what sounds like a desire to rewrite most of the system? And the question is related to none of that? Or? What exactly are you asking? – Jimmy Hoffa May 24 '13 at 6:24
  • I have cleaned up and clarified my question. Please consider reopening it. – user44798 May 24 '13 at 15:27

"Of course the way things are now, there is no solid billing history due to the mistakes that were made."

As far as I understand your question, your database scheme is missing some features (like history information), and you have problems to change it because you don't know the full impact into your existing system.

One approach for dealing with that kind of situation is to solve the problem with upward-compatible extensions to your data model. For example, you need a history in your billing table? Add a new "billing_history" table, with exactly the same columns as your billing table, and an additional "record version number" as well as a "validity date" column. Then add an "after update" trigger to that table which makes a copy of the current billing record into the "billing_history" table whenever the "deactivated" flag is reset to "activated". Now, you can add new modules to search, display and access that history table, and you don't have to change anything in your existing modules.

The details may be different in your situation, but I hope you get the idea.

  • You could also have single billing_with_history table and make billing a view on that table. – Donal Fellows May 24 '13 at 8:48
  • 1
    @DonalFellows: this may work for some database systems, but I suspect this is bit risky because replacing a table by a view may lead to subtile incompatibilities. Nevertheless, this is just an example, the OP will have to work out the details on his own. – Doc Brown May 24 '13 at 9:46
  • @DonalFellows: I am not experienced with using views, but I will at least look into it. Thank you. – user44798 May 24 '13 at 14:35
  • @DocBrown: That sounds a like a good idea (an improved mimicking table.) Would you by chance happen to know of any literature regarding this kind of refactoring or 'upwards-compatibility', where the data source is touched? – user44798 May 24 '13 at 14:36
  • 1
    This is a good book to read if you are refactoring a legacy db: amazon.com/… – HLGEM May 24 '13 at 19:34

Your issue is not the database schema. It is the question where the code influences the data. If it is structured well you can just change code and schema. Implement a piece of code which handles missing history for old records and you are done.

The fact that you would want to put tricks into your schema or database to prevent any unknown issues means that the code view there is currently is not complete. So would focus strongly on having a good overview of where the code acts with the schema.

If you find it scattered all over the place then you might consider first improving the code structure instead of directly changing to the functionality you need. That will create a better end-result.

Why: Think about all changes you might have to make in future. If you need to apply tricks now you have to take care of them all the way along. If you fix it straight from the beginning in a right way it saves you time in the end without having to over-engineer.

Your Answer

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