--- In firebird-support@yahoogroups.com, "martinthrelly"
<martinthrelly@...> wrote:

>

Hello Martin,

Please do not abbreviate your application User Defined Fields to UDFs.
Firebird has User Defined Functions which are abbreviated to UDFs, so
you will get confused replies.

> for example, lets say the user chooses to display only the grid
> columns FORENAME, SURNAME and HAIRCOLOR (where HAIRCOLOR is a UDF). in
> this case in order to select the HAIRCOLOR alone i must be doing
> something like this:
>
> SELECT udfvalue FROM contacttype_udf WHERE contacttype = X and
> udfdescription = 'HAIRCOLOR'.
>
> this is ok when i try to run it as a standalone query, but i am having
> to do this for potentially a lot of UDF's and CONTACTS. any advice
> much appreciated. thanks.

The table design is mostly correct here. The only difference is that I
would normalise out the udfdescription into a second table and store
the udfdescriptionid instead.

I presume that although your customers define these custom fields, the
fields themselves are often used in more than one contact (otherwise
how would you even do a grid like you have described).

Inverting the table into a set of columns is really a function that
should be performed on your application server rather than at the
database level. SQL is not a really suitable language to do such a
transform. While you may be able to hack around using execute
statement inside a stored procedure, it will be much easier to do it
right.

Adam