Re: problems maintaining boolean columns in a large table

Richard Huxton wrote:
> On 09/02/10 11:25, Ben Campbell wrote:
[I was talking about moving a "needs_indexing" flag out of a big table
into it's own table]
>> But my gut feeling is that the flag would be better off in it's own
>> table anyway, eg:
>>
>> CREATE TABLE needs_indexing (
>> article_id integer references article(id)
>> );

> That sounds sensible to me

Cool - glad to know I'm not suggesting something totally insane! I never
can quite tell when I'm doing database stuff :-)

> Oh - you might want to consider how/whether to handle multiple entries
> for the same article in your queue.

I settled on:

CREATE TABLE needs_indexing (
   article_id integer REFERENCES article(id) PRIMARY KEY
);

The primary key-ness enforces uniqueness, and any time I want to add an
article to the queue I just make sure I do a DELETE before the INSERT.
Bound to be more efficient ways to do it, but it works.

Thanks,
Ben.