Re: [GNUe] Inverse Master/Detail relations

[James Thompson]

> How do I set up forms for
> multiple tables linked by a master/detail reference, but where the
> reference to the detail-record is stored in the master table, as in
> the following example:
>
> CREATE TABLE customer (
>   id serial PRIMARY KEY,
>   name text,
>   address integer REFERENCES addresses,
>   ...
> );
>
> CREATE TABLE addresses(
>   adr_id serial PRIMARY KEY,
>   street text,
>   ...
> );
>
> I've allready a form with the master/detail realtion where the
> detail-table holds the reference to the master table working pretty
> nice. I think this case could be handled with triggers, but I would
> like to know if there is a standard way of doing it, before
> reinventing the wheel.

For existing records I don't see any problem using forms standard 
master/detail.  Just still use the customer table as the master.   Forms 
doesn't map master/detail to any type of primary/foreign key relationship in 
the backend.  Instead master/detail simple says if the master changes update 
the detail blocks to reflect the proper values.  So in that case I don't see 
an issue. 

Thinking it through I guess new records would cause a problem as I believe the 
master is always inserted before the details.  In these cases you would have 
to fall back to triggers to populate the key fields prior to committing the 
data.

I *think* something like this should do it if you're using a db that supports 
sequences.

<trigger type="PRE-INSERT" name="fldAddress"><![CDATA[
# Set default value
if self.isEmpty():
  self.autofillBySequence('seq_address_id')
]]></trigger>

But I don't have a working form with this logic so it may need a little work 
to make it happy.

Hope this helps,
James