Help/tips setting up MySQL db

[crzyone9584]

(Originally posted in Recruitment, but became a solid resource for beginning PBBG database designing. Initial recruitment post left for reference only. -Jackolantern)

I'm looking for someone on their free time to develop a database to use in my ORPG found here. I really don't know much about databases and how to set them up. I would like the database to hold all information about the user, about in game information like items, npcs, maps, what rank is a user (such as admin, mapper etc etc), the players inventory. I like how netgore is set up via database but i hate how it requires 'SUPER' to be enabled. My host won't allow 'SUPER' to be enabled. So if 'SUPER' needs to be enabled then I'll just re install MySQL on my VPS. If anyone has the free time and knows how to set up Databases please let me know.

Eventually I would like to integrate my game MySQL with my forums which are MyBB, eventually will be getting vBulletin. Such as use the users login for the forums to log into the game. If thats even possible please let me know.

[Xaleph]

Everything is possible, but setting up a database really isn`t that hard. If you have a good database design ( I recommend using tools like MySQL workbench) it`s even easier. If you have a clear picture on how it should look, it won`t have to take more then 2 hours. Really, it`s that easy.

Another thing: what do you mean by SUPER? That word most often is used as a reference to a parent object.

[crzyone9584]

By 'SUPER' i mean http://dev.mysql.com/doc/refman/5.0/en/ ... priv_super

As for the design, I think i know how i want it. Just not sure if it would work.

[Xaleph]

SUPER command is a very, very dangerous command, it gives full access to an account ( and your entire MySQL server) which in itself does not necessarily have to be damaging, but using a root account for a connection can be devestating. It`s normal for it not to be active, really, it is. It gives privileges you don`t need. Why would you need it anyway?

Anyway, if you know what you want, download mysql workbench, it should be easy enough. You can forward engineer it to an existing database or just get the SQL, which in turn can be used on any database that supports SQL.

All a database does is create, read, update and delete entries, that`s all you need a database for. You don`t need to create dynamic table ( i hope ) and even that is possible with simple user privileges. Anyway, if you have a design, post it here, the rest will look at it, me too.

[crzyone9584]

I really don't want super. Thats the one thing i hate about netgore, it requires super... Thanks. I'll download the workbench and see what I can do.

[Jackolantern]

Everything is possible, but setting up a database really isn`t that hard. If you have a good database design ( I recommend using tools like MySQL workbench) it`s even easier. If you have a clear picture on how it should look, it won`t have to take more then 2 hours. Really, it`s that easy.

Another thing: what do you mean by SUPER? That word most often is used as a reference to a parent object.

Well, I do have to kind of back crzyone a bit here and say that making a database is easy, but designing a good one can be a bit harder. If you are not able to think about all the ways you will need to reference your data, you may inadvertently make make certain queries inaccessible, costing you lost data, anomalies, corruptions, or forcing you to throw the old design away and start completely over again.

Granted, it doesn't take that long to get a good handle on designing solid, extensible, scalable, high-performance databases, but it is not trivial. It is much easier than learning how to program from scratch, but I would say a lot harder than learning something like HTML.

[crzyone9584]

Okay here is just the small design I have. If you guys think it will work maybe someone might be able to help me create it.

account - holds user id, user name, password, password hash, email, registration ip, last login ip, date registered, and last login date

accountban - hold user id, name, registration ip, last login ip

characters - all accounts will be able to have 3 characters. This table will hold the user id for the character, character id, its name, lvl, what sprite its using, equipped items (weapons, armor, etc), location in the game, what "server" its one. I simulate the idea of being able to go to multiple servers.

inventory - it will hold the different types of item for each character, It will be broken up to key items, weapons/armor, potions/scrolls/everything else. This table should be attached to the character id.

item - This will hold information about all the items in the game. It will have a unique id for the item, a description, item type (armor, weapon etc),

spells - Same as items but for spells

map - it will hold the XML data to send to the client to add new maps to the clients.

That's all i really need for now. I would like to have routines to call from my source so its a little harder to do a sql injection that will handle adding information into the database..

Let me know if this a decent/good starting design. If not what would you guys recommend?

[Jackolantern]

You have too much info stacked on the character, inventory, item, and spells table, although most of those you would see as soon as you started adding data to them. For example, it would not work out well to hold both weapons and armor in the same table, because each uses at least some different stats (weapons cannot typically offer defense, nor can armor typically attack, at least). Adding them to the same table would guarantee empty column values for every item stored, which is bad practice. You can say the same thing about items. For example, what about healing potions? Although they comprise a very small portion of your total items, if they were in the same table with all other items, every single entry would have to have an "Amount to heal" or "Heal value" column, and probably 95%+ of the items would have that column empty. The same thing likely goes for the spell table as well.

The character table is a bit different. It is one of the most sensitive tables in your entire game, because corrupted data means you are erasing a player's entire life in the game, so much care has to be given to it. For that reason, it is usually best to break pretty much every category of character data down to its own table to ensure you can add or alter data down the line and still be able to retrieve everything you need. Give yourself tons of room for growth, particularly for ever-changing mediums such as MMORPGs. The types of joins you will be coding to retrieve data from these types of database designs are really nothing at all to MySQL since they will be simple one-to-one joins that require no virtual tables. So if you are ever not sure, make it a separate, indexed and foreign-keyed table!

[Xaleph]

Yeah too much data inside tables, consider splitting it up. Create a table for weapons, armor, head gear et cetera. Also seperate IP`s from the user table. An IP is not something of the user, it`s used for logging purposes. So create a log table where you store IP`s, auth stuff in general.

Don`t store 2 passwords, 1 normal and 1 hash, the whole point of hashing is to not store the original password!

Seperate locations from the character. Make sure you have a seperate locations table. Every zone/map has an ID, and a character will have a progress table, in that table you can store the location ID, possibly even x and y coords.

Items are different and i`ll bet there will be people who not agree with me here but:
Every item should be an entity. So if you have a weaponsshop and you sell weapons, every time a weapon get`s sold, a new entity should be created. That way market values and economics can work best. So every item should be an entity on it`s own. So weapon drops should also be entities, including ultra-rare drops. That way economics can do the magic of supply and demand. So when designing a database take that in to account.

Spells looks fair enough, however, designing it like this means it`s all flat. All spells can be learned from start, whereas normally you would want your players to meet some requirements either by level or by following a talent tree. Maybe consider that. Also, as for spells, I would create entities of them as well. Or at least a coupling table to bind spells to users. That way it`s easier to track who learned which spells.

[hallsofvallhalla]

I agree with entities.

Have a table called weapons,it would be the stats and description. Then have a table called pweapons(player weapons) that would be an instance of the weapon for the players.

SO lets say both carl and fred buy a short sword.

in pweapons 2 swords will be created using the data from weapons. a ID field would signify which sword is who's. Now lets say fred pas $1.99 to have one of his weapons enchanted for 30 days. It changes the weapon then in the pweapon table. At the end of 30 days it reverts back to normal by grabbing the stats from weapons.

Now lets say you want to run a special on all short swords for 10 days that makes them have 3 attack instead of 2, all you have to do is change it in weapons and nothing changes in pweapons, so people who already bought one doesnt change.

I also agree with breaking out each type of item. Weapons, armor, ect... less fields for different items. Like you don't need a protection field in weapons but clumped together armor will need it so weapon will get it anyways.