locked
Designing an Activity Table RRS feed

  • Question

  • Hi, i just want to know how you guys design an activity table for the user. I can design the table just like how i design a log table but it got complicated as the user can have different activities. Say for example, the user is an athlete who loves to play basketball, volleyball and chess. 

    On the basketball game, i need to log the minutes played, how much score he did per quarter and so on so of course the volleyball table can have different fields as they are not the same.

    Do you guys separate the log for each type of game or combine them in a single table. All those logs/activity will be view on a grid-view. I must be able to show all those logs at once then be able to filter them. I must be able to filter the logs for the basketball, chess and volleyball.

    Your help will be much appreciated.

    Regards,

    Monday, February 5, 2018 12:27 PM

Answers

  • Ideally you need to have reference table for games  which stores game details like Volleyball, Basketball etc. Similarly there would be a reference table for players like playername, ...

    Then a bridge table with many to many  relationship between player and game. Each entry will correspond to a single game player by a player. Then you can have the attributes stored in two approaches

    1. Using multiple sparse columns

    the approach would be like as discussed here but columns being declared as SPARSE if it holds only very less number of row values

    http://aboutsqlserver.com/2012/02/01/store-custom-fieldsattributes-in-microsoft-sql-server-database-part-1/

    Only thing to note is that in this case, the table will have large number of columns most with NULL values

    2. In XML format

    Single XML column with each attribute represented by the node name within

    This will require some  logic to shred the XML to get data out into columns when you want attributes to be listed againts games

    3.  EAV approach 

    Where we will have a Attribute table which will store attribute values and links to each game through Game_Attribute table

    Then have a Attribute value table which will store value against each game attribute. But this has the disadvantage of involving too many joins + requirement to pivot if you want to show the attributes against a game.

    SO in short the model would look like (assuming EAV approach)

    Activity
    ------------
    ActivityID
    ActivityName (Football,Volleyball etc)
    
    
    User
    --------
    UserID
    UserName (players)
    
    
    
    UserActivity
    ------------------
    UserActivityID
    UserID (FK to User0
    ActivityID (FK to Activity)
    ..
    
    Attributes
    --------------------
    AttributID
    AttributeName
    ...
    
    ActivityAttribute
    ---------------------
    ActivityAttributeID
    AttributeID
    ActivityID
    ...
    
    
    ActivityAttributeValue
    -----------------------------
    ActivityAttributeValueID
    ActivityAttributeID
    Value


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page


    • Edited by Visakh16MVP Monday, February 5, 2018 1:02 PM
    • Proposed as answer by Ashish Kumar Tiwari Monday, February 5, 2018 2:25 PM
    • Marked as answer by Dikong42 Monday, February 5, 2018 2:29 PM
    Monday, February 5, 2018 12:46 PM

All replies

  • Ideally you need to have reference table for games  which stores game details like Volleyball, Basketball etc. Similarly there would be a reference table for players like playername, ...

    Then a bridge table with many to many  relationship between player and game. Each entry will correspond to a single game player by a player. Then you can have the attributes stored in two approaches

    1. Using multiple sparse columns

    the approach would be like as discussed here but columns being declared as SPARSE if it holds only very less number of row values

    http://aboutsqlserver.com/2012/02/01/store-custom-fieldsattributes-in-microsoft-sql-server-database-part-1/

    Only thing to note is that in this case, the table will have large number of columns most with NULL values

    2. In XML format

    Single XML column with each attribute represented by the node name within

    This will require some  logic to shred the XML to get data out into columns when you want attributes to be listed againts games

    3.  EAV approach 

    Where we will have a Attribute table which will store attribute values and links to each game through Game_Attribute table

    Then have a Attribute value table which will store value against each game attribute. But this has the disadvantage of involving too many joins + requirement to pivot if you want to show the attributes against a game.

    SO in short the model would look like (assuming EAV approach)

    Activity
    ------------
    ActivityID
    ActivityName (Football,Volleyball etc)
    
    
    User
    --------
    UserID
    UserName (players)
    
    
    
    UserActivity
    ------------------
    UserActivityID
    UserID (FK to User0
    ActivityID (FK to Activity)
    ..
    
    Attributes
    --------------------
    AttributID
    AttributeName
    ...
    
    ActivityAttribute
    ---------------------
    ActivityAttributeID
    AttributeID
    ActivityID
    ...
    
    
    ActivityAttributeValue
    -----------------------------
    ActivityAttributeValueID
    ActivityAttributeID
    Value


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page


    • Edited by Visakh16MVP Monday, February 5, 2018 1:02 PM
    • Proposed as answer by Ashish Kumar Tiwari Monday, February 5, 2018 2:25 PM
    • Marked as answer by Dikong42 Monday, February 5, 2018 2:29 PM
    Monday, February 5, 2018 12:46 PM
  • Thank you. It helps me a lot.
    Monday, February 5, 2018 2:29 PM
  • Good answer, I would just add one more thing. Using one of these "soft" schema methods is really flexible and easy to use, but it has a major drawback... data integrity (particularly the XML or EAV methods). Because it is flexible, you could end up with chess moves in a volleyball game, and the design would not do anything about it.

    The sparse method is a bit easier, because sparse columns allow check constraints, so you can carve up the tables into valid configurations by saying case when type = volleyball then numberOfChessMoves must be null.

    If data integrity is important, then designing a table per type of activity you are working with is worth it. But it complicates matters greatly, and showing the data in a grid requires you to jump through some hoops. However, having a table for chess and one for volleyball makes it a lot less likely that you have data in the wrong columns

    Good luck!


    Louis

    Without good requirements, my advice is only guesses. Please don't hold it against me if my answer answers my interpretation of your questions.

    Tuesday, February 6, 2018 2:44 AM