Re: which is better- storing data as array or json?

i can't keep creating tables or adding columns every time i need to
add a nickname- this happens a lot.

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

remember rows can have different number of nicknames.

On 2/17/15, David G Johnston <david(dot)g(dot)johnston(at)gmail(dot)com> wrote:
> zach cruise wrote
>> for indexing, accessing, filtering and searching?
>>
>> as simple array-
>> first name | last name | nicknames
>> tom | jerry | {cat}, {mouse}
>>
>> as multi-dimensional array-
>> first name | last name | nicknames
>> tom | jerry | {cat, kat}, {mouse, mice}
>>
>> as simple json-
>> first name | last name | nicknames
>> tom | jerry | {"public": "cat", "private": "mouse"}
>>
>> as multi-nested json-
>> first name | last name | nicknames
>> tom | jerry | {"public": {"first": "cat", "second": "kat"},
>> "private": {"first": "mouse", "second": "mice"}}
>
> The choice of proper model depends on how you intend to make use of it.
>
> That said, I'd go with "none of the above" by default.
>
> My first reaction in this scenario would be to create a nicknames table:
>
> [nick_person_id, person_id, nick_name, nick_scope, nick_scope_order]
>
> You could maybe normalize further by having a nickname table with integer
> keys that then end up as FKs on this many-to-many relation.
>
> An array is too complicated given the fact you need to track attributes on
> the nicknames. You could possible do an array over a composite type but
> I'm
> not sure how indexing and searching would fare in that setup.
>
> Why are you even considering storing the information in JSON? The answer
> to
> that question would make it more obvious whether that solution is viable
> but
> do you really want any application that makes use of this data to have to
> speak JSON to do so when the time-tested relational model can likely give
> you everything you need - and probably more. Even if you had to serialize
> the data to and from JSON I would say that storing the data in that format
> to avoid the serializing is an instance of pre-mature optimization.
>
> David J.
>
>
>
> --
> View this message in context:
> http://postgresql.nabble.com/which-is-better-storing-data-as-array-or-json-tp5838358p5838362.html
> Sent from the PostgreSQL - general mailing list archive at Nabble.com.
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>