Cheers,

I'm setting up a db of tournament results. I don't want matches against
self which I managed to solve, but I also don't want the same players
playing more than once on a given date. This is where I'm stuck.

The little table is
create TABLE test (
w_id smallint,
l_id smallint,
date date,
unique (w_id, l_id, date),
check (w_id <> l_id)
);

This still allows for things like:

w_id | l_id | date
------+------+------------
1 | 2 | 2001-01-01
2 | 1 | 2001-01-01

where the second row should be offed. How to go about?

On Thu, 30 Sep 2010 22:44:35 +0200, Dorky Dork wrote:


Check constraint, specifying that w_id must be < (strictly less than)
I_id will solve your problem.



--
http://mgogala.byethost5.com

On Fri, 01 Oct 2010 00:29:32 +0000, Mladen Gogala wrote:

But if w=winner and l=loser, what happens if team 4 beat team 3?

The problem needs to be rethought. And what about ties?

On Fri, 01 Oct 2010 10:41:00 +0000, HoneyMonster wrote:


Then, you would have to change your data model You would have columns
t1,t2, and w. First team, second team and enumerated column "w" which
would signify the winner.
On a more subtle level, you're mixing categories: teams that played the
game and the outcome of the game are not the same type of objects. You
shouldn't pack the information about the game together with the
information about the team.



--
http://mgogala.byethost5.com

On Fri, 01 Oct 2010 13:32:24 +0000, Mladen Gogala wrote:

Quite so. Ted Codd and all that.
But I take umbrage at the "you". It wasn't *I* who was suggesting the
original design.
Your point about the "winner" column is precisely what I was hinting at,
without doing OP's homework for him.

Mladen Gogala wrote:
No ties in tennis.

I don't see how this solves anything. Then again I'm a complete SQL dork
so ...

With the above, I would be left with the original problem. With an
additional winner column, I still wouldn't want a (t2, t1, w) inserted
if a (t1, t2, w) existed (given the same dates). Back to square one.

I'm not sure how orthodox this is, but I did this:

create table test (
w_id char(4),
l_id char(4),
date date,
unique(w_id, l_id, date),
check(bla('test', w_id, l_id,date)),
check(w_id <> l_id)
);

where bla() is a little plpgsql hack which essentially does an

EXISTS
(SELECT 1 FROM test WHERE w_id= 'l_id'
AND l_id = 'w_id' AND date = 'date').

Seems to worky work. So far at least.

I don't think this is the problem. The problem is that a match between
two opponents is a reflexive relation, (p1,p2) = (p2, p1). Tables in SQL
are not, unless enforced. See the above caveat though.

On 2010-11-06, Dorky Dork <dork (AT) dorky (DOT) com> wrote:
having check constraints that do table lookups can be probematic as
the check is only evaluated at (or update) time, probably harmless in this
case asthe check is an exclusion check that woult catch any attempt to
violate it.


here's another way to do it, require reflex entries instead of
forbidding them.

create table test (
team char(4),
opponent char(4),
teamresult integer,
opponentresult integer,
date date,
check(team <> opponent),
check(teamresult = 2-opponentresult),
FOREIGN KEY (team,opponent,date,teamresult,opponentresult)
REFERENCES test(opponent,team,date,opponentresult,teamresult)
ON DELETE CASCADE,
UNIQUE (team,opponent,date),
UNIQUE (opponent,team,date,opponentresult,teamresult) -- needed for FK
);

if today WINR beat LOSR and DRW1 drew agaisrt DRW2

insert into test values ('WINR','LOSR',2,0,'today'),('LOSR','WINR',0,2,'to day');
insert into test values ('DRW1','DRW2',1,1,'today'),('DRW2','DRW1',1,1,'to day');

note: you need to do the inserts two rows at a time like above.
or make the FK deferrable, defer it, and do the inserts in a
transaction.

The foreign key and unique constraint will keep things consistant.
and the enforced duplication of records will make queries easy to write.

If your league does not allow draw results a boolean can possibly be
used instead of the integer I used.

Doing it this way makes the table about 3 times larger (twice as many rows,
more columns) but the advantages of leveraging the bult-in
integrity checks, and simplifications to queries may offset that.

Also see the documentation for postgres character data types, there is no
advantage of using char(4) over using text (other than char(4) does an
implicit length check and truncates or pads with spaces)

--
ɹǝpun uʍop ɯoɹɟ sƃuıʇǝǝɹ⅁

On 06.11.2010 07:55, Dorky Dork wrote:
If you enforce that t1_id < t2_id then only (1,2,2001-01-01) is valid in
the DB and not (2,1,2001-01-01). Make the combination of all three
unique and you avoid two teams with the same date in the DB.

No, w/l column would not be part of the uniqueness enforcing columns.
For those you only need the three mentioned above.

You are not really comparing those column values against column names,
are you?

But it is way inefficient - especially since your table will grow over
time. Even with an index you have the callout to plpgsql which isn't
necessary.

Enforcement is easy, as Mladen has pointed out. I do not know Postgres
well enough but I guess you can even create a before insert trigger
which will exchange columns if id ordering is wrong. So you can do

insert into foo values (1,2,2001-01-01)

and

insert into foo values (2,1,2001-01-01)

and in both cases the record would read (1,2,2001-01-01).

And I do also think that you assign semantic to the column ordering.
Note especially that the distribution of ids across the two columns is
dependent on the outcome of the game. If you pick Mladen's approach you
can create records beforehand and later update the record with the
winning team info.

Kind regards

robert

--
remember.guy do |as, often| as.you_can - without end
http://blog.rubybestpractices.com/

Robert Klemme wrote:
Yes. This is much better.

No, just sloppy nomenclature.

I see that now.

This made me see the light. Reading up to this, a concern was that I
would have to spread the constraints (id1 < id2) into the code that
feeds/fixes/updates the db. I took a quick look at the documentation and
the triggers would seem to do this.

Yes, I agree now that this is the way to go.

Thanks, folks, for your time.