Re: Allowing Custom Fields

On Friday 27 January 2006 08:25, Aaron Colflesh wrote:
> 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).

You could have the two tables linked with a key, say

table A ( custom_key int )
table B (custom_key int) <- and this custom_key references A
I'd probably go for a view that is recreated by a trigger on table B.
Second alternative would be to just use a join on the tables. I don't know
what kind of reporting software you use, but I don't know any that can't do a
join on two tables. The worst case scenario would look like

SELECT a.*,b.* FROM a JOIN b ON b.custom_key=a.custom_key

that will give you one result set.

There is a third option. If you know the maximum number of custom columns and
possibly their data type, you could add those columns statically, like in

table B (custom_key int,
cust_field_1 int,
cust_field_2 int,
....
....
)

and then use a third table to label the custom fields, aka

table C (cfield1_label varchar(80), cfield2 varchar(80) ....)

Your application then can grab the label for the field dynamically and the
fields in table B wouldn't have to change at all.

>
> 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.
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo(at)postgresql(dot)org so that your
> message can get through to the mailing list cleanly

--
UC

--
Open Source Solutions 4U, LLC 1618 Kelly St
Phone: +1 707 568 3056 Santa Rosa, CA 95401
Cell: +1 650 302 2405 United States
Fax: +1 707 568 6416