Re: proper use of array datatype

Eric Andrews wrote:
> hello all,
>
> 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. do I use an array
> datatype for the content column, or is there a better more efficient
> way to go about this?
>
> From http://www.postgresql.org/docs/8.1/interactive/arrays.html
>
> Tip: Arrays are not sets; searching for specific array elements may
> be a sign of database misdesign. Consider using a separate table with
> a row for each item that would be an array element. This will be
> easier to search, and is likely to scale up better to large numbers of
> elements.
Yeah, I've never considered arrays to be good data-types for columns.
One possible solution to what (I think) you're trying to do, is to have
a text or varchar column in which you store multiple values separated by
some delimiter (such as ::) that will not occur in the actual option
names. Then you can write rules to handle
inserting/updating/selecting/deleting options (which would boil down to
string operations). Or, you could just do the string manipulation
directly in your queries, whichever is easiest for you.

Here's a link to an article that discusses using inheritance for dynamic
content questionnaires (but, it may be overkill for what you need):

http://www.varlena.com/GeneralBits/110.php

--
erik jones <erik(at)myemma(dot)com>
software development
emma(r)