Database Design

[girlzlover12]

Hi I am developing a php game which involves generating a map of tiles but I am having difficulties in figuring out how to store the information for the large number of tiles

 

I plan on having

cities 25X25 (625 tiles)

town 15X15 (225 tiles)

garden 20X20 (400 tiles)

indoors 20X20 (400 tiles)

garden and indoor tiles will also have an item layer (same size).

 

owner ship of each tile needs to be track able to the user along with up to 6 other values needing to be stored.

 

I expect to keep about 60,000 users worth of data in a DB (probably 10-15k towns or cities)

 

I am worried about a good way to store the info I have come up with 2 ways to do it so far.

 

1. Using TEXT or LONGTEXT store all the info for each tile in 1 column of the db and using string explode to separate the values.

 

Cons.

-Huge rows with up to 625 Columns (might go over the limit)

-editing 1 tiles properties would require loading the whole row.

 

2. Store each tile as a different row in separate tables for each type.

 

Cons.

- if half of cities/towns are cities that table would need to hold >4.6mill rows

- Generating a city would require 625 queries to read the values.

 

One solution to these problems could be caching info but recreating the cache file would still require huge amounts of queries to execute.

 

What would be an efficient way to store this information or should I be using different technologies to do this.

 

I could also use help with an efficient way to store inventory info for each user.

 

Is there a way I could combine all the tiles into one image and generate an image map. Could this be a better way to go about this?

 

Any help is much appreciated.

[stuffradio]

What database system are you using?

[girlzlover12]

well I have taken a class in oracle sql and have so far done the most work on mysql dbs

[girlzlover12]

no-one knows how I can do this? I have been looking everywhere but can't seem to find a clear answer.

[stuffradio]

Well... are you sure you should be using MySQL? Maybe you should use Oracle or some other system. Do some research on the performance for large scale data handling.

[girlzlover12]

Well Im trying to find out how I should structure the DB I haven't been able to find anything about it.

 

I have looked up limitations and I cant afford oracle. The limit on tables is somewhere around 1700 and rows ~4.5 mil and each row can contain ~65,000 bytes

 

I would like help in figuring out how I can store the information for every tile in 15,000 towns and cities and also also each opf the users gets a garden and house. There is too many tiles in total to keep in one table and I cant split it into one per town because there would be to many tables.

 

Also I am worried about having to perform 400 mysql queries to build the output for each page even if I use cache files it will still require a ridiculous amounts of queries.

 

could I use gd library to save each town as one image and change the image when a tile gets changed? I would probably still need to run millions of queries at a time.

 

I have already spent weeks trying to figure this out.

 

[stuffradio]

Maybe there is a way you can just use a database to store a basic template for the tiles... and just use an array to store actual locations.

[girlzlover12]

how can I store user information in an array so I can retrieve it next time?

 

could I use csv files for each users home and garden? That way I would have 60k csv files and I could parse them for the info. I could store all the info for each tile in a separate field in the grid.

 

 

[keeB]

First thought: I'd store the map in a binary format in a single table of the database.

Second thought: If you're using a custom list of sprites i'd still go with the first thought, it'd be an extremely compressed format

 

0 = unfilled

1 = spirte 1

2 = sprite 2

.

.

.

n = sprint n

 

Then I would separate these by some other special character like |

 

So a blank map would look like this:

|0x625|0x225|0x400|0x400|

 

Then I'd read it in chunks and assemble.

 

After typing that, I'd just use a different column of the table for each.