Jason Long-2 wrote >> Jason Long-2 wrote > > > There is a unique constraint on the real price table. I hadn't thought > of how I will enforce the constraint across two tables. > size_id and area_id will have to be unique across both > t_price_base_table and t_price_original_with_area_id. I will want to > drop area_id from t_price_original_with_area_id. > > What is the best way to implement the cross table unique constraint? Don't. If size+area is a unique constraint then there should be a table that defines valid pairs and creates a PRIMARY KEY over them. Per my original comment your issue isn't JOINs (well, your biggest issue anyway) but your model. The fact that you couldn't write a good query simply exposed the problems in the model. This is not uncommon. I would need a lot more information (and time) than I have now to offer any design thoughts on your schema; though I do find the unique constraint over size+area to be unusual - as well as using that as a foreign key from the item table. You haven't specified the domain for this model but using homes as an example I would use a 'model' table with "model_id, size, area" as columns. A particular house would then link in "model" and "price". You could possibly further restrict that certain models can only sell for certain prices if necessary - in which case you would have "model_price" and possibly "house_model_price" tables (the later could be an FK). David J. -- View this message in context: http://postgresql.1045698.n5.nabble.com/Problem-with-left-join-when-moving-a-column-to-another-table-tp5760187p5760220.html Sent from the PostgreSQL - general mailing list archive at Nabble.com. -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
