public inbox for [email protected]
help / color / mirror / Atom feedA situation with one parent table and 3 child tables
6+ messages / 5 participants
[nested] [flat]
* A situation with one parent table and 3 child tables
@ 2023-10-02 20:57 JORGE MALDONADO <[email protected]>
2023-10-02 21:46 ` Re: A situation with one parent table and 3 child tables Gareth Evans <[email protected]>
2023-10-03 02:53 ` Re: A situation with one parent table and 3 child tables David G. Johnston <[email protected]>
0 siblings, 2 replies; 6+ messages in thread
From: JORGE MALDONADO @ 2023-10-02 20:57 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: A situation with one parent table and 3 child tables
2023-10-02 20:57 A situation with one parent table and 3 child tables JORGE MALDONADO <[email protected]>
@ 2023-10-02 21:46 ` Gareth Evans <[email protected]>
1 sibling, 0 replies; 6+ messages in thread
From: Gareth Evans @ 2023-10-02 21:46 UTC (permalink / raw)
To: JORGE MALDONADO <[email protected]>; +Cc: pgsql-sql
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 protected]> 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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: A situation with one parent table and 3 child tables
2023-10-02 20:57 A situation with one parent table and 3 child tables JORGE MALDONADO <[email protected]>
@ 2023-10-03 02:53 ` David G. Johnston <[email protected]>
2023-10-03 03:03 ` Re: A situation with one parent table and 3 child tables Tom Lane <[email protected]>
1 sibling, 1 reply; 6+ messages in thread
From: David G. Johnston @ 2023-10-03 02:53 UTC (permalink / raw)
To: JORGE MALDONADO <[email protected]>; +Cc: pgsql-sql
On Monday, October 2, 2023, JORGE MALDONADO <[email protected]> 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.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: A situation with one parent table and 3 child tables
2023-10-02 20:57 A situation with one parent table and 3 child tables JORGE MALDONADO <[email protected]>
2023-10-03 02:53 ` Re: A situation with one parent table and 3 child tables David G. Johnston <[email protected]>
@ 2023-10-03 03:03 ` Tom Lane <[email protected]>
2023-10-03 03:10 ` Re: A situation with one parent table and 3 child tables David G. Johnston <[email protected]>
2023-10-03 03:14 ` Re: A situation with one parent table and 3 child tables Rob Sargent <[email protected]>
0 siblings, 2 replies; 6+ messages in thread
From: Tom Lane @ 2023-10-03 03:03 UTC (permalink / raw)
To: David G. Johnston <[email protected]>; +Cc: JORGE MALDONADO <[email protected]>; pgsql-sql
"David G. Johnston" <[email protected]> writes:
> On Monday, October 2, 2023, JORGE MALDONADO <[email protected]> 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.
regards, tom lane
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: A situation with one parent table and 3 child tables
2023-10-02 20:57 A situation with one parent table and 3 child tables JORGE MALDONADO <[email protected]>
2023-10-03 02:53 ` Re: A situation with one parent table and 3 child tables David G. Johnston <[email protected]>
2023-10-03 03:03 ` Re: A situation with one parent table and 3 child tables Tom Lane <[email protected]>
@ 2023-10-03 03:10 ` David G. Johnston <[email protected]>
1 sibling, 0 replies; 6+ messages in thread
From: David G. Johnston @ 2023-10-03 03:10 UTC (permalink / raw)
To: Tom Lane <[email protected]>; +Cc: JORGE MALDONADO <[email protected]>; pgsql-sql
On Monday, October 2, 2023, Tom Lane <[email protected]> wrote:
> "David G. Johnston" <[email protected]> writes:
> > On Monday, October 2, 2023, JORGE MALDONADO <[email protected]>
> 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.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: A situation with one parent table and 3 child tables
2023-10-02 20:57 A situation with one parent table and 3 child tables JORGE MALDONADO <[email protected]>
2023-10-03 02:53 ` Re: A situation with one parent table and 3 child tables David G. Johnston <[email protected]>
2023-10-03 03:03 ` Re: A situation with one parent table and 3 child tables Tom Lane <[email protected]>
@ 2023-10-03 03:14 ` Rob Sargent <[email protected]>
1 sibling, 0 replies; 6+ messages in thread
From: Rob Sargent @ 2023-10-03 03:14 UTC (permalink / raw)
To: [email protected]
On 10/2/23 21:03, Tom Lane wrote:
> "David G. Johnston" <[email protected]> writes:
>> On Monday, October 2, 2023, JORGE MALDONADO <[email protected]> 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.
>
> regards, tom lane
>
>
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"
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2023-10-03 03:14 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2023-10-02 20:57 A situation with one parent table and 3 child tables JORGE MALDONADO <[email protected]>
2023-10-02 21:46 ` Gareth Evans <[email protected]>
2023-10-03 02:53 ` David G. Johnston <[email protected]>
2023-10-03 03:03 ` Tom Lane <[email protected]>
2023-10-03 03:10 ` David G. Johnston <[email protected]>
2023-10-03 03:14 ` Rob Sargent <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox