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@xxxxxxxxxx>
software development
emma(r)
