>>>Hi,
>>>
>>>I am posting this in VFP forum but the backend is SQL Server database.
>>>
>>>I want to ask your opinion on the following DB design consideration.
>>>I need to create a table (which will be a child table) that will store different value, depending on the Parent PK
>>>For example. Some records of the table will have 2 INT columns, 1 Bit Column, 4 Decimal columns. Other rows/records will have different number of types. And the captions to the columns (the way they will be viewed by user of the VFP app) will be different.
>>>I am thinking of having a set of different type columns, as follows:
>>>BIT_VAL 1 Type: Bit
>>>BIT_VAL2 Type: Bit
>>>BIT_VAL3 Type: Bit
>>>DEC_VAL1 Type Decimal
>>>DEC_VAL2 Type Decimal
>>>DEC_VAL3 Type Decimal
>>>DEC_VAL4 Type Decimal
>>>INT_VAL1 Type INT
>>>and so on.
>>>
>>>So, for each different Type the table will have a max number of columns. This way, the UI will take care of Captions and matching the Captions to the Col Name/Type.
>>>
>>>What do you think of this approach?
>>
>>I wouldn't. I'd use a one-to-many approach of some sort, with one record for each value. Since the values can be different types, you'd either need multiple tables, or for each record to have one field for each possible type, and another field that tells which column is in use.
>>
>>Tamar
>
>Multiple tables is what I want to avoid. The second part of your suggestion, in general ("for each record to have one field for each possible type, and another field that tells which column is in use.") is what I thought I described above. Although I am confused as how you mean by "for each record to have one field for each possible type".

I'd STRONGLY argue for Tamars multiple table approach. If there is OVERWHELMING reason to implement different, I'd go for a memo field and fill it with XML or JSON "object" data. If the table is critical/central, perf might make is "sensible" to denormalize.