locked
Database design for a multiplayer game RRS feed

  • Question

  • Hello, 
    I want to create a game for social network. The game's database must store information about users, their items and scores. It must be possible to add/extract scores and items QUICKLY, because there may be (I hope :)) a lot of players. I think my DB tables should be created like this: 

    CREATE TABLE users ( 
    user_id UNSIGNED INT PRIMARY KEY, 
    score UNSIGNED INT 
    ); 

    CREATE TABLE items ( 
    user_id UNSIGNED INT PRIMARY KEY, 
    item_id UNSIGNED TINYINT 
    ); 

    Also, can the users be added in a sorted (by score) order? If so, how to do that? 

    Any advices, suggestions about improvement and implementation will be highly appreciated.
    Wednesday, May 5, 2010 1:56 PM

Answers

  • Yes, it is possible. Add a UNIQUE CLUSTERED INDEX on Score, Id.

    But it would be a bad idea. You are worrying about speed before having what to store, to the point that you're trying to define how it will be stored.

    First get it designed, then worry about speed. It is unlikely it will be slow. And even if it end up being slow, solutions abound. What can help and which is best can only be determined after the actual problems are encountered. Sure, you can have an eye towards them now, but don't let that change storage!

    Wednesday, May 5, 2010 3:15 PM
    Answerer

All replies

  • What you suggested will work, but there is little for us to go on. For example, score is usually a total of other things. A DB can store the things and total the score for you.

    CREATE TABLE Player(Id INT PRIMARY KEY, Score INT NOT NULL DEFAULT 0);
    CREATE TABLE Item(Id
    TINYINT PRIMARY KEY, Name VARCHAR(20), Description VARCHAR(255));
    CREATE TABLE Player_Item(Player INT REFERENCES Player, Item TINYINT REFERENCES Item, PRIMARY KEY(Player, Item));

    Though, it would be ideal to name the CONSTRAINTs as well.


    Ordering the result set is basic SQL: SELECT Id, Score FROM Player ORDER BY Score DESC;

    Wednesday, May 5, 2010 2:18 PM
    Answerer
  • Brian, thank you for the answer.

    > For example, score is usually a total of other things

    Due to specific of the game scores don't depend on other things, they depend only on time. 

    The reason, why I ask the question is because I don't know if my DB will work fast, especially when getting items of the particular player.

    Ordering the result set is basic

    It isn't exactly what I need :) 

    I want to store all the data sorted by score, because I need a best results table. If the data in the DB will be sorted in descending order, all I need is to show first n results. Is it possible to do such a thing?

    Wednesday, May 5, 2010 3:09 PM
  • Yes, it is possible. Add a UNIQUE CLUSTERED INDEX on Score, Id.

    But it would be a bad idea. You are worrying about speed before having what to store, to the point that you're trying to define how it will be stored.

    First get it designed, then worry about speed. It is unlikely it will be slow. And even if it end up being slow, solutions abound. What can help and which is best can only be determined after the actual problems are encountered. Sure, you can have an eye towards them now, but don't let that change storage!

    Wednesday, May 5, 2010 3:15 PM
    Answerer