Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>I'd have a separate table which tracks events and their corresponding XP adjustments per-user:</p> <ul> <li>xp_events <ul> <li>event: The event as a string or foreign keyed ID or whatever.</li> <li>user: The user in question.</li> <li>delta: The corresponding XP change.</li> </ul></li> </ul> <p>Then store both the total XP and the current level with your users. Add triggers to <code>xp_events</code> to update the total XP and current level in the user table whenever xp-events are added. I'd recommend against ever altering or removing entries in <code>xp_events</code>, an audit trail is quite useful and you can always undo one event by adding another event.</p> <p>This approach gives you your summary information pretty much for free so you don't have to do a bunch of extra aggregation to compute a listing page of your users. You also get a nice history in <code>xp_events</code> for auditing or just in case people want to see their progress. As an added bonus, you get an audit trail in <code>xp_events</code> that you can use to regenerate each user's total XP and current level when you have bugs in your triggers.</p> <p>You'd also want to have the xp-to-level information in the database, your triggers will need it and it can't hurt to have all your data in the database anyway. Something like this:</p> <pre><code>min_xp | level -------+------ 0 | 1 100 | 2 500 | 3 1000 | 4 ... </code></pre> <p>Then you can get the level for <code>$xp</code> XP with:</p> <pre><code>select max(level) from xp_levels where $xp - min_xp &gt;= 0 </code></pre>
    singulars
    1. This table or related slice is empty.
    1. POPHP/SQL: Leveling/XP System for users on a website. How should I store the data? Thinking about sessions
      singulars
      1. PTQuestion
    1. PTAnswer
    1. This table or related slice is empty.
    1. USmu is too short
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    1. COThanks a lot. I like the idea of an event list to see why someone got X amount of XP. Could be very nice for the users. "Event" would be the reason of the XP change, right? Something like: event: "answered correct", user_id: "1", delta: "+5". What do you mean with triggers? I thought of a php function that updates the xp_event table, then the user table that stores the total xp (old total xp + xp change) and then checks if the xp is high enough for a new level and updates it too if required. Do you have another solution?
      singulars
      1. PO
      1. USDiLer
    2. CO@DiLer: Yeah, the "event" would be whatever triggered the XP change, I couldn't think of anything else to call it. Triggers are bits of code inside the database that do things to your data when rows are inserted, updated, or deleted; here's some [MySQL information](http://dev.mysql.com/doc/refman/5.6/en/triggers.html). You could do the same thing in PHP but you could have concurrency issues if two XP events are being processed at the same time, using a database trigger avoids that problem; the trigger would fire when you inserted something in `xp_events` and it would update the user for you.
      singulars
      1. PO
      1. USmu is too short
    3. COOh thanks, I didn't know that. I'll definitely give it a read. Looks very interesting. :)
      singulars
      1. PO
      1. USDiLer
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload