Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Is my reasoning correct in splitting up tables with similar structures since they contain unrelated data?

I'll explain the situation with an example :

Suppose there is a server which hosts an online game's database. The game has multiple gameworlds which operate in parallel. The schema for each gameworld of the game is same, let's say there are 15 relational tables in the database design of the game. Also assume that the server decides not to delete the data ofold servers and instead, archives it.

Approach 1 :

Now since the gameworlds are hosted on one server, It might seem logical to put all the game data into one database containing 15 tables. The table structure of an example table might look like this :

CREATE TABLE `table` (
    playerid ...,
    score ....,
    gameworldid ...,
    PRIMARY KEY(gameworldid,playerid)
) ENGINE = InnoDB

So, everytime I need to show top 10 players with most score, I will have to do something like SELECT * FROM table WHERE gameworldid = {id} ORDER By score DESC LIMIT 10

Approach 2 :

Since the data for one gameworld doesn't relate to data of the other gameworld in any way (except for the structure), we may make different databases for different gameworlds, or different set of tables for each gameworld. The table structure will look something like this :

CREATE TABLE `gameworldid_table` (
    playerid ...,
    score ....,
    PRIMARY KEY(playerid)
) ENGINE = InnoDB

In this case, top 10 players with most scorequery will be SELECT * FROM table ORDER BY score DESC LIMIT 10

The question is, which approach is better in this situation? The storage engine being used is InnoDB, and in all , 6 billion and up rows can be expected to be there in the biggest merged table, and 500 and up gameworlds. (Up since the old data isn't deleted, so when a gameworld restarts, a new gameworld is created.

In my opinion, the advantages of Approach 2 over Approach 1 are :

  • Less space consumed by primary key and indices, making them more efficient
  • Faster sorting, joining and other stuff as compared to merged table in approach one which contains lots of extra data that might come in way of these operations.
  • Faster inserts as compared to ones in larger tables
  • Data won't keep just piling up forever in the same table

And the advantages of Approach 1 over Approach 2 ?

  • Better manageability : 15 tables sound better as compared to 500 * 15 tables, if you ever have to do some manual fixing (rare).
  • Sounds less insane
  • Too many tables may just make database slower ( don't know for a fact, can't find it on the internet)

I guess my opinions on advantages and disadvantages may be biased, but I really want to know what all problems can come my way if I choose one Approach over the other. Also, is there any other possible solution for this solution?

share|improve this question
    
How does your data change? Are you only ever inserting data? How do you access your data? Do you only look for the top N players per game? If you go with games split by world, do you have any concerns about the additional administrative overhead of applying the same changeset to all of them? What if you have uneven growth? Game C takes off and has 5.9 billion players/games and the others share the remaining .1 Your current assumption sounds like it's based on symmetrical growth –  billinkc Sep 2 '13 at 13:46
    
Data is regularly inserted and updated, there are no deletes at all (the db also serves as an archive for old game data). Data access: all kinds, looking up player information, joining, sorting, id lookup, etc. If there are certain changes to be made in the table design, it can easily be done for all the split up tables. And, I don't understand how uneven growth in different gameworlds matter –  Peeyush Kushwaha Sep 2 '13 at 13:56
add comment

2 Answers

up vote 1 down vote accepted

Your issues are probably less about performance, assuming reasonable equipment and a good indexing structure, and more about other impacts that you face.

Approach 1 means some additional programming complexity to deal with filtering rows in each table to restrict access to a single gameworld. It also means that if you need to restore a gameworld, you would have to (a) restore all gameworlds (since they are in the same database) or (b) restore the one database to a recovery database and then script the appropriate gameworld data from Recovery to your Gameworlds database.

Approach 2 means that you need to be able to manage schema changes across many databases. This can be done by scripting as needed. It is also easier to choose to have different schemas for some gameworlds, but that adds management overhead. However, if a a single gameworld crashes, you can restore the latest good backup(s) and get it going again.

Some years ago some Microsoft folks wrote up their take on Multi-Tenanted databases. See: http://msdn.microsoft.com/en-us/library/aa479086.aspx

Having more tables should not, in itself, cause performance problems within the size range you describe.

Choosing your approach should take into account leveraging your strengths so that you have a system you (and your project members) can readily manage.

share|improve this answer
add comment

If you have a small number of game words and don't expect to scale massively in that direction (allowing some or all classes of user to create their own world for instance) then what you have here is essentially the same as a "standard" multi-tenanted architecture choice - only each tenant is an instance of the game rather than a completely different client.

Assuming that the game worlds do not need to share data at all (do users exist in more than one world and need/want to share data between them?) then I would be tempted to go for the separate database approach but not for immediate performance reasons (with good design including index choices and adequate hardware, performance need not be any different between one world and several in the same DB). Splitting each world into its own DB removes some code complexity as you never need to be aware of the multiple worlds (each DB only has one), and allows you an extra scaling option as your needs grow: you can split the database role between multiple machines (or more easily split them between different spindle sets if I/O is the bottleneck rather than anything else so multiple machine is over-kill) by moving the world databases around. A related advantage is that backups are smaller individual units which may help (for instance you can restore an individually game world much more easily, either if corruption/loss occurred due to hardware fault or if data was damaged due to user error) or may hinder (a single database may be less hassle to manage/schedule/monitor).

Have a search for "multi-tenanted architecture", here and generally, and you will find a lot of good articles discussing the common approaches and their pros and cons. One example article to start would be http://msdn.microsoft.com/en-us/library/aa479086.aspx

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.