locked
Database advice RRS feed

  • Question

  • Hi all,

    I would like some advice on how to implement a particular database. The task itself is simple, but I want to make sure that this is the way forward. So, the task at hand is to create a client/server application that logs game statistics such as player's hardware configuration, missed clicks, playing time, etc. The client which will reside inside the game .exe, will collect this information while the user is playing the game. At some point, the client application sends this information to the server, which is then saved in the database. During the development of the game, the number of attributes may increase. Having said that, the database design must be 'flexible' enough to enter new attributes and information. Also note that the attributes are of different types (i.e. int, string, bool etc).

    After developing a small prototype, I decided to use a Windows Communication Foundation Service, which is basically a web-service. The client sends an object that contains the user-id, game-id and timestamp along with a list of attributes (see the list below). The database design I came up with is the following;

    Attributes
    (

    Id int IDENTITY(1,1) PRIMARY KEY,
    Name nvarchar(20),
    Type nvarchar(10),

    CONSTRAINT UQ_Name UNIQUE (Name)
    )

    Records
    (
    Id int IDENTITY(1,1) PRIMARY KEY
    PlayerId int NOT NULL,
    GameId int NOT NULL,
    Timestamp timestamp,

    AttributeId int NOT NULL,
    Key int,
    Value nvarchar(200),

    FOREIGN KEY FK_Attributes_Id REFERENCE Attributes(Id)
    )

    List of possible attributes;

    Name            Key (Meaning)   Value

    ItemFound       5 (ItemnID)     TimeToFind (in seconds)
    MissedClicks    1 (Level)       Count
    WrongItemClicks 3 (Level)       Count
    HintsUsed       7 (Level)       Count
    TotalClicks     1 (Level)       Count

    Therefore the database will look something like this;

    Attributes Table

    Id     Name         Type
    --------------------------
    1      HintsUsed    Int
    2      ItemFound    Int

    Records Table

    Id
         PlayerId      GameID      Timestamp      AttributeId      Key      Value
    ----------------------------------------------------------------------------
    1      1            1          19-10-10      1               2       5
    1      1            1          19-10-10      2               102     15

    Is this a correct database design for this particular scenario? A problem that I'm seeing is that in order to show these statistics back to the administrator a lot of type casting is required since all values have to be stored in a string (including float, int etc). Is there a better solution for this?

    Comments are greatly appreciated. Thanks for your time and patience
    Monday, August 23, 2010 10:57 AM

Answers

  • Hi,

       Design looks good .... Well ..type casting is required in these type of scenarios , where a generic solution is implemented for ALL different types of data ... From design perspective this design is FINE .. From coding .... we have to do the possible tuning on the queries ..

    -- Reddy Balaji C.

    • Marked as answer by claytoncurmi Monday, August 23, 2010 12:55 PM
    Monday, August 23, 2010 12:08 PM

All replies

  • Hi,

       Design looks good .... Well ..type casting is required in these type of scenarios , where a generic solution is implemented for ALL different types of data ... From design perspective this design is FINE .. From coding .... we have to do the possible tuning on the queries ..

    -- Reddy Balaji C.

    • Marked as answer by claytoncurmi Monday, August 23, 2010 12:55 PM
    Monday, August 23, 2010 12:08 PM
  • You know to suggets on design database is very very hard as it is business requirements and only you know it will all nuans....

    Looks design OK, just hope you have Gameid also FK to tables Games or whatever


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Monday, August 23, 2010 12:20 PM
  • Hey guys,

     

    Thanks for your comments! I was thinking of creating a table like I mentioned before, then by using a batch job that runs say every night, it extracts statistics (like minimum, maximum and average for int values) and stores them into a separate table (which contains statistics for each day).

     

    Any other tips you might wanna share about this approach?

     

    Thanks

    Monday, August 23, 2010 12:54 PM
  • Hi,

      Creating seperate table to maintain statistics is OK .. but we have some alternate solution ... Indexed View ... which is very much usefull if there are many aggregate  fucntions or expressions are used and the data is more static .. you can think about this option ..

    -- Reddy Balaji C.

    Tuesday, August 24, 2010 4:43 AM