Re: One parent record with 3 possible child records

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(dot)Wetmore(at)evernorth(dot)com> wrote:

> Wouldn’t page size performance be decreased with a very wide table?
> 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 <sanjay(at)crestadvice(dot)com>
> *Sent:* Thursday, May 2, 2024 6:34 AM
> *To:* JORGE MALDONADO <jorgemal1960(at)gmail(dot)com>
> *Cc:* pgsql-sql(at)postgresql(dot)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 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
>
>
>
>