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

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.