How to go about designing a database for MMO's?

Unity Engine
1

Hi all,

As for this beeing more or less a discussion kinda thread I thought it would be more of a forum post then the asked and answered section

My question is(I think quite straightforward) how would we go about designing a database that holds the data for an RPG. This is purely for learning purposes.

I’m building an online multiplayer RPG with some friends just to see how far we can go.
At this moment the game is working great as a online shooter with some general gear and weapons etc…
I’d like to rewrite it as a Online Multiplayer FPS/RPG.

Few Thinks you’d want to know about the current setup:

  • I´m working with SmartFox as a Game Server and extensions that handle game mechanics.
  • mySQL Database that stores the basic information (PlayerID, Name, Password, inventory and score)
  • Website connects to the same database for viewing the leaderboards, Ribbons, Wall of shame and account administration.

I’m not asking for a code run through; I’m asking for a brainstorm session about how to design the database its tables and there relations. As this is not a easy job to begin with, lets start primarily on the Character and its relation to tables like e.g. Classes, Attributes, skills, etc

Edit: Just because this is my second post doesn’t mean I’m new to unity basically I am a lurker not a poster…
Edit 2: added an image.

Here is something that I was thinking about how to start off…

Kind Regards,

Nicklorion

2

Hey. I am making some kind of MMO / RPG aswell. For me it’s pretty simple. I have two databases. One contains all the players. Structure is like:

ID | Name | Password | LastActivity | isOnline | Settings | Inventory | Abilities | Location | etc.

the other one contains the world data:

ID | Name | Type | State | Location | Rotation

Where for example, I have:

0 | Door(Clone) | Door | Open | Vector3Data | QuaternionData

Which indicates that there is an ‘Open’ ‘Door’ at ‘Location’ with ‘Rotation’.

Remember that you can always design the database and add/ remove fields later.

3

Your game is level based upgrades ?

4

As I understand you want to use the character’s attributes as metadata. I do not particularly like it that way. Some great games out there put on the table all the attributes of the character (yes, a large, ugly and bizarre table), but it’s easier to manage, read and write data.

The metadata model is advantageous only when the data is very dynamic, these attributes are part of the basic logic of the game and insert a new attribute means modifying the game.

This model is based on levels depending on the level of character he has access to certain skills or skills with higher Levels.

Highest level for skill means an improvement in the attribute it modifies.

You also can have active and passive skills. An active skill are those that you have to perform, such as a doctor uses the skill “Bandage” in another character to restore hp. A passive skill is usually applied to classes or items, a sniper can have a passive skill called “eagle eyes” that helps you see at night or a passive skill can be applied to a helmet that’s -5% chance of taking a headshot.

You can define how the character gets the skills. He can get training (the more he uses the skill, it gets better), shopping (with coins he paid a “teacher” to teach).

The green tables are used to store data for progress.

The table “class_attributes” contains the attributes of a class according to the level of the character. (The Sniper is faster than the Heavy Trooper, Heavy Trooper is stronger than a Scout, etc. …)

The table “class_skills” contains the skills that a particular class has access. This table controls the contents of the table “character_skills” (a Sniper can not have a specific skill of an Engineer)

The table “skill_attributes_modifier” contains information for modifying attributes of the character when a particular skill is active. Imagine a skill called “Assault Rifle mastering” which gives 5% chance of success if the character shoot with an Assault Rifle. This skill would modify an attribute called “Performance with Assault Rifles”, something like that.

I made this analysis based on the games I know the same type, War Rock, Combat Arms … If you give more details I can help more.

=]

5

True! And I’ll probably will have to add and remove allot, but first I will write down some sort of design document before building the actual database and code.

I was thinking about that, more along the lines of Fallout 3 and it’s successors.

I was planning on doing that, it somehow felt natural. I must say that the model you described actually fits my needs. But to be honest I’m a Delphi programmer, and SQL is pretty new for me :wink:
I’m going to experiment a bit and will get back to you.

Thanks allot