>Hi Doug.
>
>IMHO, go option 2. The other options are neat hacks, no doubt, but may cause problems down the road when trying to efficiently create queries on the data. Why? Because you are going to have to interpret packed data row-based and lose the advantages of set-based mass queries.
>
>
>>
>>Ok, here's the situation and question;
>>
>>I have spent a couple days creating a table (amidst numerous interruptions <g>) that will store data that is intended to be used in a real estate form called in the industry as a 1003 form. A 1003 form is the 8 1/2" x 14" form you fill out when you take out a loan - usually an automobile or house.
>>
>>Anyway, FoxPro still has that pesky limitation of 255 fields per table and I'm just about to run into that limit.
>>
>>I got to thinking about this a little and wanted to see if any of you had any experience or opinions (I know there's no shortage of those! <g>) about how best to store large numbers of fields but potentially unknown numbers of fields. PLUS being able to 'field upgrade' the table.
>>
>>I see three basic options:
>>
>>1) Use memo fields, MLINE() and so forth and either use position (Nth line = field 'X') or 'wrapper' markers to marke each field. Extract & insert as needed but causing memo bloat.
>>
>>2) The 'traditional' method of a parent table and child tables for assets, liabilities, etc...
>>
>>3) Is to store the data 90 degrees from normal. That is, create a table that would hold a common record key and then each entry can be a row. This gives me virtually unlimited numbers of fields if needed or as few as one if needed.
>>
>>For example.. Calyx Software makes a product called "Point" which is a real estate loan management package. Heavily used in the industry, they have a large market share and, well, an interesting manner in which they store data. I've decoded their storage scheme and it is able to allow them to store up to some 65536 distinct fields, each 'known' by its position along with the length of the data. Very clever actually but showing its DOS roots. <g>
>>
>>I am leaning towards option #3 and am pretty convinced I could fill up a multi-page form fairly quickly but wondered what you thought. All I need is a routine that reads the field name and seeks it actually.
>>
>>Any thoughts?

Hi Doug,

There may be some logical point between 2 and 3 where it makes a good balance between set relationships and raw data. For instance you may want to store repeated known data in related tables but only store "extra" non-standard data in the memo array. As part of the record that hosts the memo field you would need to have a field which represents the max index so as to allow easy determination of any content.

Not pretty but practical.