• Create Account

## A few painful questions about Databases

Old topic!

Guest, the last post of this topic is over 60 days old and at this point you may not reply in this topic. If you wish to continue this conversation start a new topic.

9 replies to this topic

### #1Nymall  Members

Posted 25 May 2013 - 12:08 AM

Using: MySQL, PHP, JQuerry

Well, I have a few questions about database organization and I'm hoping you guys can help me out. I'm not new to programming - I'm mostly self-taught, and I've been doing it on and off since I was in my early teens. However, I've been finding information about organizing things very hard to track down...

I guess I should give an example. Right now I'm working on the framework for a web game for my wife. It's real simple, image mapping, point and click stuff, but I'm having some difficulty figuring out how to plan ahead just in case this game is live in 5~10 years(as unlikely as it is). At the moment the inventory is split into seven different tables, with a total of 600 columns combined for all of them.The farther I'm getting into this project, the more I'm wondering if each character should have their own table, and rows for each of the items they have, or if I should proceed as I have done.

So, in summary - ​Is it more efficient to have a table with 600+ columns and 100,000+ rows, or 100,000+ tables with a average of 100 rows each? How much weight does a empty entry add to the database? Is there a easy way to future proof the minimalist number of table concept without it eventually getting completely unmanageable?

Now for problem number 2: I've got a bit of explaining first... Right now, when a user clicks a link, the game pulls the location the player is currently in and the number associated with the hotspot they clicked, and checks it against a table of valid destinations for that room. If there is a valid destination, it's returned to the script and the game makes another check to the database for the information for that room, which is then brought back to the script. From there, it enters a "flag section" which filters if the player can continue.

1)If it returns true, the scene draws as normal.

2)If it returns false, it checks the database for the new possible destination, and then renders that location as if it was the location initially returned.

The problem - This can at times mean that the database can be queried a total of five times for a simple move action.

Notes: There are some locations that are designed to have multiple tests running at the same time, by order, so it would add a lot of bulk to add the test information right into the scene table.

Is there any general advice you can give to shorten up this insanse number of database calls? Is there a faster way to script this in MySQL? Would the performance loss be fine for a small/medium sized game?

My favorite error yet:

Parse error: Syntax error, unexpected '\$pants'...

### #2Herwin P  Members

Posted 25 May 2013 - 02:16 AM

I'm not an expert in database management, but to me that number is too high for a database column or tables.

Have you ever heard about Dabatase Normalization? Simply, it's a concept of separating columns into tables and make relations to it. It's useful to avoid redundancy and dependency. It also will free your databases from some anomalies (deletion, update and insertion anomalies).

A character having a table just for its items is completely unnecessary. What if there's a new character? Making the same table (with only different names) over and over again is obviously redundant.

I'm wondering how could your database have that many tables and column. Can you please export your database and upload it here? That way I can analyze it and, if possible, make it more simple and effective. Trying to tweak something I can't see is hard.

The row number is fine if the game is really big, but not for column and tables.

Edited by Sky Warden, 25 May 2013 - 04:15 AM.

### #3Steve_Segreto  Members

Posted 25 May 2013 - 03:41 AM

​Is it more efficient to have a table with 600+ columns and 100,000+ rows, or 100,000+ tables with a average of 100 rows each? How much weight does a empty entry add to the database? Is there a easy way to future proof the minimalist number of table concept without it eventually getting completely unmanageable?

One large table is likely to be more efficient than 100,000 small tables simply because MySQL is a relational database and there could be significant overhead in chasing down primary and foreign keys for each access to a different table.

Empty entries should not add much weight to the database, but you should really wonder why you need them, are you simply trying to keep the dimensions of two tables similar in some way?

MySQL most likely uses some sort of logarithmic search algorithm to find entries (i.e. a binary search).

This search is extremely fast even for HUGE numbers. For instance a computer can find one name in a phonebook with 1,000,000 names in just 20 comparisons. So you shouldn't worry too much about the number of search queries you need to make. But if you insist on worrying, see if it's possible for you to batch them all together and make only one large query call through the API to reach MySQL and ask it to batch search many rows at once, because the time to issue the call and connect to MySQL might be slower than the time to search.

It's nice you are making a game for your wife. I agree with Sky Warden, you should share your usage of this database with us, because it sounds like possible overkill if you have 100,000 distinct tables.

Edited by Steve_Segreto, 25 May 2013 - 03:55 AM.

Posted 25 May 2013 - 04:29 AM

One large table is likely to be more efficient than 100,000 small tables simply because MySQL is a relational database and there could be significant overhead in chasing down primary and foreign keys for each access to a different table

I think this can't be generalised so easy. tbh, 100,000 tables is a ridiculous number, but so is 1. Once your business logic becomes slightly complex you will run into various performance issues related to write locking, cache utilisation, index updates, etc.

"because MySQL is a relational database and there could be significant overhead in chasing down primary and foreign keys for each access to a different table".

This can be viewed from a different angle: Relational databases are highly optimised in dealing with relations. If your data can be modeled in a relational manner you should go for it. As Sky Warden suggested, OP should look up data normalisation.

Edit: The fastest database query is the one that doesn't happen. If you know you are going to read a lot of data in the future, you can preload it into an easier accessible storage. You could, for example, load the current room data the user is in into memory and check from there instead of constantly checking the database.

Edited by Madhed, 25 May 2013 - 04:35 AM.

### #5rip-off  Moderators

Posted 25 May 2013 - 05:07 AM

To re-iterate, you need to normalise your data. Generally speaking, databases have lots of rows in a relatively small number of tables with a small number of well defined columns.

... I'm having some difficulty figuring out how to plan ahead just in case this game is live in 5~10 years(as unlikely as it is).

If your data is well normalised it usually isn't incredibly difficult to migrate the schema should the need arise. Speculating too far ahead can cause all sorts of problems, not least finding out that what you need is not what you planned for.

### #6Herwin P  Members

Posted 25 May 2013 - 05:16 AM

POPULAR

because it sounds like possible overkill if you have 100,000 distinct tables.

It will. Trust me. Even in my multiplayer RPG game database design, I just need about 43 tables to cover most of the things like items, skills, magics, users, characters, quests, etc.

I think, instead of using a table for each character to contains its items, you can do something like this:

character_table : id, name, other, information, needed
item_table : id, item_name, type, price, weight, other, information, needed


And make a pivot table like this:

character_items : id, character_id, item_id, quantity, slot_number


The character_id and item_id attributes in the pivot table refer to the id of character_table and item_table.

The tuples will be like this:

id, character_id, item_id, quantity, slot_number
01, 142         , 153    , 30      , 1
02, 143         , 153    , 20      , 5
03, 142         , 192    , 14      , 2
04, 142         , 112    , 11      , 3


See? You don't need one table for each character.

Oh no! I want to delete character 142! Simple. MySQL, just delete any tuple in character_item table where the character_id is 142.

Oh no! I want to delete item 153! Simple. Just do like the previous.

If you need to use the item information for something else, like what items are in a room, you can simply make a pivot table to connect the room table with the item table. This makes your data re-usable, and, magically, reduce redundancy.

You don't need a table with 1000000 columns either. Remember that those attributes can be separated into tables. Just use your imagination.

As for empty data in a row, try to keep it minimum. It just doesn't feel right to me.

Remember to make the id attribute a numerical auto_increment-ed column and set it as primary_key. It will make the search faster. It's very good for consistency.

My Little SQL : Normalization is Magic.

Edited by Sky Warden, 25 May 2013 - 05:17 AM.

### #7Aurioch  Members

Posted 25 May 2013 - 07:27 AM

I'd just like to expand a little on Sky Warden's post.

Having 1 table with 600+ columns will just lead to severe performance degradation sooner or later.

Imagine you have a table with attributes (columns) like this:

char_id, char_name, char_class, char_level, char_spell1, char_spell2, char_spell3, char_spell4, armor_id, armor_name, armor_value...


Now, imagine there are 365 out of 10 000 different chars which carry the same armor.

You have it? Ok, now change the value specific armor provides, for example from 3 to 5. See the problem?

Another problem with table with huge amount of columns is that you need to fill. Every. Single. Column. Each. Time. You add a new row. This isn't noticeable if each row is different than others, but for example if you're making a monster list where name and stats are same but equipment varies, table will have insane redundancy problems. Try edit anything in a table like that without screwing whole table.

Is there a easy way to future proof the minimalist number of table concept without it eventually getting completely unmanageable?

Take your time to model your database. Normalization is great tool to achieve efficient database without redundancy.

Edited by Aurioch, 25 May 2013 - 10:32 AM.

Posted 25 May 2013 - 11:42 AM

I agree with the others.  For example here is one way to normalize it:

character

========

id

name

attribute

======

id

name

character_attribute

===============

id

attribute_id

attribute_value

unique index main_ind (id, attribute_id)

Your character_attribute table will now have 500 rows per character.    This is much more scalable and still easy to work with.

edit:  Just to populate this a bit to further illustrate:

character table

===========

id , name

1 , Conan

2 , Gandalf

character_attribute  table

===================

id , name

1 , class

2 , level

3 , strength

4 , spellpower

character_attribute

==============

id , attribute_id,  attribute_value

1 , 1 , Warrior

1 , 2 , 15

1 , 2 , 18

1 , 2 , 0

2 , 1 , Wizard

2 , 2, 20

2 , 3 , 5

2 , 4 , 19

Edited by starbasecitadel, 25 May 2013 - 12:47 PM.

### #9Aurioch  Members

Posted 25 May 2013 - 01:31 PM

Now for problem number 2: I've got a bit of explaining first... Right now, when a user clicks a link, the game pulls the location the player is currently in and the number associated with the hotspot they clicked, and checks it against a table of valid destinations for that room. If there is a valid destination, it's returned to the script and the game makes another check to the database for the information for that room, which is then brought back to the script. From there, it enters a "flag section" which filters if the player can continue.

1)If it returns true, the scene draws as normal.

2)If it returns false, it checks the database for the new possible destination, and then renders that location as if it was the location initially returned.

The problem - This can at times mean that the database can be queried a total of five times for a simple move action.

Notes: There are some locations that are designed to have multiple tests running at the same time, by order, so it would add a lot of bulk to add the test information right into the scene table.

Hmm... better designed and normalized database would really help here.

I do not know how exactly have you structured your database, but I'd most probably do something like this:

• Table called RoomInfo which holds relevant information about each room.
• Table called RoomLinks with attributes (colums) called CurrentRoom_ID, DestinationRoom1_ID, DestinationRoom2_ID etc.
• When link is clicked, have the database return row from RoomLinks (based from current location) parallely joined with RoomInfo based on each ID. This removes the need of calling it once more because you can both check for valid destinations and immediately grab required data.

It's always faster to have separate tables (like rooms, hotspots, destinations) and just join them when called to get all the data you need in one call than to have one table which you need to call multiple times. There are various ways to join tables, depending on your needs.

Also note that this solution may not be perfect - after all, I'm just studying databases, maybe someone else can provide better insight.

### #10LorenzoGatti  Members

Posted 26 May 2013 - 01:53 PM

Now for problem number 2: I've got a bit of explaining first... Right now, when a user clicks a link, the game pulls the location the player is currently in and the number associated with the hotspot they clicked, and checks it against a table of valid destinations for that room. If there is a valid destination, it's returned to the script and the game makes another check to the database for the information for that room, which is then brought back to the script. From there, it enters a "flag section" which filters if the player can continue.

1)If it returns true, the scene draws as normal.

2)If it returns false, it checks the database for the new possible destination, and then renders that location as if it was the location initially returned.

The problem - This can at times mean that the database can be queried a total of five times for a simple move action.

Notes: There are some locations that are designed to have multiple tests running at the same time, by order, so it would add a lot of bulk to add the test information right into the scene table.

Is there any general advice you can give to shorten up this insanse number of database calls? Is there a faster way to script this in MySQL? Would the performance loss be fine for a small/medium sized game?

Please explain what sort of additional tests you need to run when the user tries to follow links. Basically, this is another many-to-many relationship, from rooms to rooms, with link-related data in the pivot table (e.g. room A is linked to room B through rectangular hotspot from x1,y1 to x2,y2 with caption S). You can get s list of a room's outgoing links very cheaply, then you only need to load room data for the entered room: what performance issues are you worrying about?

Omae Wa Mou Shindeiru

Old topic!

Guest, the last post of this topic is over 60 days old and at this point you may not reply in this topic. If you wish to continue this conversation start a new topic.