Search Postgresql Archives

Re: [PG9.1] CTE usage

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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



-- 
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general




[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux