MIME-Version: 1.0
In-Reply-To: 
 <CAAY=A7_wHBKkKFxuxXc7A+fr3TYGmWS9-hCGghLRDcQAAa_wfQ@mail.gmail.com>
References: 
 <CAAY=A7_wHBKkKFxuxXc7A+fr3TYGmWS9-hCGghLRDcQAAa_wfQ@mail.gmail.com>
From: "David G. Johnston" <david.g.johnston@gmail.com>
Date: Mon, 2 Oct 2023 19:53:54 -0700
Message-ID: 
 <CAKFQuwZbmqeLoHBzuOkVjd4qXyNUi1tcuhf8bECK+2c-DGK9iw@mail.gmail.com>
Subject: Re: A situation with one parent table and 3 child tables
To: JORGE MALDONADO <jorgemal1960@gmail.com>
Cc: "pgsql-sql@postgresql.org" <pgsql-sql@postgresql.org>
Content-Type: multipart/alternative; boundary="0000000000006306800606c700e0"
Archived-At: 
 <https://www.postgresql.org/message-id/CAKFQuwZbmqeLoHBzuOkVjd4qXyNUi1tcuhf8bECK%2B2c-DGK9iw%40mail.gmail.com>
Precedence: bulk

--0000000000006306800606c700e0
Content-Type: text/plain; charset="UTF-8"

On Monday, October 2, 2023, JORGE MALDONADO <jorgemal1960@gmail.com> wrote:

> Hi,
>
> I have one parent table (*table_p*) with 3 child tables (*table_ch1*, *table_ch2
> *and *table_ch3*). Each record of the parent table can be associated with
> 1 and only 1 child table records. This means that:
>
> * Some records of the *table_p* will link to records of *table_ch1*
> * Some records of the *table_p* will link to records of *table_ch2*
> * Some records of the *table_p* will link to records of *table_ch3*
>
> At first look, this does not make very much sense to me. I thought about
> considering 3 parent tables, one for each child table. However, the 3
> parent tables would have the same exact structure and I would like to know
> if there is a workaround for this issue.
>

You are thinking of it backwards.  Your chN tables will have FK pointing
back to the p table.  I suggest adding some kind of type column to the p
table indicating which chN table the row belongs to.  Then have the same
column on the chN table with a check constraint.  The key is then the id
and that type.  You can, with a bit of effort, ensure a row exists on the
chN table for every row on the p table but it is circular so must be
deferred in enforcement.

David J.

--0000000000006306800606c700e0
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

On Monday, October 2, 2023, JORGE MALDONADO &lt;<a href=3D"mailto:jorgemal1=
960@gmail.com">jorgemal1960@gmail.com</a>&gt; wrote:<br><blockquote class=
=3D"gmail_quote" style=3D"margin:0 0 0 .8ex;border-left:1px #ccc solid;padd=
ing-left:1ex"><div dir=3D"ltr"><div dir=3D"ltr"><div dir=3D"ltr">Hi,<div><b=
r></div><div>I have one parent table (<b>table_p</b>) with 3 child tables (=
<b>table_ch1</b>, <b>table_ch2 </b>and <b>table_ch3</b>). Each record of th=
e parent table can be associated with 1 and only 1 child table records. Thi=
s means that:</div><div><br></div><div>* Some records of the <b>table_p</b>=
 will link to records of <b>table_ch1</b></div><div>* Some records of the <=
b>table_p</b> will link to records of <b>table_ch2</b><br></div><div>* Some=
 records of the <b>table_p</b> will link to records of <b>table_ch3</b><br>=
</div><div><br></div><div>At first look, this does not make very much sense=
 to me. I thought about considering 3 parent tables, one for each child tab=
le. However, the 3 parent tables would have the same exact structure and I =
would like to know if there is a workaround=C2=A0for this issue.</div></div=
></div></div></blockquote><div><br></div><div>You are thinking of it backwa=
rds.=C2=A0 Your chN tables will have FK pointing back to the p table.=C2=A0=
 I suggest adding some kind of type column to the p table indicating which =
chN table the row belongs to.=C2=A0 Then have the same column on the chN ta=
ble with a check constraint.=C2=A0 The key is then the id and that type.=C2=
=A0 You can, with a bit of effort, ensure a row exists on the chN table for=
 every row on the p table but it is circular so must be deferred in enforce=
ment.</div><div><br></div><div>David J.</div><div><br></div>

--0000000000006306800606c700e0--