Re: proper use of array datatype

# eric(dot)m(dot)andrews(at)gmail(dot)com / 2006-08-02 10:49:01 -0700:
> 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...

You need to rotate your brains 90 degrees. You cant have a distinct
set of columns (a table) for each set, but you can have have a
distinct set of rows (a set) for each, ummm, set. The language suggests
it's a better model, and indeed it is:

CREATE TABLE destination (
destid SERIAL PRIMARY KEY,
destname VARCHAR
-- ...
);
CREATE TABLE box (
boxid SERIAL PRIMARY KEY,
destid INT REFERENCES destination (destid)
-- ...
);
CREATE TABLE box_contents (
boxid SERIAL REFERENCES box (boxid),
thing TEXT
-- ...
);

SELECT * FROM box_contents
JOIN box USING (boxid)
JOIN destination USING (destid)
WHERE destination.destname = 'foo';

--
How many Vietnam vets does it take to screw in a light bulb?
You don't know, man. You don't KNOW.
Cause you weren't THERE. http://bash.org/?255991