So I was bored, and I thought it might be a nifty idea to see if I couldn’t figure out based on my years involvement, what the back-end of WoW’s database looks like. Really what inspired this was my trying to figure out why in the world they suggest they have a limited number of character restores, having recently gotten 2 characters restored through that service. Thanks to Turasael who was the GM that got stuck taking care of my mistake. Really the lesson I need to learn here is that deleting characters does all of nothing, the real question to me than became, why is Blizzard so willing to restore a character to its exact state of being, but also mention that they have a limited number of restorations. For this, I’m thinking I actually need to come up with a theoretical version of their database. I imagine I’m going to miss quite a few tables, and I don’t even want to think about the number of attributes that I as a player would simply have no clue about.
That having been said, I imagine the Database design is rather complex, but given the size and scope of the game, that’s not all to surprising. The first thing to understand is that Blizzard has the following mentality: “Let the User’s side maintain the simple math [rolling / timing / placement] and also let the user be responsible for all of the graphics that will be displayed on-screen, but on everything else… let the server track it, because our users will cheat at an ungodly rate. I’m also going to take a guess that Blizzard uses oracle to track all of this for a couple of reasons. The first one of those being that Blizzard has mentioned in the past that they were looking for oracle developers in some of their job postings, and past that they I don’t see them as the type to use an off-brand SQL server, just because of the massive presence that Oracle has on the market, no even controlling Sun Microsystems. So with that in mind, I’m going to make a few more assumptions about their databases.
I’m going to guess that each server is using its own Database, and the start of the table structure is probably something from outside the Server’s local database, in order to keep in contact with the account as a whole [this is also my guess as to why the friends list is restricted the way it is currently, because the individual server database’s don’t talk with each other save for battlegrounds and random instances, that the server only tracks characters and simply pulls the user ID from the system.
So that first table may or may not be localized on the server, but I know it’s going to be pulling from the server at some point to tie a character with an account. So that first table will probably be BlizzardUser or something of the likes. I imagine that the primary key will be the Battle.net username that Blizzard recently moved everyone over to. I also imagine that this table is only storing users who have created characters on the individual server, that way a server isn’t storing data on users that it will never actually see or have to deal with. I also imagine that this table probably has a pretty standard set of attributes that you would find with pretty much any account type table would hold, that would include activation date, a boolean value if the account is available [assuming they aren’t using some kind of billing method to determine if the account is active.] Past that is the entire list of attributes like First Name, Last Name, etc. These are just the standard kind of things that Blizzard ask its customers, and no doubt are part of some overall database.
The next table is probably the most obvious, and that’s the list of characters that the player happens to have. Based on the fact that you can’t have but one name server wide, I’m going to guess that Alliance and Horde are not treated in any way differently, and as such the name of the character also happens to be its primary ID. I don’t see Blizzard as the type to create useless arbitrary numbered ID’s simply to have an ID, when they can simply let the name be the Unique ID. Also with that I’m going to go out on a limb and assume that Blizzard probably wants to tie a few key traits in the database, these include the Character’s Name, Level, and Class and then 5 Attributes related to the look of the character, and the system interprets each based on game logic. The game probably also sorts out all the rest based off that name / level / class in terms of side / background at startup / etc.
Those 5 attributes probably don’t get their own table, and are likely just some kind of number system that have no meaning to anything but the game logic, which passes it down to the client and tells it to paint it with the related textures based off whatever happens to getting passed down.
One thing I’m hesitant to add, but imagine it’s going to have to be in there, is an attribute for the characters location. I don’t think this is something until the /camp action is activated. Until that point, I image that the server is actually the one to take care of keeping track of where the character is at any given time, and at the second that a sufficient timeout between server and client is detected, the server simply shoves the position that it currently has to the database. I just can’t see this table being updated every time the character moves, as that would seem to me like a crazy amount of stress on the system as a whole.
I also imagine this particular table stores a numerical number for the experience. This again would probably only be updated at logout, and the server logic would take it and run with it during any actual gameplay, using whatever that numerical number is + the level that is stored to figure out anything related to experience.
To figure out the various faction related things, I also imagine that there is a faction table, and a characterFaction table which controls how the individual character interacts with a given faction. I imagine that the faction table is based on IDs rather than names, that way the game can take care of naming those individual factions. The table between the faction and character probably just has a current State (Friendly, Honored, Revered, Ect) and than there is also a numerical data type again like the experience that the game handles but gets stored at logout.
Than we move on to the issue of equipment and bags etc. I imagine a few things, first this is going to be a massive table, and the actual item information may not be stored on a by server basis, and instead be taken off site That having been said, I imagine there is a large series of item tables. Is there is going to be a central Item table buried in there, that’s going to have an Item ID [we know the game makes use of an Item ID]. We also know that each item is likely going to have some kind of stats / use function / type / quality. the stats table I’m going to take a shot in the dark and say it’s on its own table, and the item and stats table have some kind of central table between them.
I also assume there is some kind of Use table where items who need it, where the ID is used by the game to determine the model used for any kind of animation / what the item actually does. The type / Quality are probably to determine if it’s a quest item or not. I imagine there are some other attributes here, but I’m not sure from a game user perspective what it would be.
Now I imagine there is some kind of PlayerItems table, with a few unique attributes including “Place” which would signify if its equipped or if it is in the backpack. I am going to guess that the various bags are labeled as “equipped”, and the game uses their ID in the item database to figure out how much bag space the user has. And let the game do the various restrictions on space and the likes.
In addition to all of this, is a table of abilities / abilities known which would be shared by both a character table and the mob table. The mob Table I assume would be some kind of mobId / MobName / Mob. I imagine the server auto-populates an area based off some general in-game logic. This leads us into the talent tree, and why Blizzard said they were having such trouble getting the second tree setup. I’m going to guess that before the “Secondary Tree” that the talents were just linked to the abilities table, [which probably has a lot more attributes than I actually gave it to deal with the damage that an ability does / healing / etc.] In fact I’d be willing to bet that like the Item table that abilities have a tie back to this “Use” table which will give an ID the game can identify for what exactly the ability does.
But back to the talents, I’m guessing it was a combination of Character ID and maybe Ability ID [the extra talent table may not even be needed] and the game constructed the UI based off the links between the ability’s the character would have in the Talent tree. I would like to throw out, that I am probably completely wrong on this, but It was my guess at the overall structure, Enjoy my picture of what I think it looks like:
Of course feel free to correct me if you think I’m completely off base, but for having nothing but WoW to look at I think that’s a reasonably accurate model.
That having been put out there, I’m going to guess the reason they don’t delete characters is actually an issue of not wanting to go through half a dozen different tables to remove all that data, and instead the character is just deactivated. The limited thing is just to stop people from deleting characters on a regular basis and keeping the GM’s busy with some really stupid stuff. I’m not going to say my deleting my own characters wasn’t stupid, but I will defend myself in saying I don’t request character restores on any kind of regular basis. Don’t judge >.>