I'm at the planning stages for a facebook game, it will be similar to farmville, but will feature parks. I've been a Flash game dev for many years but all this database stuff is new to me so I'm having trouble getting my head around what's the best way to handle the data.
I know I need a table for the players, so that's straightforward.
Each player can grow crops/flowers and/or harvest trees etc that are already in the parks. The way I'm setting it up is that the parks are made up of 2 layers, or 2 2D arrays, the bottom array or ground tile array is just big tiles, and in a 8x8 grid, so that seems pretty straightforward as well, just store 16 entries for each part in a "parks ground tile table", but first question about that. Those 16 entries would correspond to each park, so would I still use the auto increment on the ID, or some other kind of ID to represent them as a group somehow? I'm just thinking of what's the fastest way to grab those 16 from the database.
Next onto the trickier stuff which is the 2nd layer of the park or the items/objects/crops 2d array. Each player has to have their own version of this 2D array, so first qusetion is how do I set that up in the database? I was thinking maybe having a "park items array" and then each feature (tree, crop) etc for each player would just be stored in that array, but I can see that getting huge really quick, so not sure how to do that? or if the size of that would be an issue?
Then on top of that, each player can maintain multiple parks :p...again what would be the best database design to maintain all of those?
Thanks for any help!