Re: proper use of array datatype - Mailing list pgsql-general

From Eric Andrews
Subject Re: proper use of array datatype
Date
Msg-id 7a4707ef0608021049v1f9667a6kc8d2e9463e2105df@mail.gmail.com
Whole thread Raw
In response to Re: proper use of array datatype  (Reece Hart <reece@harts.net>)
Responses Re: proper use of array datatype  (Roman Neuhauser <neuhauser@sigpipe.cz>)
List pgsql-general


On 8/1/06, Reece Hart <reece@harts.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

pgsql-general by date:

Previous
From: "Carlo Stonebanks"
Date:
Subject: Re: Where do Tcl questions go?
Next
From: James Robinson
Date:
Subject: Re: Performance/Issues with CMP and JBoss