MIME-Version: 1.0
References: 
 <CAAY=A79usb2Lbr9nUyzxyRNWLQNc-w3oTBbtUGyC2a4E-qaX3A@mail.gmail.com>
In-Reply-To: 
 <CAAY=A79usb2Lbr9nUyzxyRNWLQNc-w3oTBbtUGyC2a4E-qaX3A@mail.gmail.com>
From: Sanjay Minni <sanjay@crestadvice.com>
Date: Thu, 2 May 2024 19:03:44 +0530
Message-ID: 
 <CAMpxBokoRxVgPVXTKeLV3jb+vA-wsDnwVsH_V_hQw=gATkCvoQ@mail.gmail.com>
Subject: Re: One parent record with 3 possible child records
To: JORGE MALDONADO <jorgemal1960@gmail.com>
Cc: pgsql-sql@postgresql.org
Content-Type: multipart/alternative; boundary="0000000000008ec923061778a8db"
Archived-At: 
 <https://www.postgresql.org/message-id/CAMpxBokoRxVgPVXTKeLV3jb%2BvA-wsDnwVsH_V_hQw%3DgATkCvoQ%40mail.gmail.com>
Precedence: bulk

--0000000000008ec923061778a8db
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

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=E2=80=AFAM JORGE MALDONADO <jorgemal1960@gmail.=
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 associate=
d
> to tblMain.
>
> I am not sure how to design a case like this. I will very much appreciate
> your feedback.
>
> Best regards,
> Jorge Maldonado
>
>

--0000000000008ec923061778a8db
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr"><div class=3D"gmail_default" style=3D"font-family:verdana,=
sans-serif">maybe unorthodox=C2=A0but if its certain to be 1:1 then why not=
 flatten it to a single=C2=A0table with all the possible columns (tblMain+t=
blOne+tblTwo+tblThree). You can keep a flag to indicate the type. there may=
 be a gain in simplicity without losing anything.</div><div class=3D"gmail_=
default" style=3D"font-family:verdana,sans-serif"><br></div><div class=3D"g=
mail_default" style=3D"font-family:verdana,sans-serif">Sanjay=C2=A0=C2=A0</=
div></div><br><div class=3D"gmail_quote"><div dir=3D"ltr" class=3D"gmail_at=
tr">On Thu, May 2, 2024 at 4:39=E2=80=AFAM JORGE MALDONADO &lt;<a href=3D"m=
ailto:jorgemal1960@gmail.com">jorgemal1960@gmail.com</a>&gt; wrote:<br></di=
v><blockquote class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;borde=
r-left:1px solid rgb(204,204,204);padding-left:1ex"><div dir=3D"ltr"><div d=
ir=3D"ltr"><div dir=3D"ltr">Hi,<div><br></div><div>I have one table that ca=
n have relations to only 1 of 3 possible tables. For example:=C2=A0tblMain<=
b>,</b>=C2=A0tblOne, tblTwo and tblThree.=C2=A0</div><div><br></div><div>I =
will always have 1 record in tblMain=C2=A0but each record in this table wil=
l be related to one record in tblOne OR one record in tblTwo OR one record =
in tblThree.=C2=A0</div><div><br></div><div>The relation between tblMain=C2=
=A0and tblOne is 1:1.</div><div>The relation between tblMain=C2=A0and tblTw=
o is 1:1.</div><div>The relation between tblMain=C2=A0and tblThree is 1:1.<=
/div><div><br></div><div>Is it better to set tblMain=C2=A0as parent or chil=
d?</div><div><br></div><div>This can be seen as if tblOne, tblTwo and tblTh=
ree extend=C2=A0tblMain=C2=A0depending=C2=A0on a specific criteria.<br></di=
v><div><br></div><div>Let&#39;s say that tableMain has a string field calle=
d &quot;type&quot; with the following possible values: &quot;residential&qu=
ot;, &quot;industrial&quot; and &quot;energy&quot;.=C2=A0</div><div><br></d=
iv><div>I will always insert a record in tblMain but:</div><div>* If type i=
s &quot;residential&quot; then a record in tblOne is created and it is asso=
ciated to tblMain,=C2=A0</div><div>* If type is &quot;industrial&quot; a re=
cord in tblTwo is created and it is associated to tblMain</div><div>* If ty=
pe is &quot;energy&quot; a record in tblThree=C2=A0is created=C2=A0and it i=
s associated to tblMain.</div><div><br></div><div>I am not sure how to desi=
gn a case like this. I will very much appreciate your feedback.</div><div><=
br></div><div>Best regards,</div><div>Jorge Maldonado</div><div><br></div><=
/div></div></div>
</blockquote></div>

--0000000000008ec923061778a8db--