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
