Cool man.
First thing is now you have the "core engine" for the database.
I mean the core logic that drives the database, everything else is just built around this.
But now you need data so lets start with FEN strings, as you pointed out, Ply counts, so you ask yourself do I need to store this in the database or can it be calculated easily. In the moves table I have move_number, if it can be calculated from that no need to store it, otherwise just add two columns to the moves table and store that data.
Next grab a bunch of PGN files, I would say 8, and get the PGN specification itself.
Games need to be a mix, with comments, with no comments, draw, white win, black win, stalemate, checkmate, 3 fold rep, 50 move, insufficient material, blah, blah.
You then must manually create the data from the PGN files, look at the data using a text editor.
A very laboureless (sic) exercise but essential, you will shape the rest of the database doing this and will learn how the data works, you can't write code before you do it manually first.
For example, the games table will be created from the info in the PGN files, player names, tournament, location, date, round, time controls, result, blah, blah.
Bit Boards
I will use an empty board with a white king on A1 and a black king on H8 as an example.
Use A1 as the MSB (Most Significant Bit) and H8 as the LSB (Least Significant Bit)
In the facts table you will only ever have 2 rows to represent this position, one for white to move, one for black to move.
For the WK (White King) field in the table you will have a 1 bit followed by 63 zero bits, the actual number is not important, you are more interested in the bit flags (1 or 0)
For the BK (Black King) field, 63 zero bits followed by a 1 bit, the actual number in this case is 1.
Then player to move, zero for white, one for black or the other way around if you want.
For all other fields, zeros.
Once you have the database shaped, and it has the data you created manually, somebody writes code, you then force feed 100 PGN files into the database, check it out, tweak a few problems.
Then do 1,000 next, then 10,000, then ...
Then do SAS for all the fancy stuff.
Everything works?
Hit the button, start loading everything.
What will your key be on the games table, to identify every single game?
Then jump ahead in the future and you have the database and an interface like on this website using Menu | Learn | Analysis
Good stuff, game_designer. I'm busy with another project today (I thought this thread had been forgotten forever) so today I don't have time to look into all the claims made in the different posts. Two quick comments, though: (1) I'd never used the Analysis tool on this site until you mentioned it today. I posted a screen snap of what it gave me for the Hillbilly Attack of the Caro-Kann Defense as an example in case other users have never seen that interface, either:
(2) It looks like your facts table is trying to use all the info from an FEN string, so wouldn't you want the other two values from that string...? (https://en.wikipedia.org/wiki/Forsyth%E2%80%93Edwards_Notation)
()
Halfmove clock: This is the number of halfmoves since the last capture or pawn advance.
This is used to determine if a draw can be claimed under the fifty-move rule.
()
Fullmove number: The number of the full move. It starts at 1,
and is incremented after Black's move.