Re: proposal: operator exclusion constraints with cardinality

On Sun, Nov 1, 2009 at 10:49 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Jeff Davis <pgsql(at)j-davis(dot)com> writes:
>> It could go something like this (syntax still open for discussion, for
>> illustration only):
>
>>   EXCLUSION (room     CHECK WITH =,
>>              attendee CHECK WITH =,
>>              during   CHECK WITH &&)
>>     CARDINALITY 30
>
> There's an old design principle that says "the only good numbers in
> computer science are 0, 1, and N" -- that is, if you need to allow more
> than one of something, you should have no hard-wired upper bound on
> how many of them you allow.  The reason that meme comes to mind is that
> I'm having difficulty seeing applications for this type of constraint.
> I can certainly believe that people might like to enforce constraints
> like "no more than N people are signed up for class X".  The problem is
> that they won't want the *same* N for every class, and that's what this
> constraint design seems to require.  What they'll want is N drawn from
> some other table entry about the size of the classroom.  If you can't
> support that then the design isn't fully baked yet.
>
> (Of course, the reason UNIQUE constraints are good is that they
> correspond to the number 1 ;-))

Following that thought, in this particular case it seems like you could do:

EXCLUSION (room CHECK WITH =,
attendee CHECK WITH =,
foobar CHECK WITH =,
during CHECK WITH &&)
and then also
CHECK (foobar >= 1 AND foobar <= 30)

I'm a bit baffled by what this constraint is trying to represent,
actually. I would have thought that the example would be room and
during constrained to a quantity of 30, and the solution would be to
have attendee. Since you already have attendee as part of the
constraint, I'm a little mystified as to what the quantity of 30 is
supposed to represent, but it any case it seems like you can get the
effect with an extra field - which also allows you to do things like
variable room-sizes (by setting up triggers that arrange to copy the
room size into a column of your scheduling table so that you can then
check that the attendee number is less than the room size).

...Robert