>>>Hello,
>>>
>>>In the past I have broken data normalization rules but I am swearing that off :) I also want some table columns to be defined in a data driven way.
>>>
>>>I'm asking for help to see if I am missing something important. Please give me your comments and criticism as I need to make a choice soon and don't want to make a bad one.
>>>
>>>Say you have the following tables:
>>>1. Header Table
>>> header_id, other fields
>>>2. Child Table
>>> child_id, header_id, other fields
>>>3. Table with name and value of "virtual fields" of child table - One record for each virtual column that has a non default value
>>> Virtual_id, child_id, ColumnName, ColumnValue
>>>4. Data dictionary with name of all possible "virtual fields" of child table - Contains description of all possible virtual columns
>>> ColumnName,ColumnType,ColumnWidth,ColumnPrecision
>>>
>>>I am considering the following approach to integrate data from tables 2 and 3 during user edits:
>>>I use Codemine framework which makes it relatively easy to go the cursor approach.
>>>- Create either an object or a cursor with the following properties / fields
>>> child_id, header_id, other fields from child table, VirtualColumn1, VirtualColumn2, VirtualColumn3 etc
>>>- Update base tables as user makes changes
>>>
>>Instead of step 3 you might consider the route of NoSQL,
>>which can to a part be simulated in vfp utilizing 1 memo field filled with key/value pairs
>>
>>Formats well adapted are Ini,JSon or Xml.
>>Saves a lot of Join work but leaves you with packing worry for high-write setups.
>>There is no rule that you cannot employ DD setups like step 4 such NoSQL/EVA hybrids
>>- I even think it is a good policy ;-)
>>
>>regards
>>
>>
>>thomas
>
>Thanks for the lead, Thomas.
>
>I have been considering the approach described in points 1-4 above because I want table structures that are stable yet adaptable to new situations. In the past I have modified table structure regularly to accommodate new needs and that is not satisfactory.
>
>Now I have a new application and I want to follow best known practices. I thought the approach I was considering was a "best practice" but I have heard enough negatives now to keep looking for better ways.
>
>The way you suggest sounds flexible, fast and easy. You say you think it is a good policy. Can you comment some more on that?

Sorry for the delay...
I view all the NoSQL approaches as possible, but think they should be used ONLY if most of the properties will remain empty (>90%) in the table. Vfp is plenty fast to seek in added tables and even if you are using an activerecord-pattern similar approach anyway, via scatter name additive this is faster than parsing the memo field.
In my case it eliminated 3 tables in which on the average only between 30 to 40 properties total were actually filled - and LAN access was way slower, so the the object-text transfer was minimizing index traffic.

Just to save some headaches for db structure update IMHO is ***not*** the best reason to go ANY NoSQL way -
my guess is you are better served using xCase or Stonefield to bulletproof your update mechanism if you already
plan to have DD description of possible data.
NoSQL might make sense if clients can enter any key-value pairs you have not forseen.

ANY NoSQL approach forces you into the activerecord pattern,
wheras IMHO in pure vfp solutions the biz tier should work on cursor data as data store & datasource for GUI elements if possible
- vfp was designed around then..


"My" approach is IMHO better suited to vfp with activeRecord than most of the other NoSQL approaches -
but if you have to create statistics on items, use your approach, as SQL gives you that easily with an added table.

not totally on topic, but might push you into the frame of thinking on DB strucs:
http://weblogs.asp.net/manavi/archive/2010/12/24/inheritance-mapping-strategies-with-entity-framework-code-first-ctp5-part-1-table-per-hierarchy-tph.aspx
as well as
http://weblogs.asp.net/manavi/archive/2011/03/27/associations-in-ef-4-1-code-first-part-1-introduction-and-basic-concepts.aspx
each with the follow ups.

HTH

thomas