Jump to content
  • Advertisement
Sign in to follow this  
Wixner

(MM)O(RPG) database design

This topic is 2635 days old which is more than the 365 day threshold we allow for new replies. Please post a new topic.

If you intended to correct an error in the post then please contact us.

Recommended Posts

For the last couple of months I've designed and implemented my own little MMO-network library called wixnet and I am ready to test it in my (MM)O(RPG) game prototype and I need some input on the underlying database design.

To spare you the details of the design of my network design, I've decided to use the following types of databases:

The Account Database
This is a database dedicated to User Accounts.
Only one database per region - UK, US, EU.
Contains the following information:

  • Username // self-explainatory
  • Password // self-explainatory
  • Account ID // used to connect this account to characters on the game servers
  • Connections // how many simultaneously connections are allowed on this account. Perhaps the game allows for multiple sessions (on different game servers though)
  • Subscription Expiration // unless the game is free to play
  • First Name // self-explainatory
  • Last Name // self-explainatory
  • E-Mail Address // self-explainatory
  • Country // used to locate the game servers closest to the users
  • Gender // self-explainatory
  • Date of Birth // self-explainatory. Could be used to give the player some nice in-game birthday presents
    The Character Database

    This is a database used to store characters, or avatars, or toons, or whatever you call them.
    One database per game server.
    Contains the following information:

    • Account ID // connects this character to the player account
    • Character ID // used by the game server to identify this character
    • "General information" // name, title, guild-membership, gold
    • "Appearance flags" // ID for: character model, hair style, tattoos, facial hair, like: "Character: 6" (where 6 is the ID for the "Undead Centaur")
    • "Equipment flags" // ID for: each item in every available inventory- and equipment-slot, like: "Left Ring: 15" (where 15 is the ID for "Brewgor's Ring of Low Latency") or "Inventory[12]: 55" (where 55 is the ID for "Major Potion of Bandwith Reducer" and is located in the inventory slot number 12)
    • "Attribute flags" // strength, dexterity, mana, power, luck
      These are the databases that I think seems right, and I'm working on the Item Database and the Game Database at the moment and I will post those as soon as I'm confident that they're good as well.
      What is your opinion? Does this seem like a good implementation to you? any ideas? suggestions?

Share this post


Link to post
Share on other sites
Advertisement
This is not a database design.

This is not even a requirements specification.

It is most certainly not an "implementation."


You have no details or even any vague description of the formats of your "flags" and "IDs." They could be anything. There isn't even enough information here to offer any real feedback, unfortunately. All I can glean from your post is that you have characters, stats, and items. Well, yeah, it's an RPG; those are kind of a given...

Share this post


Link to post
Share on other sites
I never said that the database was implemented, it's just "pseduo-design-overview"

Perhaps I thought it was too obvious, but here's the basic idea:


  • "ID" is an INTEGER referring to a primary key in another database
  • "Flags" are just a collection of approperiate datatypes (like TEXT for string-based information such as names, and REAL and INTEGER for character attributes)

    And yes, these are the very basics of just any RPG ever, but is there anythin I'm overseeing? Am I to wide or to narrow in my thinking?

Share this post


Link to post
Share on other sites
Vastly too wide. You're not even really listing any useful details; yes, obviously, you need to track players, and stats, and whatever else. That by itself is not interesting at all.

What is important is how you plan on tracking all that information, and you've given only vague hints as to your intentions there. What exactly do you expect us to say?

Share this post


Link to post
Share on other sites

The Account Database

The Character Database



Sounds like table schemas, rather than databases, to me.

How will you query those schemas? When will you query them? What are the performance requirements? How many reads per second? How many writes per second? What are the data integrity guarantees?

Share this post


Link to post
Share on other sites
In my honest opinion, I think you should scrap your current idea.

Your schema's look very similar to an relational SQL database. What relations do your account have with each other? None? At most they have a one to many relation with the characters. which is way overkill for pulling in a complete relational SQL DB.

I would say a key value store for your accounts and characters would be a solution you might would want investigate.

I think an SQL database would be worth considering to provide things such as auction house functionally, when you really might need more advanced search queries.

Share this post


Link to post
Share on other sites

And yes, these are the very basics of just any RPG ever, but is there anythin I'm overseeing? Am I to wide or to narrow in my thinking?



Let me be a little more clear about what I was hinting at before:

I think what you should do is figure out what I, as a user of your system, would actually want, before you figure out what the system looks like. I think starting to design "a system" in a vacuum of requirements is putting the cart before the horse.

Am I a third party developer? If so, how do I connect to your system? How do you make sure that my users can't edit data that they shouldn't be able to edit?

Or am "I" really just yourself, as a game developer? If so, what information do you need to store about your customers? About your customers interaction with the game? About the game itself?

For example, it's often useful to separate "customer" from "avatar" -- I may want to have multiple avatars for a particular customer. However, modern games (such as Wizard 101) realize that there are possibly many "customers" for one "payer" -- a family may play a game, but only Mom pays the credit card.

Additionally, you may have multiple server instances -- do "avatars" (characters) travel between instances, or are they stuck to a particular instance?

How does payment work? Is it free to play? Do I pay per zone? Pay per month? Pay once and get to play forever? Do you think you will provide all three options at some point? If not, how do you know the option you pick is the right one? How do you know why a particular "payment expires" field has the value it has? What if someone calls in and complains that their expiry is wrong?

Where do customers come from? Banner ads? Word of mouth? Radio advertising? Magazine inserts? Store check-outs? Do you need to know, for each customer, to do a better job of selling your game? Do you need to know which customers stick together so you can market their relation -- or mine the relation to find scammers, fraudsters and griefers?

If I have characters, can they be shared with other users on the same payer account? At the same time or separate times? Do users get tagged with specific attributes, like "over 18" or "over 13" or whatever? Or does that live with "characters"? Do you want to keep logging information about how a characters is used, by which user, at which time, so you can later go back and look it up?

I have no idea of knowing whether these are use cases that matter to you or not, so I have no idea whether your table schema will serve a purpose or not. I think it would be much better if you asked a question something like:
"here are my use cases, and here is the schema that realizes these use cases, and I think that N database server instances will serve M users with an assumed query rate of Q per hour -- is that about right?"
Or maybe what you're REALLY asking about is "what are the use cases I forgot about," which is a totally different question.

Share this post


Link to post
Share on other sites
Make sure look into the subject of 'transactions' and continuous data archiving/backup issues.

You show player and char data but likely you will also likely be using the DB for char related
data that has alot of dynamic sub data (not just fixed monolithic records), hence the
transaction processing to keep 'saved' data sets cohesive. The same goes for world game state.

There is nothing worse in a game than having several hours/a days game progress lost
when a server goes down and restarts back at a clumsy once-a-day backup image.

Many of the 'toy' Databases (ie- mysql) dont/havent offer good solutions for continuous backup/archiving
(or even transactions) and it may be up to you to add such feature supoort short of paying $$$ for an
enterprise level DB.

Share this post


Link to post
Share on other sites
Hello Wixner

I see you speak about Account Database and Character Database, obviously you just show schemas, not a real database definition, you still don't know how to do that, and here many people is showing you which data your game should include, that's good, you can take decissions based in that but what will give you a real idea about what you should include is to write actually the code, let me explain you that, you can't just write a "generic" database for every single mmorpg because every game is diferent, not only player characters, but NPCs, spells, items, maps, monsters...

Where is all that data defined in your database?

I'm a programmer and I've finished many projects using DBs, using Interbase, MS SQL server and MYSQL, using various programming languages: Pascal, c++, php, visual.net and the best advice is to stop thinking in an abstract way!, think in real things: the objects in the game , How will you handle them? , how will you store them?, then you will realize that you need a DB Table with X fields, the same for monsters, characters and everything else...

There are NOT GENERIC DATABASES, not for an MMORPG, not for a Game , not for any program..

I've engaged in a project to build (oh, yes!) my own MMORPG and I have already writen a draft for the DB, but i know it will change in the time I bring the game from "smoke" to "alpha" when I will start to actually test it and I will receive feedback from the testers...

BTW, mysql is not a "toy" database Wodinoneeye, a toy database can't handle millions of registry entries and GBs of info:

http://en.wikipedia.org/wiki/Comparison_of_relational_database_management_systems


...a "toy" database is something like MS Access, with it's wonderfull performance and annoying database corruption every X months.. yeah love it pal... ;)

Hope this helps

Share this post


Link to post
Share on other sites
"mysql is not a "toy" database"

It is if you've ever tried to run an actual global network that someone actually based on it. Then it's "interesting" approach to replication, distribution and failover starts to make it look a teensy bit plasticy-primary-coloured-made-in-chinaery.

Share this post


Link to post
Share on other sites
Sign in to follow this  

  • Advertisement
×

Important Information

By using GameDev.net, you agree to our community Guidelines, Terms of Use, and Privacy Policy.

We are the game development community.

Whether you are an indie, hobbyist, AAA developer, or just trying to learn, GameDev.net is the place for you to learn, share, and connect with the games industry. Learn more About Us or sign up!

Sign me up!