Indie-Resource.com

Just out of curiosity. What is more efficient, using a bunch of smaller tables with only a few fields or a few smaller tables with lots of fields? Does it depend on the number of queries? Basically what are some tips on making an efficient game? What should be done and what should be avoided? Thanks.

More smaller tables is faster. If you think about it, no matter what kind of table you are querying, you have to have MySQL search the indexes of the table, so you may as well try to make it a smaller one so it has less to look through.

This is a massive and loaded question.

I personally have yet to kill a DB or even slow it down to the point of noticing a difference and I have DB's with 100,000+ entries and that is with very few indexes or query streamlining.

Indexes are important however.

Very true. While more, smaller tables are more efficient, you are unlikely to ever push the envelope with MySQL. It is blazingly fast, and a small-to-medium indie game is not likely to stress it out.

The most important question you can ask yourself about your game's database is whether or not every piece of data you may need is going to be easily accessible. That is much more important for most of our situations than the most efficient table size. Is every piece of related data tagged with foreign keys to keep the data accessible? Is repeated data minimized to prevent data anomalies? Are your tables organized to reduce empty fields?

What are 'data anomalies'? What do you mean by having 'data tagged with foreign keys'?

Sorry for so many questions, but I want to learn all that I can. Thanks for the input.

A "data anomaly" is when data becomes corrupted in your database. In plain English, it means the data has become disorganized or incorrect. As an example to see how this happens, think about a e-commerce website's database. Among several others, it has an ORDERS table and a CUSTOMERS table. Now say the person making the database thought it would be convenient to have the customer's address stored in both the CUSTOMERS table (because that is part of the customer's info) and in the ORDERS table (to ease the shipping department's data access). It sounds like a good plan at first. Now imagine that one of the customers calls in to customer service to alert the company that she would like the last order she made to be shipped to a different address. The rep says that is fine, and he updates her address in their system, which updates the CUSTOMERS table. Now when the order drops through to shipping, they will have a different address in ORDERS and in CUSTOMERS. Without notes in an account management system (which always seem to be missing when you need them most), the shipping department really can't ship the order to either one, because they don't know which one is right. The data is corrupted. And since there is corrupted data in the database, none of it can be trusted, and it must all be assumed to be corrupted. This is one of the most catastrophic disasters that can happen to a database, and in some cases, it can be worse than even losing the whole thing because almost every time the anomaly is not caught until months down the line, after the system has been working on the wrong data for months. This can be enough to sink basically any game, company or organization.

The above example is an example of an "update anomaly", but there are delete anomalies, insert anomalies, and several others.

A "foreign key" is the index of another table that links tables together. For example, say we have a Master_Item table in the database. This is so that we don't have to repeat the name of items, their stats, etc. for every instance of the item a player has. Beyond how much wasted database storage that is, it also leaves you open for update anomalies. Like all SQL database tables, you need to have a numeric index on your Master_Item table (1 - 1000+, etc., named something like "itemID"). You also have a table of players, who also have their own numeric index ("playerID").

Now to actually represent the inventories of various players, you have another table called "inventories". All you really need are maybe 4 columns in this table: 1. A numeric index for each inventory entry ("inventoryID"), 2. A foreign key linking the entry to an item in the Master_Item table (the "itemID" key listed above), 3. A foreign key linking the entry to a player in the Player table (the "playerID" key listed above), and 4. a column to represent the number of items, so you aren't needlessly making entries for high-stacking items such as arrows, bullets, etc.

You see how the foreign keys are used to reference entries in other tables? It is the cornerstone of relational databases such as MySQL. I hope all this made sense!

It makes perfect sense. It also gives me an idea of somethings not to do. Thanks for taking the time to write that It was very helpful.

Awesome, and you're most welcome!

Another data anomaly is something when you have a player linked to a character. So you have 2 tables, PLAYER and CHARACTER. If there`s an option to delete your player account, you can delete a record in PLAYER. However, if you don`t remove it from CHARACTER, your data is also corrupted. Because there`s an unused CHARACTER row in your database.

If you use InnoDB as your MySQL engine ( weird hehe ) you can link the 2 tables together. Since a character will have a player ID linked to it, if you want to remove the player from PLAYER table, you get an error because a reference still exists in CHARACTER. So you need to delete the CHARACTER row first, then delete the PLAYER row. This way, you can sanitize your database.

I`m not saying this is a good thing, i`m not particulary fond of InnoDB, however, this has more strict rules to follow. But truth be told, you can optimize your database as much as you want, without a good databasemodel or ERD or whatever you want to name it, even the best of databases will fail miserably. Structuring data should allways be a priority. Optimization is actually only necessary if performance is dropping. Meaning, structure your database in a good way, if this however stresses your database too much, only then should you look at performance. Most database are a lot smarter then you`ll ever be in storing data. Don`t try to outsmart it.

Yep, that is a DELETE anomaly!