Too many fields on my table but want to add more...

Question

I have a table in my SQLServer 2000 database that has in my opinion been poorly designed.

It has a large number of fields in it and needs refactoring. However until I can do this I have other projects going on and one them requires a relationship with this table.

I am really reticent to add a further field to this table at this time. (There must be ~130) in this current one but I need to finish this task before hand and I am thinking about a work around.

Is it good practice to create an extension table in this case? I'm thinking of one with a foreign key to the original table's primary key. Should I just add the field to the original table? Is 130 fields ok?

Answer 1

If you're definitely coming back to it later to fix it up it'll be ok. You need to get it out of the way for the moment right?

Hack in the extension table, make a note of it and come back and fix it up properly when you have the time.

Answer 2

130 fields is something of an accomplishment. Write a post here when you hit 200.

I may suggest (but won't insist) splitting the table into several smaller. Group the fields by their united use. If some part of the table deals with settings, make it a settings table.

Another option is to group fields by the use frequency. Make a small table for the most frequently accessed fields, and the ones less commonly needed can be shifted to the other table. Thus you will improve your performance metrics.

Answer 3

IMO 130 fields is way too many. It sure sounds like a poorly designed database (or a lazy developer :o))

If you're gonna clean up the mess later on I, personally, would add those fields. But only if I knew I had time to fix it soon. Adding an extension table sounds like yet another hotfix and the start of another thing to fix within 3-4 years.

You might want to add an settings table later on - or something similar. Or move the fields into their own tables later on, grouped by their function.

Answer 4

You can Split the Table NOW and create à View with the same Name As rhe old Table, thereby replacing it. Adding new Fields is nö Problem then. You Might want to create an indexed View to increase performance when using à View, however it increases Demand for Storage.

later when you have Time to, do your refactoring.

Answer 5

If the table is already denormalized, ie. not all of the 130 fields are identified by the primary key, then adding a new field to this table is not going to introduce any new problems you don't already have.

In fact I would suggest you do this rather then add a completely new table that may or may not have a valid relationship with the main table.

Before adding any new table, I would try to seriously tidy up the design by getting your tables to third normal form. Therefore just adding the extra field is a reasonable compromise between getting something done now, and then doing a proper refactoring later on.

Answer 6

Fix it now...

130 is to many IMHO, and if you let it slip, then you will never get time to fix it later on (unless it is broken).

Now you have an "excuse" to drive a real fix here. Instead of creating one extension table, try to get the "approval" to split the table propperly, since "a datarow in SQL Server can only hold 8KB"

Answer 7

The advice to fix it now is beguiling but probably misplaced. Adding a column to the existing table is the work of half-a-day. Whereas splitting the table into several smaller tables, building a view, migrating the data, regression testing ... that's a largish - and risky - piece of work, which is why you haven't tackled it before now.

Applying the latest change as a separate table adds extra complexity to the system without any benefit, except as a statement of intent. And, let's face it, until the width of the table becomes a performance issue or affects the application in some other measurable way, addressing it will remain at the bottom of the project's To Do list.

So, be pragmatic: add the column to the existing table. It rankles but the alternatives are worse.