Arthur Fuller
artful at rogers.com
Wed Oct 27 12:09:43 CDT 2004
From time to time I ponder the following two databases, trying to come up with the optimal design in terms of both space and performance. This is strictly a question of personal interest, and I have no commercial interest in either solution. I simply find them interesting problems, and I thought I'd trot them out in search of feedback from my colleagues here. 1. A database that records chess games. It strikes me that perhaps the most compact way to store a game is by using the modern notation for the moves themselves. But in addition to recording the sequence of moves, the database would also be expected to record situations and be able to compare them. I.e. given two sequences, A and B, that both result in exactly the same position of pieces, irrespective of the number of moves it took to get there, the database should be able to detect this as quickly as possible. For example.... aha! This is exactly the same position that Bobby Fischer faced in year 19xx, when playing somebody at some tournament, but they got here in 11 moves and the current players got here in 13 moves. (The idea behind this requirement is that certain positions have known solutions, i.e. paths to checkmate.) 2. A music database that records (let's keep it simple in version 1) melodies and single-line compositions (i.e. ignoring instrumentation, harmony, counterpoint, etc.). The idea here would be to compare any two rows and determine whether they are identical. For example, George Harrison v. the Ronnettes, for "My Sweet Lord" and "He's So Fine" respectively. Ideally, this database should also be able to see past the selected key (in the musical sense), and also the tempo (piece A is identical to piece B but played twice as fast). Perhaps version 2 could also detect that melody A is identical to B except that it is inverted (upside down) or perhaps retrograde (backwards) or even retrograde inverted. Ok, database designers. There you have the specs. Any brilliant ideas out there for solutions? A. P.S. Although these are in fact strictly database issues, I am not going to cross-post to the AccessD and SQL lists because they are so obviously unrelated to the immediate problems most of us have when posting there.