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.
--
Richard Huxton
Archonet Ltd
---------------------------(end of broadcast)--------------------------- TIP 4: Don't 'kill -9' the postmaster
