Re: proper use of array datatype

On 8/1/06, Reece Hart <reece(at)harts(dot)net> wrote:
>
> 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.
>

how would these tables look though? I cant have a table for each set of
contents in a box...

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
>
>