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 (AT) yahoo (DOT) com writes:
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.

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

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

Tom Lane wrote:
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