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?
Thank you for your help!