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

On Thu, 2013-06-20 at 16:22 -0700, David Johnston wrote:

> 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.
>
>

David,

I really appreciate your help. I had not used WITH or any of the syntax
you showed me before. Pretty cool.
I normally just write a bunch of views to build complex queries.

Does the syntax you showed me have performance benefits vs joining a
bunch of views together?

I spent way to much time trying to get the query to work, and all I
needed to do was write a view

create or replace view price.v_offerprice_pipe as
select op.id,
op.price,
op.active,
op.stditem,
op.version,
opp.size_id,
opp.weight_id,
opp.grade_id,
opp.endfinish_id,
opp.manufacturer_id,
opp.condition_id,
opp.area_id
from price.t_offerprice_pipe opp
join price.t_offerprice op on op.id=opp.id;

This allowed me to move (price, active, stditem, version) to the base
table without breaking any of my views with very minimal change to the
view definitions.

I just had to replace any references to price.t_offerprice_pipe with the
view price.v_offerprice_pipe in any of the views that were complaining
about dropping the columns.

I decided not to move area_id to the base table for now. Without being
able to properly do a cross table unique constraint, it will stay where
it is currently.