How to fix a HUGE Design issue? - Implement "Aditional Properties" ?

Hello...

I have a DB which has an "questionable" design. This design is presend in several tables but lets look at one of them


It contains a table Custumers

The Custumer table has a bunch of fields including a Primary key GUID (All PK's are GUIDs) a bunch of "Real" fields and this horrible "AP" This AP field is a ntext field (No its NOT a Varchar()) that will contain an XML fragment...

For the Custumer this "fragment" contains 20+ seperate Values formated like this: (The fieldnames are different then here)

<Value1>1234</Value1><AnotherValue2>1234</AnotherValue2><SomeValue3>1234</SomeValue3>........<Value24>1234</Value24>

Now we have a serious Problem. Some information inside that xml fragment is very important and is a selection criteria for several reports.  

Since there are over 150.000 Custumers in that table this has some serve side effects since this results in a table scan and parsing the AP field for each and every client...

So my Question:

-----> How do you fix that and why would you do it in the way suggested?

My aproach would be creating an AP table to hold this information so i can index it correctly. The AP table should contain 4 fields :

SourceTable (int),
PK of the source table (Guid)
Name of the Field (Varchar)
Value of the field (Varchar)

This would allow decent indexes on this field and basically allow me to build the table as one giant clustered index covering all fields in the way i need...

Pro: Speed, only one table for all other tabels, AP fields need to be parsed only once
Con : VERY ugly querries with joins over 3 fields to retrieve one value, All querries must be altered to honor the lookup table

Anyone got any other (Maybe simpler) suggestions? - The above suggestion looks good so far but it would require extensive rewrites of most report procedures

Start Free Trial