Обсуждение: A situation with one parent table and 3 child tables
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
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 <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_ch3At 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
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_ch3At 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.
"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.
regards, tom lane
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.
On 10/2/23 21:03, Tom Lane 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. > > 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"