1
\$\begingroup\$

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!

Improve this question
\$\endgroup\$

3 Answers 3

Reset to default
2
\$\begingroup\$

I'd really suggest teaming up with someone who's got database experience and have them work this out with you. If you're out to create a Facebook game, it's not just about data storage formats like you are talking about, but also how your database is designed for security, stability and performance.

The best thing you could have happen as a game developer is have a big success on your hands with lots of players. The worst thing you can have happen as a game developer is a big success with lots of players, and a back-end system that's not designed to handle it.

Put it another way. You don't need a MySQL database for your game, you need a system your game can communicate with in a secure way to get and set data values related to a game session. This may or may not be via MySQL. Your game definitely shouldn't be written to directly talk to the database, but rather go via a web services (or other) proxy that can authenticate all requests and handle them in an appropriate manner.

Improve this answer
\$\endgroup\$
5
  • 3
    \$\begingroup\$ Agreed, at the same time, IMHO all programmers need to know databases at this level regardless of if they are going to be writing them for their jobs in the future. This project could serve as a good point to start learning and as they don't know it yet. I'de say it's probably the biggest benefit they will get from this project if they implement all of the work themselves. You're still right though, in the short term, a DB programmer would help. \$\endgroup\$
    – brandon
    May 19, 2011 at 20:17
  • 1
    \$\begingroup\$ Yea kind of an interesting challenge. A programmer that comes in and asks, "How do I make art?" might be better off just getting an artist partner, but on the other hand maybe they really can learn art and do it. Irrespective of the "who", I really do stress the idea of adding a layer between game and database. That lets you restructure the back-end database system in the future without breaking the game for example. \$\endgroup\$
    – Tim Holt
    May 19, 2011 at 20:42
  • \$\begingroup\$ Finding someone else to do this work was my first reaction, but it proved to be a hard thing to do, finding people who know what they are doing and have experience with this kind of work AND is affordable is almost impossible. I did find someone eventually but they keep dissapearing and the project is not moving forward, so I'm left with not much choice but to do it myself, hence my question :) oh and I should mention that I'm using AMFPHP to communicate to the php/mysql. All I need is to understand how to design the database better. \$\endgroup\$
    – Phil
    May 19, 2011 at 23:28
  • \$\begingroup\$ It's not possible to write to a DB directly from flash. Although there is a lot you can screw up in your intermediate layer, mainly validation and escaping which will enable SQL injection attacks if not done properly. \$\endgroup\$
    – bummzack
    May 20, 2011 at 6:11
  • \$\begingroup\$ If you're worried about escaping you're doing it wrong. Your lowest level (aside from the DB itself) should be built on parameterized queries. Anything else and you should be fired. \$\endgroup\$
    – user744
    May 20, 2011 at 11:11
1
\$\begingroup\$

I would probably design it like this (you can add more fields where required..):

Player

  • PlayerID
  • Nickname

Park

  • ParkID
  • PlayerID (Foreign Key)
  • Name

ParkObject

  • ObjectID
  • Flash Class identifier

GroundLayer is a many-many relation table between Park and ParkObject

  • ParkID (Foreign Key)
  • ObjectID (Foreign Key)
  • Index (position in the array)

UpperLayer again, a many-many relation between Park and ParkObject

  • ParkID (Foreign Key)
  • ObjectID (Foreign Key)
  • Index (position in the array)

Edit: Edited in the index/position for GroundLayer and UpperLayer (this is to determine the location of the tile in the 2D array)

Improve this answer
\$\endgroup\$
0
\$\begingroup\$

I would make a table called "park_ground_state" in that table, you would have:

  • park_ground_state_ID int auto-increment
  • state_data char[16] not nullable
  • player_ID int foreign key to the player table

Since you probably don't need to query the park cells separately, you can just grab them all at the same time from a char array.

It seems odd to some people to store separate entities in the same field, but in reality, the park state is a datastructure. In this case an array. You just want to grab the whole array at once and you never query on elements of the array from the database.

Improve this answer
\$\endgroup\$
3
  • \$\begingroup\$ Forgot to mention. Since I put player_ID in the park_ground_state table, the players have a one to many relationship with parks. So they can have multiple parks. \$\endgroup\$
    – brandon
    May 19, 2011 at 19:25
  • \$\begingroup\$ Also, if you make state_data a nchar[16] array. You can have many more options for states of the individual cells. Each separate character would represent a state for a cell. \$\endgroup\$
    – brandon
    May 19, 2011 at 19:28
  • \$\begingroup\$ Hmm, but each tile is basically going to be a string, which will be a library ID for Flash, as all the assets would come in at run time from a .swf, how I would I store all 16 strings though as one record, ah maybe as 16 columns? What about though the trickier issue of the 2nd layer 2D array? \$\endgroup\$
    – Phil
    May 19, 2011 at 23:25

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .