Re: which is better- storing data as array or json? - Mailing list pgsql-general

From David G. Johnston
Subject Re: which is better- storing data as array or json?
Date
Msg-id CAKFQuwY2U7k5k+b2YQF-U1u+n=y4zSMc01kOrE1kMP5G=tH5yg@mail.gmail.com
Whole thread Raw
In response to Re: which is better- storing data as array or json?  (zach cruise <zachc1980@gmail.com>)
List pgsql-general
On Tue, Feb 17, 2015 at 9:00 PM, zach cruise <zachc1980@gmail.com> wrote:
i can't keep creating tables

​Where did you get this idea?​

or adding columns

​Of course not...​

every time i need to
add a nickname- this happens a lot.


​OK​...
 
so i want to put everything in an array or json.


​Those are not the only two solutions...​

remember rows can have different number of nicknames.

​Which is why you setup a "one-to-many (optional)" relationship between two tables; the first table's row (i.e., person)​ can have many related rows (i.e., nicknames) on the second table.

​Each nickname a person has is represented by a single row on the "person-nickname" table with whatever attributes you wish to keep track of.  New nickname means you add a new row - just like you would add a new array item to your JSON model.

If you need a truly dynamic representation (i.e, you do not know what nickname attributes you want to keep track of - like visibility and order as already shown - or want to allow users to add their own) you'd have to go with JSON (or add an hstore column to the nickname table); the array will not get you want you need​
 
​because multi-dimensional arrays are not a solution.

The solution proposed solves the "variable number of nicknames per person" need that you describe.  If there are other constraints you are dealing with you need to list them.​​

​David J.​

pgsql-general by date:

Previous
From: John R Pierce
Date:
Subject: Re: which is better- storing data as array or json?
Next
From: Dmitry O Litvintsev
Date:
Subject: postgresql93-9.3.5: deadlock when updating parent table expected?