Hello folks,
I've run into a challenge that doesn't appear to have been discussed in
the archives anywhere.
I'm designing a database that users need to have the ability to
customize some. They just need the ability to add extra fields to an
existing table (oh and they can't touch the predefined fields). So the
database schema so far is table A (existing table), table B (contains a
list of custom field names and other meta data) and table C
(intersection table between A & B containing the values for the custom
fields for each row). That works really well and all but we have
problems with retrieving the data. Due to other requirements related to
reporting we need to be able to present the data in table A along with
any custom fields in a table as if the custom fields were actually
fields on A. I only know of two ways of doing this, and I'm hoping one
of you knows of a third way (I've tried to use a function to do it but
it just doesn't seem to work).
1. Build the virtual table outside the database in application code
2. Use triggers on table B to actually create and remove custom fields
on A as they are inserted/removed from B.
#2 would seem to be the simplest except I'm really not too keen on the
idea of manipulating a table like that on the fly (even though I did
proof of concept it and it seems to be simple enough to be fairly safe
if adequate checks for entries on table B are put into the system). Does
anyone know of a 3rd way of doing it? It seems like this shouldn't be an
all that uncommon task, so I'm hoping there is some slick way of maybe
putting together a function or view to return data rows with a flexible
field layout. So far all the in-db tricks I've come up with have
required me to know what the field names were to generate the final
query anyway, so they don't really gain me anything.
Thanks,
Aaron C.
