Something that no one has mentioned is that you could use a single table that looks something like:

create table legacy_struct (

struct_name varchar(255) , -- the class name of the struct

struct_instance int , -- an identifier for the instace of the above class

field_idx int , -- identifies the field number in the struct (array index)

n_val numeric(10,18) , -- stores numeric values

s_val nvarchar(1000) , -- stores string/date values

data_type_id int , -- fk to type child table

s_len int -- length of string data (if needed)

)

The data_type_id field would point to a child table that tells you which of the two data fields, n_val or s_val, the data is stored in, and also what it was meant to be cast into, such as: int, money, float, etc for the n_val field; varchar, datetime, etc. for the s_val field. You could probably dispense with the n_val field on use just the s_val, but I prefer to store numeric data in a real numeric type when possible.

Now, it can be a beast to get things out of this, and some queries have to be written somewhat cleverly to avoid conversion errors (espcially when storing dates). The upside though, is that you can store anything in there, so long as it doesn't over run the maximum field size available, which I wouldn't anticipate being a problem anyway.

I have personally used this technique to store responses to a set of questions that could not be predicted when the database was architected. Rather than require somewhat more dangerous schema updates to add new types of questions, I implemented this with a couple of supporting tables so that new questions could be specified purely by adding new records to existing tables. It's probably a bit hard to show someone new how to use it, but once you get the hang of it, it's not too hard to deal with and it's extremely flexible. This complexity could be mitigated by writing a small application to handle the actual creation of the needed rows so that someone new wouldn't have to know too much about how the system worked; there's not much you could do to make querying it easier though.

Matthew Galbraith