Three Options

[garrensilverwing]

I am working on a website for a chess club and one of the features I am going to add is a database of all the games played at the club or sponsored tournaments. I have three ways of going about this:

1. Store the games in their raw PGN state.
2. Extract all the data to store in separate columns in a table.
3. Extract all the data and then store it with the PGN in the table.

Code wise I already have the code to extract the data from a previous post but I am looking for the most efficient way to do it. From the get go there will probably not be many games but there is potential for a lot of games. There are three things I want to do with the games:

• List the games based on their data (excluding the moves). With a search feature.
• (Eventually) Have a clickable javascript chessboard where you can play through the game on the site.
• Download the games in a PGN format (either the full database or whatever they come up with the search.

I am leaning toward the second way because coding it to reconstruct the PGN file would be easier (and probably less server-taxing) than having it deconstruct the PGN every time someone views the game Also it will only include relevant information. I am just looking for opinions from professionals since I am just an amateur! Thanks!

[marshian]

All those "PGN" files you are talking about... are they png files?

Basically, you need raw data (PGN files?) and data that can be deducted from that raw data. I'll enumerate all of the possibilities.
t1 and t2 are the time required to extract the data (t1) and to restore the raw data (t2).
m1 and m1 are the memory requirements for the data (m1) and raw data (m2).
The given times are assumed if you need both the raw and extracted data.
1. Store only the raw data. Time: t1, memory: m2 (more time, more memory)
2. Store only the extracted data. Time: t2, memory: m1 (less time, less memory)
3. Store both. Time: 0, memory: m1 + m2 (least time, most memory)

As you see, the time/memory-efficiency of your program/script will depend heavily on the chosen method. Usually time is more important than memory though, especially when talking about storage memory (like a HDD). As a server application, if you think it will be executed a lot, I'd certainly go for the time-efficient method most of the times. Servers should be able to handle a lot of requests at the same time. Imagine your script (apart from the above mentioned times t1 and t2) takes 500 ms to execute. Take 500 ms for t1 and 250 ms for t2. Using the first method your script executes in 1000 ms, so you can serve one client every second. Using the second method, it is 750 ms, or 1.33 clients per second. However, using the last method it only takes 500 ms, the equivalent of 2 clients per second. Of course these are fictional numbers, but they're just to give you an idea of how to look at it.

Personally, in this case I'd go for the last option in the first place and change to the second one if the database is growing out of proportions.

I hope this helps (:

[misson]

If you're doing anything more than regurgitating PGN data (which you are), option 1 isn't worthwhile. Whether you pick option 2 or 3 is a matter of how much disk space you have remaining in your quota, and whether certain functionality is easier to perform on PGN data than your parsed version.

All those "PGN" files you are talking about... are they png files?

PGN: Portable Game Notation.

If you're ever really concerned about minimizing space usage, have a read at "Programmer Puzzle: Encoding a chess board state throughout a game".

[slacker3]

SQL:
I would store this values separately

[Event "F/S Return Match"]
[Site "Belgrade, Serbia Yugoslavia|JUG"]
[Date "1992.11.04"]
[Round "29"]
[White "Fischer, Robert J."]
[Black "Spassky, Boris V."]
[Result "1/2-1/2"]

and this one just as one varchar:

1. e4 e5 2. Nf3 Nc6 3. Bb5 {This opening is called the Ruy Lopez.} 3... a6
4. Ba4 Nf6 5. O-O Be7 6. Re1 b5 7. Bb3 d6 8. c3 O-O 9. h3 Nb8  10. d4 Nbd7
11. c4 c6 12. cxb5 axb5 13. Nc3 Bb7 14. Bg5 b4 15. Nb1 h6 16. Bh4 c5 17. dxe5
Nxe4 18. Bxe7 Qxe7 19. exd6 Qf6 20. Nbd2 Nxd6 21. Nc4 Nxc4 22. Bxc4 Nb6
23. Ne5 Rae8 24. Bxf7+ Rxf7 25. Nxf7 Rxe1+ 26. Qxe1 Kxf7 27. Qe3 Qg5 28. Qxg5
hxg5 29. b3 Ke6 30. a3 Kd6 31. axb4 cxb4 32. Ra5 Nd5 33. f3 Bc8 34. Kf2 Bf5
35. Ra7 g6 36. Ra6+ Kc5 37. Ke1 Nf4 38. g3 Nxh3 39. Kd2 Kb5 40. Rd6 Kc5 41. Ra6
Nf2 42. g4 Bd3 43. Re6 1/2-1/2

So it's easy and fast to search for specific games.


http://chessflash.com/chessflash.html
To display (or download) selected games you just have to concatenate the whole game data.

Display it by appending your gamedata after "&pgndata=", for example

<div><object type="application/x-shockwave-flash" data="http://chessflash.com/releases/latest/ChessFlash.swf" width="100%" height="350"><param name="movie" value="http://chessflash.com/releases/latest/ChessFlash.swf" /><param name="flashvars" value='orientation=H&tabmode=true&light=f4f4fF&dark=0072b9&bordertext=494949&headerforeground=ffffff&mtforeground=000000&mtvariations=FF0000&mtmainline=000000&mtbackground=ffffff&pgndata=[Event "F/S Return Match"]...   game data here   ...43. Re6 1/2-1/2 '/></object></div>

Just an suggestion ;)


btw,
http://jost.byethost2.com/xmas.html

[garrensilverwing]

thanks for the input, generally PGN format games are very low memory and it will probably take a long time to reach any kind of memory limits so i think i'll go with the third option, to make it easy