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. -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
