Re: Problem with left join when moving a column to another table

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.