Subject Firebird & User Defined Fields - Architecture Suggestions
Author martinthrelly
hello there. i am trying to work out the best way to utilise Firebird
in order to maximise performance for a certain business requirement. i
would be grateful for any advice.

i am developing a database that must allow for user defined fields. to
summarise, every CONTACT in my database has a CONTACT TYPE. and the
UDF's are set on a per CONTACT TYPE basis.

when the WinForm loads, my client wants to fill a grid with every
contact and their associated UDF's. exactly what information is
retrieved is based on a grid filter i.e. the user can choose which
columns of information they wish to display.

the main problem i have is how to join and return the UDF information
in an efficient manner. this is a problem because the UDF description
is a row value within the table, rather than a column header.

for example, lets say the user chooses to display only the grid
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.