Обсуждение: which is better- storing data as array or json?
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"}}
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.
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.g.johnston@gmail.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@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>
On 2/17/2015 8:00 PM, zach cruise wrote: > 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. david was suggesting a join table. one table with names (id, first name, last name, other attributes...) with one row per person and another table with (id, names_id, nickname, nickname attribute) with as many rows as there are nicks and users... you'd join these to get all the nicks for a given name, or to get all the names for a given nickname, etc. -- john r pierce 37N 122W somewhere on the middle of the left coast
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.