How to handle column growth of wide, flat tables

Question

How would you DBA's handle this? I have taken ownership of an existing app (VB6) and database that was written in 1999. The database design is fairly 'flat', meaning the main tables are fairly wide (100+ columns) and developers have continued to tack on additional columns to the end of the tables. This has resulted in columns that have a lot of Nulls since they don't directly relate to the primary key.

I am considering splitting the main table out as a way to abstract myself from the years and years of 'column explosion'. I am certain that new fields will continue to be added as new requirements come up.

So the question is, as new fields are needed, do you continue to grow the width of the existing table? Or do you STOP extending an existing table and split it out into a separate supporting table that will house new fields, thereby creating a 1-to-1 relationship? If you were to split the main table, what would your naming scheme be?

Let's assume for this example I have a table called 'Foreclosure' with 150 fields. What is a good name for the new 1-to-1 table? 'ForeclosureExtended'? ForeclosureOtherInfo'?

By the way, there are Views and Stored Procs that will need to be modified to support any new tables, but that is inevitable anyway when columns are added.

thanks in advance for any thoughts.

Answer 1

80% of the time, your nulls have definite patterns.

These patterns define subclasses of your table. In your case, they will be subclasses of Foreclosure.

Your splitting should be based on these subclass relationships.

Say, for example, some Foreclosure instances have a bunch of fields related to legal proceeding that are nearly all filled in. And other Foreclosure instances have the legal proceeding fields entirely filled with nulls.

You have two classes. You need to work out the relationship between them -- are they superclass-subclass or are they peer subclasses of some other superclass?

This tells you how to partition your table to make useful stuff happen.

• You may have proper superclass subclass relationships

• You may have found a thing (a LegalProceeding) which should have been a separate table all along. It should not have been permanently joined into Foreclosure. This is remarkably common.

You now have some relational implementation choices.

• One common choice is to put all subclasses into a single, massive table with a lot of nulls. This is what you have today, and it isn't working.

• One choice is to split the two subclass relationship tables into peers, duplicating the common information.

• One choice is to have a superclass table with an optional FK reference to the additional information in the subclass.

• One choice is to have a subclass table with a mandatory FK reference to the superclass information.

Answer 2

Unless you are really brave, app is very small/simple, or there are major performance issues do not fix the schema. If it ain't broke, don't fix it.

Just create a new table ForeclosureExtended, as you suggest with the same key and start adding columns. Or, you could make proper tables with grouped columns as new columns appear. Either way, if the schema is this bad, I'll bet the code is very fragile.

Answer 3

Why do you feel that you have a problem? To my mind it's easier to deal with one table that has a lot of columns than it is to deal with a ton of narrower tables and all the associated views you have to maintain.