> If you don't want to store IPs for registered users, I'd use:
>
> user_id INTEGER,
> ip cidr,
> CONSTRAINT must_have_userstamp
> CHECK ( user_id IS NOT NULL OR ip IS NOT NULL)
>
> ... and yes, I'd use a functional index to look it up, or even a
> trigger-maintained cache of the text representation if I had to. Then
Ok, this is an idea. And I do agree that multifunction fields are a
potential pain in the distant future.
My questions:
1. What extra tax will this constraint levy on an INSERT or UPDATE on
this table? There are about 100,000 inserts a day, and over three
times as many UPDATES. The concurrency is pretty high -- I mean
sometimes 1,000 users at the same time but no more than that. If the
additional cost of insertion/updating is not too heavy, I suppose this
could be a nice approach.
2. Why not have an INET field...why a CIDR? What's the benefit? It
stores those pesky ".../8" type additional data which one has to mask
with functions. Would INET work just as well?
3. Storage wise does this add significantly? How much space does an
INET field take as opposed to, say, a VARCHAR field?
4. Most importantly, how would you structure the index for this? I
would much rather have a fast "=" in my sql's WHERE clause. No "OR"
etc. Any thoughts?
Thanks