MIME-Version: 1.0
In-Reply-To: <CAMXrOMOav1m8Ep01o7_GDLWxLSpgMp+o4d3FNdmOY7RoWe5B7Q@mail.gmail.com>
References: <CAMXrOMOav1m8Ep01o7_GDLWxLSpgMp+o4d3FNdmOY7RoWe5B7Q@mail.gmail.com>
From: "David G. Johnston" <david.g.johnston@gmail.com>
Date: Wed, 22 May 2024 20:50:47 -0700
Message-ID: <CAKFQuwbDLizRmcrc5=e5AMdsgZ1DrxBNuvrudcrCdeHaOKDPZw@mail.gmail.com>
Subject: Re: Json table/column design question
To: Skorpeo Skorpeo <skorpeo11@gmail.com>
Cc: "pgsql-general@lists.postgresql.org" <pgsql-general@lists.postgresql.org>
Content-Type: multipart/alternative; boundary="000000000000dad384061916f494"
Archived-At: <https://www.postgresql.org/message-id/CAKFQuwbDLizRmcrc5%3De5AMdsgZ1DrxBNuvrudcrCdeHaOKDPZw%40mail.gmail.com>
Precedence: bulk

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

On Wednesday, May 22, 2024, Skorpeo Skorpeo <skorpeo11@gmail.com> wrote:

> Hi,
>
> I was wondering if having unrelated columns in a table is a sound approac=
h
> when using json.  In other words, if I have two collections of unrelated
> json objects, for example "Users" and "Inventory", would it be ok to have
> one table with a "Users" column and a "Inventory" column?  My concern is
> that from a row perspective the columns could be different lengths, such =
as
> more inventory items as users.  And for any given row the data in one
> column would have no relation to another column. I would only query a
> single column at a time.
>
> Would this approach be ok or are there pitfalls such that it would be
> advantageous/recommended to have a separate table for each column?
>
> Any thoughts/inputs are greatly appreciated.
>

What do you expect to gain?  The relational model and normalization has
earned its longevity, I suggest you learn and apply those techniques.
Which generally means avoiding json as a data type. (The single table
abomination you describe isn=E2=80=99t even really on the table - you did a=
 fine
job arguing against it yourself.)

David J.

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

On Wednesday, May 22, 2024, Skorpeo Skorpeo &lt;<a href=3D"mailto:skorpeo11=
@gmail.com">skorpeo11@gmail.com</a>&gt; wrote:<br><blockquote class=3D"gmai=
l_quote" style=3D"margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left=
:1ex"><div dir=3D"ltr"><div>Hi,</div><div><br></div><div>I was wondering if=
 having unrelated columns in a table is a sound approach when using json.=
=C2=A0 In other words, if I have two collections of unrelated json objects,=
 for example &quot;Users&quot; and &quot;Inventory&quot;, would it be ok to=
 have one table with a &quot;Users&quot; column and a &quot;Inventory&quot;=
 column?=C2=A0 My concern is that from a row perspective the columns could =
be different lengths, such as more inventory items as users.=C2=A0 And for =
any given row the data in one column would have no relation to another colu=
mn. I would only query a single column at a time.</div><div><br></div><div>=
Would this approach be ok or are there pitfalls such that it would be advan=
tageous/recommended to have a separate table for each column?</div><div><br=
></div><div>Any thoughts/inputs are greatly appreciated.</div></div></block=
quote><div><br></div><div>What do you expect to gain?=C2=A0 The relational =
model and normalization has earned its longevity, I suggest you learn and a=
pply those techniques.=C2=A0 Which generally means avoiding json as a data =
type. (The single table abomination you describe isn=E2=80=99t even really =
on the table - you did a fine job arguing against it yourself.)</div><div><=
br></div><div>David J.</div><div><br></div>

--000000000000dad384061916f494--