13 Replies - 1421 Views - Last Post: 10 June 2008 - 04:06 AM Rate Topic: -----

#1 girlzlover12  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 13
  • Joined: 26-May 08

Database Design

Posted 26 May 2008 - 09:26 PM

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).

ownership 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.

Is This A Good Question/Topic? 0
  • +

Replies To: Database Design

#2 Trogdor  Icon User is offline

  • D.I.C Addict
  • member icon

Reputation: 15
  • View blog
  • Posts: 627
  • Joined: 06-October 06

Re: Database Design

Posted 27 May 2008 - 05:27 AM

basicaly you have 2 ways to store your information:
1: one record per tile, where you store all the data, including ownership, the X/Y and "RoomID" in its columns.
2: compress your data like you said.

the first sollution is obiously the logical choise. Allso your Cons are not very valid.
- Any database should not have a problem with holding and using 4.6 mil records, given proper indexing. If it does, use a better database, change hosting plan, etc.
- If you have the CityID as an indexed field in the Tiles reccords, you can simply get one Citys tiles with one query:
Select * from Tiles where CityID = 6 Order by Y, X
That will give you a reccordset with all the tiles of that city, and ordered in the way you will want to display them (first all reccords with Y=0, then all reccords with Y=1, etc)

You should just try it out in a database, generate a grid for a town, and also pump in dummy data for other towns, do that select and let your script generate the visual output. If you want to do it in html you can just do it with tables and image tags etc, should be doable.
In that test, use the time functions to measure how long it actualy takes to get the data, and to process it.


I assume the rest of the database design is not a problem?

This post has been edited by Trogdor: 27 May 2008 - 05:30 AM

Was This Post Helpful? 0
  • +
  • -

#3 girlzlover12  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 13
  • Joined: 26-May 08

Re: Database Design

Posted 02 June 2008 - 09:26 PM

thank you that was very helpful. I am a little shaky on indexing I havn't used this stuff since I took a class on it in 10th grade.

I guess if I use cache files it will limit how often I have to pull the data.

but there could be 24mill plus tiles just for users homes. I guess I will worry about that when the time comes.

how an I add values to the db and predict what ID will be assigned to it (on increment 1) without calling the row after its been created.
Was This Post Helpful? 0
  • +
  • -

#4 Trogdor  Icon User is offline

  • D.I.C Addict
  • member icon

Reputation: 15
  • View blog
  • Posts: 627
  • Joined: 06-October 06

Re: Database Design

Posted 03 June 2008 - 04:15 AM

after you create a row that has an Identity field, there is a simple way to get the new value of it.
In SQLServer you do a select @@IDENTITY
On your platform of choice there will be something similar.
However, you will hardly need it, except to generate whole "towns".
There you should do:
1: insert the record for the new town
2: get its ID
3: loop through the Y size
4: loop through the X size
5: for each X,Y insert a tile that is linked to the TownID

So with X and Y at 100, you will insert 1 town and 10k tiles.
Of all those tiles you will only need their ID to update them.
If you generate a visual representation of for example one town by doing for example a select * from tiles where townID = 6 order by Y, X then you will:
- loop through the result set to create a tile grid
- generate the imagelink/html for each tile
- wrap each tile image in a link or something that arranges its interaction, and this has to contain its ID
That is the place where you need the IDs. Not at generation.

25 mil tiles.... that is a lot. are you sure you need that much?
If you do, you might want to take a good look at your hosting. Is the machine you run your DB on optimized, big enough, fast enough?
Perhaps you can even go so far as to make a separate TownTiles-table for each town
to keep the sizes down a bit, since 25 mil is unwieldy. But that is something you should only do if you REALLY have to.

This post has been edited by Trogdor: 03 June 2008 - 04:16 AM

Was This Post Helpful? 1
  • +
  • -

#5 girlzlover12  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 13
  • Joined: 26-May 08

Re: Database Design

Posted 03 June 2008 - 03:33 PM

I already planned on having tiles split into tables. I figure 10,000 towns X 400 tiles (20X20) = ~4 mill
Each user will be able to have a garden (20X20) ~60k users is 24mill same with their home.

I thought maybe I could use a code to put together the gardens and homes.
ie. G2443.D453.G4544
Where the first letter is the type of tile and the number is the item id for the item placed on the tile. the .is used as the separator. This way when I run my look it would build across the top then go to the next line until the whole page is put together.

However this still requires looking up each items image separately leading to lots of querries.

View PostTrogdor, on 3 Jun, 2008 - 04:15 AM, said:

after you create a row that has an Identity field, there is a simple way to get the new value of it.
In SQLServer you do a select @@IDENTITY
On your platform of choice there will be something similar.
However, you will hardly need it, except to generate whole "towns".
There you should do:
1: insert the record for the new town
2: get its ID
3: loop through the Y size
4: loop through the X size
5: for each X,Y insert a tile that is linked to the TownID

So with X and Y at 100, you will insert 1 town and 10k tiles.
Of all those tiles you will only need their ID to update them.
If you generate a visual representation of for example one town by doing for example a select * from tiles where townID = 6 order by Y, X then you will:
- loop through the result set to create a tile grid
- generate the imagelink/html for each tile
- wrap each tile image in a link or something that arranges its interaction, and this has to contain its ID
That is the place where you need the IDs. Not at generation.

25 mil tiles.... that is a lot. are you sure you need that much?
If you do, you might want to take a good look at your hosting. Is the machine you run your DB on optimized, big enough, fast enough?
Perhaps you can even go so far as to make a separate TownTiles-table for each town
to keep the sizes down a bit, since 25 mil is unwieldy. But that is something you should only do if you REALLY have to.

Was This Post Helpful? 0
  • +
  • -

#6 girasquid  Icon User is offline

  • Barbarbar
  • member icon

Reputation: 108
  • View blog
  • Posts: 1,825
  • Joined: 03-October 06

Re: Database Design

Posted 03 June 2008 - 04:19 PM

While it will lead to lots of queries, you could always implement caching to work around that - the first queries would be a hit, but any following that would be significantly faster.
Was This Post Helpful? 0
  • +
  • -

#7 girlzlover12  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 13
  • Joined: 26-May 08

Re: Database Design

Posted 03 June 2008 - 07:24 PM

right but the preview for the editor would need to be updated and the process of editing your house/garden would slow the server to a crawl.

I don't know ajax so doing it without reloads is not possible unless someone wants to join me in creating this game and knows how to do it.

is there some way of pulling all the items in a similar way as the towns by having the WHERE include all the item ids at once. (using OR)
$all_items = '234 OR 235 OR 5677 OR 647' 
("SELECT * FROM items WHERE id = '$all_items'" );



would something like this work and how would I have to format it?

This does not help me solve my problem for the home editor. I would like to make a sort of drag and drop interface but that is probably quite hard.
Was This Post Helpful? 0
  • +
  • -

#8 girasquid  Icon User is offline

  • Barbarbar
  • member icon

Reputation: 108
  • View blog
  • Posts: 1,825
  • Joined: 03-October 06

Re: Database Design

Posted 03 June 2008 - 09:12 PM

You're thinking of the IN clause - you could use something like SELECT * FROM items WHERE id IN (234,235,5677,647).
Was This Post Helpful? 1
  • +
  • -

#9 girlzlover12  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 13
  • Joined: 26-May 08

Re: Database Design

Posted 03 June 2008 - 10:10 PM

and this will retrieve all of them?

well then I can use string explode and implode to extract item IDs from my rows and pull the items.

any Ideas on how to make a drag and drop editor?

Thanks for all the help guys. I feel confident I can make this database work thanks to your help.
Was This Post Helpful? 0
  • +
  • -

#10 Trogdor  Icon User is offline

  • D.I.C Addict
  • member icon

Reputation: 15
  • View blog
  • Posts: 627
  • Joined: 06-October 06

Re: Database Design

Posted 04 June 2008 - 02:00 AM

Seperating 60k gardens into 60k separate tables is a bad idea. It will become very very messy.
You could limit the userid range and split on that, say 10k users in each garden-tiles table.
So you will have one for 0-9999 a second one for 10k-20k etc.
Then with 400 tiles each 'segment' will have 4mil records. that is acceptable.

But like i said before, you need to test the database performance, and you need to test _before_ you take decisions like this.

In my experience it is always better to let the queryoptimizer handle complexities then to go around things and doing it in code or even in design.
So unless your realy realy need to spilt your tables, dont do it!
One query will be faster then 400 of them, even on a table that is huge.
Was This Post Helpful? 0
  • +
  • -

#11 girlzlover12  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 13
  • Joined: 26-May 08

Re: Database Design

Posted 04 June 2008 - 12:42 PM

That might work and could increase the speed of all the tables if I apply it to all the tables.

is there any easy way to make the id columns match for gardens and homes so the user id will be the same as the home and garden ids eliminating the need to look up the separate ids from page to page.

then my home and garden tables will look like this

| id | tiles |items |


using GET my pages could be garden.php?id=243

Am I on the right track?

I have been reading into some ajax to make the editor and it seems like its hard to learn. Any advice on good ajax resources that would help.
Was This Post Helpful? 0
  • +
  • -

#12 Trogdor  Icon User is offline

  • D.I.C Addict
  • member icon

Reputation: 15
  • View blog
  • Posts: 627
  • Joined: 06-October 06

Re: Database Design

Posted 05 June 2008 - 04:03 AM

No sorry, you must have missed one of the ideas behind normalisation.
If there is a strict 1-on-1 relation between homes and players, or between players and 'gardenIDs' then these should be merged into the same table.

You might considder splitting garden-type-tiles and town-type-tiles into 2 separate tables, so that you dont have to figure out (for each tile) to wich kind of entity they are linked.

so,
- Garden_tiles , gardens are entirely player-owned.
ID
OwnedBy_PlayerID
ImageID or URL or whatever you use
position_X
position_Y

- Town_tiles (tiles in a town can be owned by individual players. For a neutral tile the OwnedBy_PlayerID field is NULL)
ID
TownID
OwnedBy_PlayerID
ImageID or URL or whatever you use
position_X
position_Y


As for storeage of items, you can do that in 2 ways.
- You can go tiles-centric, where each tile can contain for example an array of itemIDs. This is ugly but probably quite fast, as long as you dont have to search through all tiles where a specific item is.
- You can go Item-centric, where every item contains the tileID of its location. Note that if it can be in a garden or in a town-tile and you decided to split town and garden tiles into 2 tables, you will need 2 IDs, one of which must be NULL!
In that construction you will have to do a JOIN when retrieving a town where you also want a list of items. This is the more elegant way, but probably not the best when you want to display a lot of tiles each time, a lot of times.

This post has been edited by Trogdor: 05 June 2008 - 04:04 AM

Was This Post Helpful? 0
  • +
  • -

#13 girlzlover12  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 13
  • Joined: 26-May 08

Re: Database Design

Posted 09 June 2008 - 09:21 PM

sorry for not responding sooner. I have been very busy working on the ads section of the website.



I am not sure if I understand what you are saying for the last part.

I think the best way to do item storage would be to store it like the x/y coordinates as another column and break it apart using explode. That way I can call all 3 columns in 1 query in my display loop.

Also it would limit garden/home tables to 60K rows.

This post has been edited by girlzlover12: 09 June 2008 - 09:32 PM

Was This Post Helpful? 0
  • +
  • -

#14 Trogdor  Icon User is offline

  • D.I.C Addict
  • member icon

Reputation: 15
  • View blog
  • Posts: 627
  • Joined: 06-October 06

Re: Database Design

Posted 10 June 2008 - 04:06 AM

If you post the database layout it is possible to understand what you are doing.
It sounds like an interesting project, but lack of actual details is limiting what i can do for you at this point.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1