Re: proper use of array datatype

Eric Andrews wrote:
> I am not much of a schema designer and have a general questoin about
> the proper use of the array datatype. In my example, I have
> destinations, and destinations can have multiple boxes, and inside
> those boxes are a set of contents. what I want to do is search and
> basically "mine" data from the content sets.

I would use arrays exclusively for data sets for which each datum is
meaningless by itself (for example, a single coordinate in 3D, although
there are better ways to handle points in postgresql). I would recommend
against using arrays for any data you wish to mine, and instead recast
these has-a relationships as many-to-one joins across at least two
tables. For example, a row from the table destination has-a (joins to)
rows from boxes, and a box has-a (joins to) contents.

The same argument goes for a similar representation such as concatenated
values in a text field. The fundamental principle is that it's
relatively easy to turn join separate data into a set of values or
concatenated list, but it's quite cumbersome to turn a set of values
into easily searchable data (i.e., it's often expensive to "peek" inside
the structure of the data for a single value). Furthermore, it's
difficult or impossible to write check or foreign key constraints on
data within such a structure.

-Reece

--
Reece Hart, http://harts.net/reece/, GPG:0x25EC91A0