making a clean break from un-normalized wide table

Question

I'll try to keep this example as simple as I can. I'm trying to implement a domain-driven design (a business object to represent each table) in an old VB6 app which follows no OOP patterns. The existing code is written just as you might expect from a 10 year old VB app (i.e. use of ADODB.Recordset with no intellisense for fields). The existing code is non-OOP and I cringe at following its existing patterns. One of my challenges here is how to handle database changes as new requirements come up, without creating too much confusion to any developers who may look at the DB design later.

Suppose in this app we have a hypothetical 'Client' table that is clearly over-burdened as such: clientId ClientName ContactName Address City State Zip Phone CreditLimit CurBalance (and on and on and on...)

A new requirement calls for new financial fields: InterestRate Terms

Would it be acceptable to break InterestRate and Terms into a separate 'Fiancials' table, which may appear as a weird split since the existing 2 financial fields will exist in the original table?

Ideally the old financial fields (CreditLimit, CurBalance) would be relocated to this new table, but at the risk of breaking MANY parts of the app, it is not desirable to move fields. I just want to stop the current practice of making a table wider and wider.

Basically, I am thinking I want to leave old code/table design alone, make a clean break with new fields, and create domain objects to handle any existing and new tables i.e. Client object could expose a Financials property representing the new Financials table.

Is it a good idea to TRY to make a clean break or just add new fields to the existing muck? Is there a clever naming scheme for representing new tables versus the old? How do you DBAs make a clean break without ripping apart the existing design?

thanks for any thoughts

Answer 1

You could use views. You could make it so that the Clients view no longer exposes the financial information and the Financials view is created to expose the information. That they share the same base table matters not to the end users, because...

You then deny all access to the tables and only access through stored procs and views and then you can refactor your tables at any time.

Alternatively, you can rename the table and split it and make a view under the old table name.

An important thing to remember is that business objects do not map 1-1 to tables. There will be link tables for relationships (for instance client_account links clients to accounts, say), but there will be no business object for the relationship, instead the client object will usually have an accounts collection and/or vice versa depending upon the design constraints.

Answer 2

Depending on the database(s) you are accessing, another option involving views would be to replace the existing data model with a properly normalized setup and then create views with the names of the old tables. So if you have a CLIENT table today, you would split that up into whatever set of tables would be appropriate and create a CLIENT view that replicates the existing table structure. Assuming the view is updatable (this will probably require something like Oracle's INSTEAD OF triggers), existing code would simply access the views and would not require changes. New code could access the proper data model (directly or through another set of views that give you freedom to evolve the data model in the future without affecting application code). As you have time to refactor existing code, you can point it at the new data model.