From: | Ladislav Lenart <lenartlad(at)volny(dot)cz> |
---|---|
To: | David Johnston <polobo(at)yahoo(dot)com> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: [PG9.1] CTE usage |
Date: | 2013-09-16 15:47:22 |
Message-ID: | 5237280A.7070203@volny.cz |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On 16.9.2013 17:30, David Johnston wrote:
> 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.
Thank you for your insightful comments. I will give it a thought.
Ladislav Lenart
From | Date | Subject | |
---|---|---|---|
Next Message | Vincent Veyron | 2013-09-16 15:48:03 | Re: [PG9.1] CTE usage |
Previous Message | David Johnston | 2013-09-16 15:30:44 | Re: [PG9.1] CTE usage |