Re: Design strategy for table with many attributes

Some folks in the team suggested to have key business attributes or say
frequently queried attributes in individual columns and others in a column
in same table clubbed in JSON format. Is that advisable or any issues can
occur with this approach? Also not sure how effectively postgres processes
JSON (both read and write perspective) as compared to normal column in a
oltp environment. Please advise.

As David suggested it breaks if a row exceeds the 8k limit I. E a single
page size , will that still holds true if we have a column with JSON in it?

On Fri, 5 Jul, 2024, 12:04 pm Guyren Howe, <guyren(at)gmail(dot)com> wrote:

> On Jul 4, 2024, at 23:28, Lok P <loknath(dot)73(at)gmail(dot)com> wrote:
>
>
>
> *"Note that you might want to split up the “parent” table if that
> naturally groups its columns together for certain uses. In that case, you
> could have the same pk on all the 1:1 tables you then have. In that case,
> the pk for each of those tables is also the fk."*
> Do you mean having a real FK created through DDL and maintaining it or
> just assume it and no need to define it for all the pieces/tables. Only
> keep the same PK across all the pieces and as we know these are related to
> the same transaction and are logically related?
>
>
> A primary key *names something*. Often it’s a kind of platonic
> representation of a real thing — say, a person.
>
> I might use a person’s login columns in some functions, and the person’s
> name, birth date, etc in other functions.
>
> Rather than have one table, I should split this into two, but use the same
> primary key (I would either name both id or both, say, person_id,
> irrespective of the name of the table, so it’s clear you’re doing this).
>
> You can just do a join on the mutual primary keys as you’d expect. In
> fact, if you name them the same, you can just use NATURAL JOIN.
>
> So you’d have person_details and person_login tables, and have a person_id
> pk for both.
>