>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?
>
>TIA

I'm all about doing the right design, and the right code, which is not what most people are doing, male or female. There is a design called the one true lookup table. That design is bad. Lots of people use it. Some even promote it. Data modeling says you put like things into a table. Customers, People, Addresses. Lookups are conceptually the same - but that is playing loosely with the underlying concept of data modeling. What is a lookup? A customer can be a lookup from an invoice perspective - does that mean it should be in the lookup table? The minute one of the lookup table subsets gets a different field from the others, you've only proven the model is wrong.

Model your data correctly, and magically, the coding becomes far more reusable. Multiple tables isn't harder. Getting around a bad design is harder.