Обсуждение: benefits of an Array Column?

I was reading the o'reilly practical PostgreSQL book and came across
the chapter that mentioned the Array Column.

I fail to see the scenarios where this would be an useful feature.  How
is this better than linking to another table that could serve the same
purpose?

For instance, the example used in the book metions the "favorite books
of employees" by using a column for employee name and array column of
favorite book titles for that employee.

I could also approach this by having an employee master table and book
titles master table then creating a third table between these two
tables that would have rows that matched up employees with their
favorite titles.


Jared

jarednevans@yahoo.com writes:
> I fail to see the scenarios where this would be an useful feature.  How
> is this better than linking to another table that could serve the same
> purpose?

A relational-database purist would probably tell you you shouldn't
ever use an array ;-).

If you need the elements of the array to be individually searchable
then the linked-table approach is almost certainly better, but if not
then I think an array is often a good engineering compromise.

> For instance, the example used in the book metions the "favorite books
> of employees" by using a column for employee name and array column of
> favorite book titles for that employee.

I agree, that's a lousy example.  Here's one that is maybe a little
silly: consider teaching a machine to play tic-tac-toe by giving it
a table containing every possible board position and the best move.
You could handle this naturally by doing, say,

create table positions (
  board char(1)[3][3],
  char(1) tomove,
  int movex,
  int movey,
  primary key(board, tomove)
);

insert into positions values('{{X,-,O},{-,X,O},{-,-,-}}', 'X', 3, 3);
-- tediously many more inserts ...

-- to play:
select * from positions where board = '{...}' and tomove = 'X';

In this case an array is a perfectly natural way to represent the data
value.  Furthermore, it wouldn't make any sense at all to break this
down into two linked tables.  If you did, the referenced table would
have to have entries representing, say, 'X at position 1,1', which is
not a particularly interesting thing by itself (whereas books, in
the favorite-books example, certainly are individually interesting).
Plus the query you want to be fast would be horridly slow, as it
would be doing something like a ten-way join to identify the particular
positions row you need.

I think probably the rule of thumb is that arrays work when you have
data items that have an array-like structure, but that substructure
isn't interesting from the standpoint of the database structure.

            regards, tom lane

Tom Lane wrote:
> jarednevans@yahoo.com writes:
>>I fail to see the scenarios where this would be an useful feature.  How
>>is this better than linking to another table that could serve the same
>>purpose?

> I think probably the rule of thumb is that arrays work when you have
> data items that have an array-like structure, but that substructure
> isn't interesting from the standpoint of the database structure.

I agree, but would add that arrays are often very useful as
non-persistent structures for processing data, e.g. in PL/pgSQL functions.

Joe

Is there not a speed advantage using arrays in the right place instead
of tables ?
János
On Jun 20, 2004, at 3:29 PM, Joe Conway wrote:

> Tom Lane wrote:
>> jarednevans@yahoo.com writes:
>>> I fail to see the scenarios where this would be an useful feature.
>>> How
>>> is this better than linking to another table that could serve the
>>> same
>>> purpose?
>
>> I think probably the rule of thumb is that arrays work when you have
>> data items that have an array-like structure, but that substructure
>> isn't interesting from the standpoint of the database structure.
>
> I agree, but would add that arrays are often very useful as
> non-persistent structures for processing data, e.g. in PL/pgSQL
> functions.
>
> Joe
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 7: don't forget to increase your free space map settings
>
>
------------------------------------------
"The shortest route between two points is the middleman"  Ayn Rand