Since this is a db design forum, perhaps, the better question is what is the best/optimal table design for the problem I described above. Instead of trying to fix the symptoms, let's get to the source. I know needing more than 255 columns seem like something is wrong with the normalization process. But that's the best I could do else I'll end up with thousands of tables. I'm not sure which one is more evil.
So, here's the detail:
1) I have close to 500 business metrics(FieldName1...FieldNameN)
2) The data is collected monthly
3) That's why I want to create one big table with Dates, Field1,..., FieldN
4) Then I have a MasterAttributeTable which has the KeyNum(primary key/index) with a list of attributes. KeyNum, Attrib1, Attrib2, ... , AttribN
5) Then I have another series of tables(current design) or one BIG table(haven't decided on this design approach yet). The table name(s) is the KeyNum and it has the following FieldNames: Dates,KeyNumFieldA,KeyNumFieldB,KeyNumFieldC,KeyNumFieldD, which are different than the MasterAttributeTable
what's the best normalization approach? Or am I stuck with hundreds of tables approach?
========================================================
Also, I've been thinking if I were to use Access as FE and SqlServer2008 as backend, and a user select a bunch of items in a listbox, then how can I send it to a stored proc in SS since SS does not accept array of values? Or is there some other simpler way to do this?
========================================================
thnx