(MM)O(RPG) database design

Recommended Posts

Wixner    163
For the last couple of months I've designed and implemented my own little MMO-network library called [i]wixnet[/i] 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:

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

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

[list][*]Account ID [b]// connects this character to the player account[/b][*]Character ID [b] // used by the game server to identify this character[/b][*]"General information" [b] // name, title, guild-membership, gold[/b][*]"Appearance flags" [b] // ID for: character model, hair style, tattoos, facial hair, like: "Character: 6" (where 6 is the ID for the "Undead Centaur")[/b][*]"Equipment flags" [b] // 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)[/b][*]"Attribute flags" [b] // strength, dexterity, mana, power, luck[/b][/list]
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?
[b]
[/b]

Share on other sites
ApochPiQ    23064
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 on other sites
Wixner    163
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:

[list][*]"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)[/list]

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 on other sites
ApochPiQ    23064
[i]Vastly[/i] 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 [i]how[/i] 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 on other sites
hplus0603    11356
[quote name='Wixner' timestamp='1313603087' post='4850404']
[b]The Account Database[/b]
[b]
The Character Database[/b]
[/quote]

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 on other sites
flodihn    281
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 on other sites
hplus0603    11356
[quote name='Wixner' timestamp='1313614667' post='4850467']
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?
[/quote]

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 on other sites
wodinoneeye    1689
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 trevanian 119 Hello Wixner I see you speak about Account Database and Character Database, [i]obviously you just show schemas[/i], 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: [b] [url="user/72312-wodinoneeye/"]http://en.wikipedia.org/wiki/Comparison_of_relational_database_management_systems [/url][/b] ...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 Katie 2244 "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 trevanian 119 [i]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.[/i] Hello I know what you can do (and the cost) with both MS-SQL server and with ORACLE, because I'have seen both working: -MS-SQL Server: Buy two 2008 server licenses (+hardware), buy the SQL server with clustering services... -Oracle:Buy a SUN Ultra-Sparc server + Solaris an then pay the license for Oracle... Both sollutions are strong, granite solid Katie, but know that? Our friend is STARTING to design a MMORPG he/she is not a big team so I don't think he/she is looking for a sollution that will cost THOUSANDS of dollars /euros, probably just want a database that does the work, and does it in a [i]cheap[/i] way.. I am making my own MMORPG and I won't use the "big" SQL databases... Share this post Link to post Share on other sites Antheus 2409 [quote name='Trevanian' timestamp='1314722247' post='4855516'] Both sollutions are strong, granite solid Katie, but know that? Our friend is STARTING to design a MMORPG he/she is not a big team so I don't think he/she is looking for a sollution that will cost THOUSANDS of dollars /euros, probably just want a database that does the work, and does it in a [i]cheap[/i] way..[/quote] The most expensive gift is a free puppy. [quote]It is if you've ever tried to run an actual global network that someone actually based on it.[/quote] I hear that some guys use it to run an online book of sorts. Apparently, containing faces. It just goes to point out that design, scalability, maintainability and everything else isn't really related to technology used. [quote]a "toy" database is something like MS Access,[/quote] I generally see two types of people. Those using best-of-breed multi-shard SQL/NoSQL replicated failover normalized thingamajig state-of-the-art engineering. They can usually be found eating ramen on the doorsteps. And then I see businessfolk who are driven to work in cars padded in ivory whose entire technical real-estate consists of one single Access database, evolved over past 15 years. They don't really care, they can afford to pay to keep it running with the spare 1000 bills lying around. When a company or project failed, nobody ever said: "If only we used a real database, not that toy Access". it's simply not something that matters upfront. But please, please, prove me wrong. So that once I will have a written proof that technology can make or break a business/venture/project. Share this post Link to post Share on other sites hplus0603 11356 [quote name='Katie' timestamp='1314697669' post='4855379'] "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. [/quote] If you try to replace ORACLE with MySQL, you will fail. If you built your application assuming all the features of ORACLE (or DB/2, or to some extent SQL Server), then MySQL is not a suitable replacement. Then again, those features often cause problems as well. Ever had all ORACLE queries hang for a very long time in your London office because some server in Florida dropped its network connection? If you build your application using application-side sharding, and only use replication for hot stand-bys, then MySQL can drive some pretty big applications. Like, say, Facebook :-) Share this post Link to post Share on other sites trevanian 119 I think I've been greatly misunderstood, maybe it is just my natural language It's not English... I'm not telling that Oracle or MS SQL Server are bad products, absolutely! but the truth is that a novice user needs help, so ok tell that the better and more expensive database is the best suite for an starting project... come on boys! Am I telling lies if I assume that NOBODY here is a member of a company like Blizzard, EA,etc??, a good advice is use something you can afford, use standards, write a dll to access the database and later if your project turns into a success and it really gives you good earnings, buy the BEST DB, the BEST Clustered hardware, buy whatever you need to make it ever better... JB Share this post Link to post Share on other sites hplus0603 11356 [quote name='Trevanian' timestamp='1314737154' post='4855620'] Am I telling lies if I assume that NOBODY here is a member of a company like Blizzard, EA,etc? [/quote] We have several contributors on this board who are employed in various networked games businesses, small and large. Additionally, many other contributors read up on available research, which includes things like GDC presentations on the technologies behind various successful (and not so successful) games, and use that as the basis of their recommendations. Share this post Link to post Share on other sites wodinoneeye 1689 Note, I did say : " it may be up to you to add such feature support short of paying$$ for an enterprise level DB"

Certain features had been missing or 'lite' in MYSQL and as I said you may have to program around
the limitations. Replicated failover DBs themselves have problems with performance/cost and you still have
to/prefer to make the Application carryout the recovery almost seamlessly.

Regular disk/component failure and power outages are going to happen (so hardware solutions will be part of the
whole strategy of course)

Archiving solutions for really catastrophic events should be considered (project should survive the building with the
power backups/servers/backup servers/spare disks all going up in flames or smashed by a rabid hoodlums/disgruntled employee).
Weekly offsite archives for that.. Splitting images off a hotswap RAID still should have some kind of App checkpointing.

Share on other sites
trevanian    119
I lied then ;)

There are many ways to achieve data redundancy, for me the best from all them is hardware RAIDs, SCSI, SAS, FDI all them are good, if your project becomes real big you need a cluster (cloud) of computers of course, then you have many ways to achieve this, with a big amount of cheap computers in a farm, with a super computer like an HP 9000, SUN Fire 15k and up with many processors and plenty of GBs RAM, but I Agree the best protection against real disasters is Tape backups , altought in my opinion NAS solution to make backup is an emergin way to provide backups, I have seen that in a few companyes , but it does not solve the "fire in the datadenter" problem....

Now there are two kinds of disasters that can strike in a project.

-Physical failure:If a disk fail, and you've got a raid 1 /5/6/10/50 whatever then no problem, same for PSUs, if you've got a cluster and a computer turns off ok no problem..
-Logical failure: those are the real BAD ones, what if a virus get into all your computers? what if you made an update that did what it was not suposed to do? what if you used a SQL DELETE FROM without the WHERE statement? you can f**k your database real good, and the worse is that you will f**k the database in all your RAID disks, your cluster nodes and basically anything redundant...

Ok, if MYSQL does not provide an automated way to make the backup you will loose data from the time there was the last backup, know what? I have been in many, many companies repairing servers, and nobody made more than a backup every day (at night), even goverment with a SUN FIRE 15K with 80 64-Bit Ultra sparc processors and a Hitachi cabin for data Storage.... sad but true...

Before anybody blames me, want to make a backup every our? every minute? it is ok!you wont loss date at all but performance will drop when doing it, that's why companyes make backups at night when nobody is working on the computers..

JB

Share on other sites
Antheus    2409
[quote name='Trevanian' timestamp='1314784098' post='4855809']
Now there are two kinds of disasters that can strike in a project.[/quote]

Only two?

[quote]even goverment with a SUN FIRE 15K with 80 64-Bit Ultra sparc processors and a Hitachi cabin for data Storage.... sad but true...[/quote]
Governments aren't accountable nor do they give a choice. If they choose to, they can "delete" voters and nobody can do much about it.

If running a for profit company, lose 5 minutes of data of some vocal user and your credibility is destroyed, permanently plastered over google search results.

[quote]Before anybody blames me, want to make a backup every our? every minute? it is ok!you wont loss date at all but performance will drop when doing it[/quote]
It's just an engineering problem. If you want streaming backups or straight up replication, it can be done. No problem really.

But it most likely won't come out of box, regardless of package. And it may require more than just your basic enterprise programmer.

And despite lacking in other areas, NoSQL emerged partly to solve these issues as well.

[quote]that's why companyes make backups at night when nobody is working on the computers..[/quote]
The reason large companies and governments use Oracle or DB/2 is leverage. If something goes wrong and they lose a day's worth of accounting information, causing their yearly IRS report to be missing \$3 million, they just point at the big company who is then responsible for fixing it. Or they will sue them or something.

The price of those big packages is insurance against or in case of data loss and other disasters - not performance, features, or similar.

Share on other sites
flodihn    281
[quote name='Trevanian' timestamp='1314737154' post='4855620']
Am I telling lies if I assume that NOBODY here is a member of a company like Blizzard, EA,etc??, a good advice is use something you can afford, use standards, write a dll to access the database and later if your project turns into a success and it really gives you good earnings, buy the BEST DB, the BEST Clustered hardware, buy whatever you need to make it ever better...

JB
[/quote]

There is no best DB, you got a number of choices which you need to match with your requirements, for example some data will be almost never read, only written, should be persistent and support complex seach queries, then chose a disk based relational database, if you got temporary data, read often but seldom written which because invalid after a server shutdown/crash use a memory based key/value store (or memory resident sql tables).

Do not use one solution for all data, do some research and use the right tool for the job.

Share on other sites
trevanian    119
Looks like I've been trying to walk over the flames..

There is no simple solution for everything, if you are a programmer the "good" solution is the one that works to you, I told before I don't like MS Access, but guess what? I have used it many times and I still use it to store data for simple projects, not every project needs a true SQL database...

I have made just ONE project using a socket server, I used it to give support to a certain amount of users (300+), the users connected to the server that had a temporary memory structure to store the users connected but almost all the data was stored into a MS SQL server (user/passwords pairs, user info, logs, messages), it was only a MS Desktop Engine, but when it was filled with all the users it only used about 1% processor time from a single-socket Pentium 4...

I made the project in vb.net and I assure you it is not my "favourite" language, not at all, but hey!, is a tool that meet the needs of the project...

BTW: You have a project pretty advanced, I have seen the web and it looks good, you have applied the rule "divide a huge project into smaller parts, if they are still too big, divide again", it is a very good aproach making it easy to code, debug and well actually finish the project.

(I don't know if all this rambling would be useful for WIXNER)

Share on other sites
wodinoneeye    1689
I havent checked recently to see what kind of checkpointing and save mechanism exist in the current MYSQL versions.

Potentially if there is no sufficienetly fast/nondisruptive mechanism (where the DB continues to operate while the checkpoint is in progress)
there are ways to program around the limitations (same actually for a DB that is a homegrown DB).

You frontend the 'DB' with a mode switch that can one way push data directly to the DB (normal ops) and the other use the DB read-only (while iimage is being
checkpoint copied) and a temporary patch DB is used along with transaction logging. Once the checkpoint is complete the primary DB is updated from the T logs
and then resumes its normal operation (the copy is used read-only with the temp patching DB in the interim, while the log is applied to the master to bring it upto date).

Seperate server machines would be the destinations/residency of the different image/log/archive file sets and RAID etc could handle the simpler errors.

The image copy then gets copied off to archives and the master is already resumed. This solves the frequent backup requirement with cohesive
backup checkpoint images without stopping operations. The DB files size is an important limiting factor. I had looked at one case of 4GB (seconds to copy) and this
copy speed was part of the decision of how often you would want to checkpoint -- WITH a metered copy so you wouldnt slow down primary operations).
Network speeds betwen servers (a 10K backplane would be nice..) to move the copies is another limiting factor.

Transaction logging would be the first redundancy mechanism - last checkpoint image plus the transaction log set when you have a primary DB failure
(no data lost, a short patching delay and minimizing the vulnerable period when the redundancy isnt available(while a new primary image is rebuilt) - though further schemes of redundant transaction logging
can also be done ). If you want, an addition of a second write-thru DB image could also be used to harden it.

Of course much easier if the native DB has mechanism like these built into them, though how much control you have when integrating ALL the features may be an issue (redundancy + uninteruptive checkpoints).