On Mon, Aug 23, 2010 at 8:21 PM, Craig Ringer
<craig(at)postnewspapers(dot)com(dot)au> wrote:
> On 08/24/2010 07:07 AM, san man wrote:
>>
>> Thanks for the replies.
>> David, I would have normalized it to 2 or more tables, but the number of
>> bar-delimited are not fixed and as new data are added the maximum number
>> of these values may change. Also, the problem with like I think is that
>> matching is not strict and thus might give spurious hits.
>
> You are trying to simulate arrays using your own custom setup.
>
> If you switch from using pipe-delimeted text to an array, you can use the
> PostgreSQL array operators to do what you want. You even have (limited)
> indexing options.
>
> As for normalizing the data out to another table: Sometimes performance
> concerns render that undesirable. Arrays can be very useful for fairly small
> amounts of data that's tightly associated with a given record, especially
> things like search keys.
>
> There's a reason that tsvector is implemented how it is, rather than as a
> breakout table full of keyword associations. It has to be fast, and
> indexable. It sounds like the OP's problem has the same requirements.
I think if I was going to normalize it I would normalize into a many
to many lookup table with the lookup table being in the middlle for an
it from each table. It's no full text search, but it would probably
be faster than one big second table with lots of redundant info in it.