Indie-Resource.com

jpoisson wrote:I guess you don't have to do the transfer of items from on table to another but it is a lot more flexible then by just adding a new field.

Adding a new field would also likely increase the size of the db needlessly compared to putting them in a new table. If the original tables are well organized, then an inventory or bank table would likely just be a simple one-to-many table containing two numbers: the player ID and the item ID. Since this table would dynamically grow and shrink as needed, it would not add the bulk of extra fields on the main table that will likely mostly be empty for many players.

@Jackolantern - I couldn't put it better myself. Indeed it is much easier to maintain a one-to-many relationship compared to a many-to-many relationship.

while it is a great idea to have a field that states whether it is in a bank or storage it may not be the best route for size reasons. Players often load up their storage and their banks. Imagine having 500 players. Each player has 10 items in their bank and 10 items in their storage. That's 10,000 items! Would you rather have 1 table holding all this or 2?

So Halls, we should move the equipped items to a separate table then too? Each character can have up to 10 equipped items in my game.

10 items is different than 10 bank and 10 storage.

Having them in separate tables is probably the best bet. MySQL honestly doesn't care how many tables you have. After about 10,000, your OS will begin to freak out regardless of it being Linux, Windows or OSX, but MySQL could care less. It is better to have more smaller, completely filled out tables than to have less tables that are longer with many empty fields. Longer tables with more fields will slow down queries whereas having many tables makes no difference to performance at all until the OS begins to choke (which again, is a huge number of tables that is likely too much for one or two developers to keep up with).

I may have to start rethinking my tables then. The longest table I have is 20 fields and none are empty. Everything else is 15 or less. Before I went to work I went ahead and separated all of my toggles into new tables and It definitely seems to speed things up a bit. I still need to redo my auction house though.

20 fields isn't that bad, really. When people in the MySQL community talk about having too many fields, they are usually talking about business-oriented applications that may have upwards of 200 or 300 fields on one monolithic table.

I've don't see the need in a game like this to have that many fields. Right now my users has 20 fields and some of them are placeholders for systems that I haven't put in yet lol

The hard coded limit is 4096 fields per table. This number though depends on the data size of each row. You cannot exceed 64k.