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.