>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
>
>All comments highly appreciated.
>
>Alex
3 and 4 is a typical EAV design. This design has known problems (hard to work with), but in some cases it's used. I suggest to do some reading on EAV model before starting this adventure.
If it's not broken, fix it until it is.
My Blog