Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1s2Y7L-003x1Z-3M for pgsql-sql@arkaria.postgresql.org; Thu, 02 May 2024 15:13:03 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1s2Y7I-001lba-Cc for pgsql-sql@arkaria.postgresql.org; Thu, 02 May 2024 15:13:01 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1s2Y7I-001lbG-2T for pgsql-sql@lists.postgresql.org; Thu, 02 May 2024 15:13:01 +0000 Received: from mail-ej1-x629.google.com ([2a00:1450:4864:20::629]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1s2Y7E-001BsU-AV for pgsql-sql@postgresql.org; Thu, 02 May 2024 15:13:00 +0000 Received: by mail-ej1-x629.google.com with SMTP id a640c23a62f3a-a595199cb9bso260148066b.3 for ; Thu, 02 May 2024 08:12:56 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1714662775; x=1715267575; darn=postgresql.org; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:from:to:cc:subject:date:message-id:reply-to; bh=Tq4gUvLLphk/mrdGkZQy0pX1tbqeXMHjaPPf+kXVHiA=; b=J4Cnm/tdYa28y7mSpjVnjg2iFbB6yHVv5LV998A67D8wz1aWWBj4y9Y7mIVAJ7BoWM +dagRxdGp4iFT6ZzAd8lfjNVwhNLR8vgjD9tonfCzvYyOazDxePQm+5okoCTf7mv0QZl A9K5CvFccrpb3Y2gr8vVquqsn/+9o11shzmwpEwjviY2X7yVsFpeJYJEZQcBUwEZCUv0 PwISVGHAlxZdGxIANm0nQCevgNSC8Ju2XipAvuwywsNZDN9vtT2d0G9mpqy8DYjhptwS uneh73gGrFmB9TCG/Y5V0eQAGXzCPfE9BWrz36WV2s+JV703SindSOTS3KwYNuIwZKSX A16w== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1714662775; x=1715267575; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:x-gm-message-state:from:to:cc:subject:date:message-id :reply-to; bh=Tq4gUvLLphk/mrdGkZQy0pX1tbqeXMHjaPPf+kXVHiA=; b=LCUUMxgvuoaLvlMo81FoaeZG1wNbqZogfw7GFww6+A7WTmIteBr5STK9zecp22lb68 ZcCxAKaArVOM3F4WGra1DunyHVXG60i7Krew1dJNd98GDo5QX6Nqes4VufUoabEAdFMN poC0XkLgv55UBJ2reQK/t+VHrGUp3KxS5u/lYll7dpwAvkSipz4jZw2G2k5lcYbuEm7q mLmhfpjla6Y6y3DhEyC3giWRNxX0RFlF84ib09TcNNCs3g2N4C37eFDjRxnCBat/ZoMm 6zhCe1eezONso49zyWDsz6ZsWRsTFbgdFA4zz2BVw1Cpet49xoNZSH+amc7HDj/L2UFk FwzA== X-Forwarded-Encrypted: i=1; AJvYcCUP9ewPEr6x/ZY9XB6AtHZxePKtk/jsGfrdMc2CgEgv8KcGpZiukCC3J8SoCwOshqNbiHGEjikT91UVaB2LlClPfqYzxaaAksA= X-Gm-Message-State: AOJu0YwhmcwO5vY0krLf/9eXduFRWACtZW5s2VTVlvIfZ66aMmnZ4ZZD iAZjY5zZBFJNMzlHlRsUnbjo8EaTcQG/6Sn7TWWosBOSAahdArPwoxoVxSP4DYWKbJsS2eUeCgl 53pAq8QKTkrSI8baekyDIJmM+vwM= X-Google-Smtp-Source: AGHT+IGnsP5LNcMvNbDWBYWl8qX4Cv9/1zPGhBKiavMBSqlF3PggF7amKq/T5EWRdsbjvdAlG6DLoenVDRbHT8427Ac= X-Received: by 2002:a17:906:2453:b0:a58:82a4:3a2c with SMTP id a19-20020a170906245300b00a5882a43a2cmr3801289ejb.13.1714662774913; Thu, 02 May 2024 08:12:54 -0700 (PDT) MIME-Version: 1.0 References: <75a1cd932cff4ed48defeb19280ab205@evernorth.com> In-Reply-To: <75a1cd932cff4ed48defeb19280ab205@evernorth.com> From: Sanjay Minni Date: Thu, 2 May 2024 20:42:42 +0530 Message-ID: Subject: Re: One parent record with 3 possible child records To: "Wetmore, Matthew (CTR)" Cc: JORGE MALDONADO , pgsql-sql@postgresql.org Content-Type: multipart/alternative; boundary="0000000000009bc24006177a0962" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --0000000000009bc24006177a0962 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable As I understand postgres does not reserve or use space for columns having no content. If that's so then the bloat will be insignificant . But off course the pros and cons of embedding the columns inline have to be weighed On Thu, 2 May, 2024, 8:07 pm Wetmore, Matthew (CTR), < Matthew.Wetmore@evernorth.com> wrote: > Wouldn=E2=80=99t page size performance be decreased with a very wide tabl= e? > Unless the columns are tight. If you are on a VM subsystem, that page > bloat will be over the network from the memory controller. > > > > *From:* Sanjay Minni > *Sent:* Thursday, May 2, 2024 6:34 AM > *To:* JORGE MALDONADO > *Cc:* pgsql-sql@postgresql.org > *Subject:* [EXTERNAL] 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 typ= e. > there may be a gain in simplicity without losing anything. > > > > Sanjay > > > > On Thu, May 2, 2024 at 4:39=E2=80=AFAM JORGE MALDONADO > 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 > > > > --0000000000009bc24006177a0962 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
As I understand postgres= does not reserve or use space for columns having no content. If that's= so then the bloat will be insignificant . But off course the pros and cons= of embedding the columns inline have to be weighed

On Thu, 2 May, 20= 24, 8:07 pm Wetmore, Matthew (CTR), <Matthew.Wetmore@evernorth.com> wrote:

Wouldn=E2=80=99t page size performance be decreased = with a very wide table?=C2=A0 Unless the columns are tight.=C2=A0 If you ar= e on a VM subsystem, that page bloat will be over the network from the memo= ry controller.

=C2=A0

From: Sanjay Minni <sanjay@crestadvice= .com>
Sent: Thursday, May 2, 2024 6:34 AM
To: JORGE MALDONADO <jorgemal1960@gmail.com>
Cc: pgsql-sql@postgresql.org
Subject: [EXTERNAL] Re: One parent record with 3 possible child reco= rds

=C2=A0

maybe unorthodox=C2=A0but if its certain to be 1:1 then why not flat= ten it to a single=C2=A0table 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.

=C2=A0

Sanjay=C2=A0=C2=A0

=C2=A0

On Thu, May 2, 2024 at 4:39=E2=80=AFAM JORGE MALDONA= DO <jorgemal1960@gmail.com> wrote:

Hi,

=C2=A0

I have one table that can have relations to only 1 o= f 3 possible tables. For example:=C2=A0tblMain,=C2=A0tblOne, tblTwo = and tblThree.=C2=A0

=C2=A0

I will always have 1 record in tblMain=C2=A0but each= record in this table will be related to one record in tblOne OR one record= in tblTwo OR one record in tblThree.=C2=A0

=C2=A0

The relation between tblMain=C2=A0and tblOne is 1:1.=

The relation between tblMain=C2=A0and tblTwo is 1:1.=

The relation between tblMain=C2=A0and tblThree is 1:= 1.

=C2=A0

Is it better to set tblMain=C2=A0as parent or child?=

=C2=A0

This can be seen as if tblOne, tblTwo and tblThree e= xtend=C2=A0tblMain=C2=A0depending=C2=A0on a specific criteria.

=C2=A0

Let's say that tableMain has a string field call= ed "type" with the following possible values: "residential&q= uot;, "industrial" and "energy".=C2=A0

=C2=A0

I will always insert a record in tblMain but:=

* If type is "residential" then a record i= n tblOne is created and it is associated to tblMain,=C2=A0

* If type is "industrial" a record in tblT= wo is created and it is associated to tblMain

* If type is "energy" a record in tblThree= =C2=A0is created=C2=A0and it is associated to tblMain.

=C2=A0

I am not sure how to design a case like this. I will= very much appreciate your feedback.

=C2=A0

Best regards,

Jorge Maldonado

=C2=A0

--0000000000009bc24006177a0962--