Re: [PG9.1] CTE usage

Le lundi 16 septembre 2013 à 08:30 -0700, David Johnston a écrit :
> 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>)
>

I would suggest :

item (item_id <PK>, id_type integer)
item_type1 (item_id <PK; FK>)
item_type2 (item_id <PK; FK>)

where item.id_type is either type_1 or type_2

So that the application knows which table to use?

But chances are the OP can't change his model easily.

--
Salutations, Vincent Veyron
http://marica.fr/
Gestion des contentieux juridiques, des sinistres d'assurance et des contrats