>>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.

Thank you for your input. I still have not decided which way I am going to go. Of course, a lot depends on whether the customer (who initially asked for this feature) will follow up with the order.