Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>I've implemented a reward system once in what you would call a document oriented database (this was a mud for players). Some highlights from my implementation, translated to PHP and MySQL:</p> <ul> <li><p>Every detail about the badge is stored in the users data. If you use MySQL I would have made sure that this data is in one record per user in the database for performance. </p></li> <li><p>Every time the person in question does something, the code triggers the badge code with a given flag, for instance flag('POST_MESSAGE'). </p></li> <li><p>One event could also trigger a counter, for instance a count of number of posts. increase_count('POST_MESSAGE'). In here you could have a check (either by a hook, or just having a test in this method) that if the POST_MESSAGE count is > 300 then you should have reward a badge, for instance: flag("300_POST"). </p></li> <li><p>In the flag method, I'd put the code to reward badges. For instance, if the Flag 300_POST is sent, then the badge reward_badge("300_POST") should be called.</p></li> <li><p>In the flag method, you should also have the users previous flags present. so you could say when the user has FIRST_COMMENT, FIRST_POST, FIRST_READ you grant badge("NEW USER"), and when you get 100_COMMENT, 100_POST, 300_READ you can grant badge("EXPERIENCED_USER")</p></li> <li><p>All of these flags and badges need to be stored somehow. Use some way where you think of the flags as bits. If you want this to be stored really efficiently, you think of them as bits and use the code below: (Or you could just use a bare string "000000001111000" if you don't want this complexity.</p></li> </ul> <blockquote> <pre><code>$achievments = 0; $bits = sprintf("%032b", $achievements); /* Set bit 10 */ $bits[10] = 1; $achievements = bindec($bits); print "Bits: $bits\n"; print "Achievements: $achievements\n"; /* Reload */ $bits = sprintf("%032b", $achievments); /* Set bit 5 */ $bits[5] = 1; $achievements = bindec($bits); print "Bits: $bits\n"; print "Achievements: $achievements\n"; </code></pre> </blockquote> <ul> <li><p>A nice way of storing a document for the user is to use json and store the users data in a single text column. Use json_encode and json_decode to store/retrieve the data.</p></li> <li><p>For tracking activity on some of the users data manipulated by some other user, add a data structure on the item and use counters there as well. For instance read count. Use the same technique as described above for awarding badges, but the update should of course go into the owning users post. (For instance article read 1000 times badge).</p></li> </ul>
    singulars
    1. This table or related slice is empty.
    1. POBest way to code Achievements system
      singulars
      1. PTQuestion
    1. PTAnswer
    1. USCase
    1. USKnubo
    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
    2. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    1. COThe classic trend in badge systems is to add a new field for the new statistic to your table. To me, that seems like a bit of an easy way out and bad idea because your storing mirrored data that can be calculated from data already in the table (maybe a simple COUNT() which is VERY fast on MyISAM tables, will be 100% accurate). If performance was your goal, you'll need to do an update AND select to get the current e.g. post_count value to check if a badge should be awarded. You could of only need one query, COUNT(*). I agree for more complex data there would be good reason to add a field though
      singulars
      1. PO
      1. USGary Green
    2. CO@Gary Green It is not only an easy way out, it is also the scalable way and compatible with document databases. As for correctness, you are right, though for a badge system I would rather have it quick and most likely correct than 100% correct and slow. One single count is probably quick, but when your system scales and you have lots of users it is not given that strategy holds.
      singulars
      1. PO
      1. USKnubo
 

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