Re: SQL query

On Friday 11 February 2005 11:46, Richard Huxton wrote:
> David Goodenough wrote:
> > I realise this is not strictly a Postgreslql question, but if the best
> > way to solve it involves using PG extensions, such as the PG procedural
> > languages I am only going to do this on PG and so I am happy to use them.
> >
> > I have an address table, with all the normal fields and a customer name
> > field and an address type. There is a constraint that means that the
> > combination of customer and type have to be unique. Normally the
> > only record per customer will be of type 'default', but if for instance
> > the customer wants a different billing address I would add in a second
> > type='billing' address record.
> >
> > I then want to join this table to another table, say an invoice table,
> > and I want to use the billing address if present, otherwise the default
> > address. I do not want to create either two addresses or to put both
> > addresses on the invoice.
>
> Not sure whether a schema change is possible for you, but you might want
> to have two tables -
> addresses (customer_id*, addr_id*, ...)
> addr_usage (customer_id*, addr_type*, addr_id)
> Add a custom trigger that ensures for every customer_id there is a valid
> row in addr_usage for each addr_type (sales, billing, shipping etc).
>
> That way you can have any mix of addresses you like, and it's explicit
> which address is for which purpose.
Interesting idea, I will consider this.

Thanks

David