Re: benefits of an Array Column?

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