Обсуждение: Allowing Custom Fields

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.

On Fri, Jan 27, 2006 at 10:25:00 -0600,
  Aaron Colflesh <aaron@synthesyssolutions.com> wrote:
>
> #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.

Couldn't you let the user creating a view joining A and B?

Bruno Wolff III wrote:

On Fri, Jan 27, 2006 at 10:25:00 -0600, Aaron Colflesh <aaron@synthesyssolutions.com> wrote: 

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

Couldn't you let the user creating a view joining A and B? 

I have yet to find a way to make a query that will take the individual row values of one table and make them appear to be columns (either by themselves or as part of a join to another table). If someone can tell me how to do that, then yes a view would be ideal.
Thanks,
AaronC

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@postgresql.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

On Fri, Jan 27, 2006 at 10:40:05 -0600,
  Aaron Colflesh <aaron@synthesyssolutions.com> wrote:
> Bruno Wolff III wrote:
> >On Fri, Jan 27, 2006 at 10:25:00 -0600,
> >  Aaron Colflesh <aaron@synthesyssolutions.com> wrote:
> >
> >>#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.
> >>
> >
> >Couldn't you let the user creating a view joining A and B?
> >
> I have yet to find a way to make a query that will take the individual
> row values of one table and make them appear to be columns (either by
> themselves or as part of a join to another table). If someone can tell
> me how to do that, then yes a view would be ideal.

I missed that part.

There is a way to do this with one of the contrib packages (tablefunc).
To use a view though, you would need to know how many columns you were
going to get at the time you created the view. So this might not work
for you.