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