>>>>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 think multiples is a better choice, but what I meant was one table with fields like:
>
>iFK
>iType
>Bit
>Dec
>Int
>
>You'd still waste a tremendous amount of space, but each record would represent one value for the record the FK points to.
>
>
>Tamar

Thank you for clarifying.