Instead of CLIENT_UDF and UDF_DEFINITION just create CLIENT_UDF_INTEGER,
CLIENT_UDF_DATE, etc... Or something like that, right?

-steve

martinthrelly wrote:

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