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.