Getting all Fifth Normal Form on your ass

I've been reading up on database normalisation and thinking about how the London Indiepop Eyespy website would work.

Data normalisation is incredibly interesting, but not quite as important as what the site looks like, form dictates function here.

In the original pub game of indie eyespy, you'd spot a band member then look up how many point they are worth.

There's Stuart Murdoch from Belle and Sebastian, four points!

As many of the bands are quite obscure, it would be less likely that you'd know the names of band members, but you could still get points for identifying the person.
There's wassisname the drummer from Camera Obscura, would be worth the same as correctly remembering his name was Lee. Although, if playing indie eyespy competitively with friends, the person who get his name would claim the points.

So here we have the dilema for a website, what should it list:-

• Check boxes for each band member by name for each band
• Check boxes for numbered members for each band
• Text box for number of members spotted and leave the rest to banter

With the limitless possibilities of computing power, I'm tempted to go for option one, and make life easier for folk who can't remember names

As bands evolved and lineups changed so did the way points were allocated, for example spotting Gav from Camera Obscura would get you three points, but if you instead noted it was Gav the bass player from Stabiliser, it would be five points. However, you couldn't then claim eight points for identifying him as playing in both bands. You only get points for one person once.

So the website would need a way of dealing with the same person in many bands.

With the Skilmo website, this sort of problem was easily resolved when it processed the checklist page, each skill was only logged once, for example tunisian crochet only counted once if you clicked it in both the textile arts category and the crochet category because the program ignored categories when logging stuff.

But with indie eyespy, because of the different points values this isn't possible, and rather awesomely, this is precisely in what 5th Normal Form is about in database normalisation theory.

On the database side of things I'd need three tables:-

• List of people and their nominal point values
• List of bands and their points values
• List of which people are in which bands

Then every time the website is accessed a list is constructed by SQL of bands and their members and points values.

Then with PHP an html page is generated which displays this list, with form checkboxes and also generates so neat JavaScript which greys out people who play in different bands when you select them.

Then submitting this form should post a list of people and their points values.

The next page would get a list of the people in the database, and run through the posted data, tote up the score, and log the score.

That seems to work in my head.

Of course the list of names will be normalised with id numbers to cover for there being more than one person with the same name, and same with the list of bands.

The list of bands also needs a status column for whether a band is currently active, on haitus or split up. Cos, of course, active bands are worth more points than bands that are on haitus, but not as much as a band who split up in 1989.

And the list of which people are in which bands needs a column indicating former band members. Because, as I'm sure you understand, spotting the original guitarist from Pocketbooks is worth more points than the current guitarist.