Best way of doing..

socoolnetwork

Hey everyone, I'm working on a Flash Facebook game, and I'm wondering what is the best way to do the following.

Each player can setup their environment, say a park, and plant different things etc, and it would be straight forward except to say that each player can setup multiple parks to mess around with, what would be the best structure for that in a mysql database? A park would be a large array, perhaps up to 60x60 cells.

Thanks for any advice.

watdaflip

If you are saying each player could have multiple parks, each with their own 60x60 grid then I would first setup a table of parks

This parks table would have a column with a park id, a player id, and possibly a label or name the park.

And then depending on how you wanted to have things setup for the 60x60 grid I would setup a table for the things that get planted.

If everything was of a uniform size of 1x1, then I would create a table that would have:

a column for the park id, a x coord, a y coord, and then an id for the type of thing planted.

If everything was not uniform, and you could plant something that is 10x20, or 5x10, or 13x17, etc. I would create a table with:

a column for the park id, a start x coord, a start y coord, an ending x coord, an ending y coord, and a column for the type id of whatever is planted.
The start coords would be the top left of the object, and the ending coords would be the bottom right corner of the object.

And then either way have another table with a list of id's for whatever is planted.

This is all based on assumptions of what your wanting.. but based on the assumptions I made that's how I would do it. Hope it helps

socoolnetwork

watdaflip;10980569 wrote:
If you are saying each player could have multiple parks, each with their own 60x60 grid then I would first setup a table of parks

This parks table would have a column with a park id, a player id, and possibly a label or name the park.

And then depending on how you wanted to have things setup for the 60x60 grid I would setup a table for the things that get planted.

If everything was of a uniform size of 1x1, then I would create a table that would have:

a column for the park id, a x coord, a y coord, and then an id for the type of thing planted.

If everything was not uniform, and you could plant something that is 10x20, or 5x10, or 13x17, etc. I would create a table with:

a column for the park id, a start x coord, a start y coord, an ending x coord, an ending y coord, and a column for the type id of whatever is planted.
The start coords would be the top left of the object, and the ending coords would be the bottom right corner of the object.

And then either way have another table with a list of id's for whatever is planted.

This is all based on assumptions of what your wanting.. but based on the assumptions I made that's how I would do it. Hope it helps

Thanks for the reply. Well to give you a bit more info, 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 park 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.

Bjom

Just create tables for every distinct set of information. "Player" is such a set. "Park" another.

Create linktables to bring them together if you want to allow for maximum flexibility in further design, like so:

ID, playerName, playerPwd, playerWhatever
1, myPlayer, myPwd, myWe
2, ...

ID, parkName, parkWhatever
1, myPark, myWe
2,...

ID, playerID, parkID
1, 1, 1
2, 1, 2
3, 2, 3

etc.

Read up on DB design, normalization etc. Takes only a few hours and saves you a lot of headaches later. There are tons of good tutorials out there.

Bjom

watdaflip

For the ground level table, I would go ahead and put an auto increment on the table in case you need to select any individual grid element individually. I would then create a index between the player id and the park id.

For the other layer, depending on how many there will be per park I would just make each object a individual record in the table. Assuming you don't plan on having thousands per park, it shouldn't be an issue. Just make sure you have the appropriate index's setup.

The structure of your tables are high dependent on not only what data you need to store but how you intend to use it. I assume you plan to do something with each object to the point where you need to select them, and possible update a piece of data for them, in which case its useful to have individual rows for each.

Databases are designed to handle large amounts of data, even if the table gets up to 5-10 million rows if you index things correctly the queries will still only ake a fraction of a second to run.