From: | David Johnston <polobo(at)yahoo(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: [PG9.1] CTE usage |
Date: | 2013-09-16 15:30:44 |
Message-ID: | 1379345444241-5771104.post@n5.nabble.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Ladislav Lenart wrote
> Hello all.
>
> I am curious about the following usage of CTEs:
>
> Imagine three tables:
> * item (id, item_type1_id, item_type2_id, ...)
> * item_type1 (id, ...)
> * item_type2 (id, ...)
> where
> * item_type1_id is FK to item_type1 (id)
> * item_type2_id is FK to item_type2 (id)
The mental model that comes to mind is:
item (item_id)
item_type1 (type1_id, item_id <FK>)
item_type2 (type2_id, item_id <FK>)
Or even better:
item (item_id <PK>)
item_type1 (item_id <PK; FK>)
item_type2 (item_id <PK; FK>)
You'd need a trigger on these tables if you want to enforce the "only a
single type allowed" restriction but otherwise this model is much more
usual.
The other mental hang-up with your model is that the "item_type1_id" has a
one-to-one relationship with the item_type1 table so that deleting the item
means it is OK now to delete the associated type. This is unusual.
David J.
--
View this message in context: http://postgresql.1045698.n5.nabble.com/PG9-1-CTE-usage-tp5771048p5771104.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.
From | Date | Subject | |
---|---|---|---|
Next Message | Ladislav Lenart | 2013-09-16 15:47:22 | Re: [PG9.1] CTE usage |
Previous Message | Ladislav Lenart | 2013-09-16 15:23:06 | Re: [PG9.1] CTE usage |