Subject: A situation with one parent table and 3 child tables

From: JORGE MALDONADO <[email-masked]>
Date: Mon, 2 Oct 2023 15:57:32 -0500

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.

Thanks in advance.

Jorge Maldonado

---

From: Gareth Evans <[email-masked]>
Date: Mon, 2 Oct 2023 22:46:00 +0100

Hi Jorge,

Several 1:m relationships may be appropriate where the number of corresponding records on the m side can be 0, 1 or more.

If the child entities vary little or not at all in structure, but represent different “types”, a single 1:m might suffice with “type” as an attribute at the many end, and suitable indexes, and filtering at the application level.

It's difficult to generalise.  If you can give more information on the scenario, better advice may be forthcoming.

Kind regards,
Gareth

> On 2 Oct 2023, at 21:59, JORGE MALDONADO <[email-masked]> 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.
> 
> Thanks in advance.
> 
> Jorge Maldonado

---

From: "David G. Johnston" <[email-masked]>
Date: Mon, 2 Oct 2023 19:53:54 -0700

On Monday, October 2, 2023, JORGE MALDONADO <[email-masked]> 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:
>

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.

---

From: Tom Lane <[email-masked]>
Date: Mon, 02 Oct 2023 23:03:04 -0400

> 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.

			regards, tom lane

---

From: "David G. Johnston" <[email-masked]>
Date: Mon, 2 Oct 2023 20:10:12 -0700

> 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.

---

From: Rob Sargent <[email-masked]>
Date: Mon, 2 Oct 2023 21:14:16 -0600

> can set up one FK constraint between that and the parent table.

I've implemented David's suggestion in the past.  Presumably the three 
child tables are of different structure but share the attributes of the 
"parent"