hi there

im in a pickle regarding an architectural solution for our Firebird
database. please let me explain. this may be slightly long winded so
forgive me if you get bored. :)

our application allows users to create user defined fields. So we have
this kind of structure:

CLIENT
------
ID

CLIENT_UDF
-----------
ID
ID_UDFDEFINITION (links to UDF_DEFINITION)
ID_CLIENT (links to CLIENT)
VALUE

UDF_DEFINITION
--------------
ID
DATATYPE

so far this has worked well as we have always loaded all the client
data into our grid. i.e. I just did a join in code. now we are trying
to scale up by implementing a search facility with order by
functionality and paging.

I have now found that this architecture becomes clumsy for paging and
ordering. for example, if a user decides to search on a UDF DATE
column e.g. 'WHERE UDF_VALUE > '01.01.2008'. then we cannot do this as
the datatype is held in another table. casting seems clumsy for each
row. also the fact that the UDFs are rows rather than columns means I
seem to be stuck SQL wise without some kind of cross tab query?

i have contemplated radical solutions like building an extra table
with the UDF data lay flat in a BLOB. but i still cant effectively
order this without substring.

this needs to be fast so i think it needs to be driven by SQL.

i hope you understand what i mean here and am grateful for any help.
thanks