MIME-Version: 1.0
In-Reply-To: <2206102.1696302184@sss.pgh.pa.us>
References: 
 <CAAY=A7_wHBKkKFxuxXc7A+fr3TYGmWS9-hCGghLRDcQAAa_wfQ@mail.gmail.com>
 <CAKFQuwZbmqeLoHBzuOkVjd4qXyNUi1tcuhf8bECK+2c-DGK9iw@mail.gmail.com>
 <2206102.1696302184@sss.pgh.pa.us>
From: "David G. Johnston" <david.g.johnston@gmail.com>
Date: Mon, 2 Oct 2023 20:10:12 -0700
Message-ID: 
 <CAKFQuwa6-C9V8nVB6CkX8P0y_b675HZRjp4uqiMqLWD0x1=TFA@mail.gmail.com>
Subject: Re: A situation with one parent table and 3 child tables
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: JORGE MALDONADO <jorgemal1960@gmail.com>,
	"pgsql-sql@postgresql.org" <pgsql-sql@postgresql.org>
Content-Type: multipart/alternative; boundary="000000000000b49d360606c73a9c"
Archived-At: 
 <https://www.postgresql.org/message-id/CAKFQuwa6-C9V8nVB6CkX8P0y_b675HZRjp4uqiMqLWD0x1%3DTFA%40mail.gmail.com>
Precedence: bulk

--000000000000b49d360606c73a9c
Content-Type: text/plain; charset="UTF-8"

On Monday, October 2, 2023, Tom Lane <tgl@sss.pgh.pa.us> wrote:

> "David G. Johnston" <david.g.johnston@gmail.com> writes:
> > On Monday, October 2, 2023, JORGE MALDONADO <jorgemal1960@gmail.com>
> wrote:
> >> 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.
>
> Do you need that?  I was wondering about converting the 3 child tables
> into a partitioned table.  Then you can query them separately when
> you need to, but you can also treat them as one table --- and you
> can set up one FK constraint between that and the parent table.
>

This sounds like a typical subclassing (animal -> {dog,cat,moose})
structure where I am assuming the children have different subtype-specific
columns.

David J.

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

<br>On Monday, October 2, 2023, Tom Lane &lt;<a href=3D"mailto:tgl@sss.pgh.=
pa.us">tgl@sss.pgh.pa.us</a>&gt; wrote:<br><blockquote class=3D"gmail_quote=
" style=3D"margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">&=
quot;David G. Johnston&quot; &lt;<a href=3D"mailto:david.g.johnston@gmail.c=
om">david.g.johnston@gmail.com</a>&gt; writes:<br>
&gt; On Monday, October 2, 2023, JORGE MALDONADO &lt;<a href=3D"mailto:jorg=
emal1960@gmail.com">jorgemal1960@gmail.com</a>&gt; wrote:<br>
&gt;&gt; I have one parent table (*table_p*) with 3 child tables (*table_ch=
1*, *table_ch2<br>
&gt;&gt; *and *table_ch3*). Each record of the parent table can be associat=
ed with<br>
&gt;&gt; 1 and only 1 child table records. This means that:<br>
&gt;&gt; <br>
&gt;&gt; * Some records of the *table_p* will link to records of *table_ch1=
*<br>
&gt;&gt; * Some records of the *table_p* will link to records of *table_ch2=
*<br>
&gt;&gt; * Some records of the *table_p* will link to records of *table_ch3=
*<br>
&gt;&gt; <br>
&gt;&gt; At first look, this does not make very much sense to me. I thought=
 about<br>
&gt;&gt; considering 3 parent tables, one for each child table. However, th=
e 3<br>
&gt;&gt; parent tables would have the same exact structure and I would like=
 to know<br>
&gt;&gt; if there is a workaround for this issue.<br>
<br>
&gt; You are thinking of it backwards.=C2=A0 Your chN tables will have FK p=
ointing<br>
&gt; back to the p table.=C2=A0 I suggest adding some kind of type column t=
o the p<br>
&gt; table indicating which chN table the row belongs to.<br>
<br>
Do you need that?=C2=A0 I was wondering about converting the 3 child tables=
<br>
into a partitioned table.=C2=A0 Then you can query them separately when<br>
you need to, but you can also treat them as one table --- and you<br>
can set up one FK constraint between that and the parent table.<br>
</blockquote><div><br></div><div>This sounds like a typical subclassing (an=
imal -&gt; {dog,cat,moose}) structure where I am assuming the children have=
 different subtype-specific columns.</div><div><br></div><div>David J.</div=
><div>=C2=A0</div>

--000000000000b49d360606c73a9c--