Hear me out. This is either an interesting idea or the Vicodin the dentist gave me yesterday is messing with me.



I am writing an app that fills in insurance policies using PDF forms. The forms are very structured in that Additional Insured - for example - may have 12 fields named ADDINSURED1 ( to 12) on the PDF form. There are perhaps 100 distinct fields like this on the 50 or so forms that need to be filled out, and some fields appear on more than one form. There are potentially, therefore, in the normalized tabular data world 100 child tables of the policy table. But the number of possible children is quite fixed ( by the PDF forms - which I do not in any way control ) and is always between 2 and 21.



All data entry is done on the policy form (and in the future options will exist for reading / translating data into the policy storage structure from other systems ). Then about 50 forms are all filled out, another 50 static PDFs added, all the forms sequenced a certain way and it is all joined into one big PDF. ( iTextsharper is the indispensible tool for all this and once you get the hang of it it is quite elegant and very fast )



Creating a data-entry interface for this is the challenge, and it is preferred that there be some other approach than 100 child tables.



I have chosen, so far, to use multiline text boxes for the data entry, and then parse the rows into the fields for that datum so that a textbox for Additional Insured could hold up to 18 lines and would fill the 18 text fields on the form.



This is working quite well and the client loves it.



The problem of course is that since the data is just string, dates, currency amount, logicals etc cannot be easily validated and interface gives no help.



But if I could store collections of however many items there were to enter and then easily bind to the collection items, it would be ideal



For example, a possible 5 dates in a collection would be stored in one sql column - a byte() {} I suppose ? - and then read out and bound to 5 controls on the form.



My question is has anyone done anything like that and are there any hints for an elegant and reasonably generic way to accomplish this collection binding ?



Ideas would be appreciated. This is a situation where there is enough of this kind of stuff that it would be worth figuring out, but I think on an instance basis it could come in handy in a lot of places.



Thoughts?

We handle this using the serialized objects method Greg mentioned. The two places that we run into the need for this kind of storage in our medical application are with insurance claims and patient statements.

Insurance claims sound similar to your needs. There are a large number of data elements that correpsond to static locations on a "standardized" paper form, or to fields within a corresponding electronic format. Rather than make a complex database structure to try and mimic those paper forms or electronic format, we created a "Claim" object that organizes the data in a way that makes sense to us.  We populate and pull from that object directly, and just serialize the entire object to a byte array in SQL.  That allows claims to be persisted in the database, and we are just dealing with our object when we write the logic to output to a claims form or electronic file.