'Non-'Unique indexing

We wanted to make a column in our MySQL database table (using InnoDB) nullable, but use the column as part of a composite key.

unique key C1,C2,C3,C4

C4 can be null

C1 C2 C3 C4

enter the following:

x y z a

goes into the table ok

enter those again - error that we break unique constraint.

This is as expected.

enter

x y z null

goes into table ok

enter those again - they also enter fine, and select * from table shows two separate row entries.

So, basically, you can have a nullable field in a composite unique index, but if the column is null, you loose the unique index checking.

I don't know about other DB's, but it is a shame that the null can't be part of the uniqueness of the index.

Playing Badminton through Clearpress

Yesterday I launched, finally, my badminton ladder web application for our sports and social club. Initially, I write something about 3 years ago, which used flat csv files and a cgi script for each page, which dealt with generating the html and processing results, and updating the ladder, and....

All not very practical, but at least showed what I could do at the time.

Earlier this year we had a change of hardware for our webservers, and we lost the functionality, due to the time lag of all the files being kept up to date as they changed on the multiple servers. A big problem.

So I decided it was time for a rewrite, which I did using Clearpress (http://clearpress.net/), trialling out git and github at the same time (I like this so much more than sourceforge! and svn).

I have blogged about Clearpress and its MVC framework before, so I won't spend time doing so, but with a little work, the setup used 5 tables in a database to produce a reliable system.

team - stores a team name, wins, losses and gives them a unique identifer
player - stores a player name, email and gives them a unique identifier
player_team - join table for a player to a team
ladder_type - our ladder has three sub ladders, to deal with new teams and those which haven't played for a long time
ladder - links team/position/ladder_type

The whole thing can be found on github

git://github.com/setitesuk/badminton-ladder.git

It is currently set up to deploy using a SQLLite database, but in production use, we are using a mysql database, and the schema is there. You just need to modify the config.ini file to use a mysql database, which is supported through clearpress.

So, if you are after a web app badminton ladder, then take a look. It is all available as Open Source (GNU Public Licence).

Next, to create a Tennis Competition app.