Json table/column design question
Hi,
I was wondering if having unrelated columns in a table is a sound approach
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.
Many thanks.
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 approach
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’t even really on the table - you did a fine
job arguing against it yourself.)
David J.
Greetings,
Storing unrelated JSON objects in the same table with distinct columns for
each type (e.g., "Users" and "Inventory") is generally not a sound good
approach may affect Query Performance and Optimization, Storage Efficiency,
scalability and Maintenance, Data Integrity.
Recommended approach is to have separate tables.
*Salahuddin (살라후딘*
*)*
On Thu, 23 May 2024 at 08:39, Skorpeo Skorpeo <skorpeo11@gmail.com> wrote:
Show quoted text
Hi,
I was wondering if having unrelated columns in a table is a sound approach
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.
Many thanks.
On Wed, 2024-05-22 at 22:38 -0500, Skorpeo Skorpeo wrote:
I was wondering if having unrelated columns in a table is a sound approach 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?
It doesn't matter much if you use one or two columns.
But the word "collection" makes me worry. Perhaps this article can give
you some ideas:
https://www.cybertec-postgresql.com/en/json-postgresql-how-to-use-it-right/
Yours,
Laurenz Albe
Thank you for the valuable feedback. I see people are big fans of json
here.
On Thu, May 23, 2024 at 3:04 AM Laurenz Albe <laurenz.albe@cybertec.at>
wrote:
Show quoted text
On Wed, 2024-05-22 at 22:38 -0500, Skorpeo Skorpeo wrote:
I was wondering if having unrelated columns in a table is a sound
approach 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 moreinventory
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?It doesn't matter much if you use one or two columns.
But the word "collection" makes me worry. Perhaps this article can give
you some ideas:https://www.cybertec-postgresql.com/en/json-postgresql-how-to-use-it-right/
Yours,
Laurenz Albe
On 5/23/24 09:06, Skorpeo Skorpeo wrote:
Thank you for the valuable feedback. I see people are big fans of json
here.
You can be a fan of JSON and still think it is not the correct way to
store data in a relational database. When you do that you often end up
with nested, possibly unstructured, data sets. That ends up with the
exertion of more effort to get the data out in any meaningful form and
in a timely manner then the time and effort it would take to enter it in
a structured way.
On Thu, May 23, 2024 at 3:04 AM Laurenz Albe <laurenz.albe@cybertec.at
<mailto:laurenz.albe@cybertec.at>> wrote:On Wed, 2024-05-22 at 22:38 -0500, Skorpeo Skorpeo wrote:
I was wondering if having unrelated columns in a table is a sound
approach 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?It doesn't matter much if you use one or two columns.
But the word "collection" makes me worry. Perhaps this article can give
you some ideas:https://www.cybertec-postgresql.com/en/json-postgresql-how-to-use-it-right/ <https://www.cybertec-postgresql.com/en/json-postgresql-how-to-use-it-right/>
Yours,
Laurenz Albe
--
Adrian Klaver
adrian.klaver@aklaver.com
On Thu, May 23, 2024, 10:07 Skorpeo Skorpeo <skorpeo11@gmail.com> wrote:
Thank you for the valuable feedback. I see people are big fans of json
here.
It's better for data interchange than as a basis for long term model
storage.
David J.