Re: One parent record with 3 possible child records

maybe unorthodox but if its certain to be 1:1 then why not flatten it to a
single table with all the possible columns
(tblMain+tblOne+tblTwo+tblThree). You can keep a flag to indicate the type.
there may be a gain in simplicity without losing anything.

Sanjay

On Thu, May 2, 2024 at 4:39 AM JORGE MALDONADO <jorgemal1960(at)gmail(dot)com>
wrote:

> Hi,
>
> I have one table that can have relations to only 1 of 3 possible tables.
> For example: tblMain*,* tblOne, tblTwo and tblThree.
>
> I will always have 1 record in tblMain but each record in this table will
> be related to one record in tblOne OR one record in tblTwo OR one record in
> tblThree.
>
> The relation between tblMain and tblOne is 1:1.
> The relation between tblMain and tblTwo is 1:1.
> The relation between tblMain and tblThree is 1:1.
>
> Is it better to set tblMain as parent or child?
>
> This can be seen as if tblOne, tblTwo and tblThree
> extend tblMain depending on a specific criteria.
>
> Let's say that tableMain has a string field called "type" with the
> following possible values: "residential", "industrial" and "energy".
>
> I will always insert a record in tblMain but:
> * If type is "residential" then a record in tblOne is created and it is
> associated to tblMain,
> * If type is "industrial" a record in tblTwo is created and it is
> associated to tblMain
> * If type is "energy" a record in tblThree is created and it is associated
> to tblMain.
>
> I am not sure how to design a case like this. I will very much appreciate
> your feedback.
>
> Best regards,
> Jorge Maldonado
>
>