2

I am adding "Badges" to my web site, and I am wondering what the best way to store the data would be. Each badge will have an ID that is stored in a MySQL database. Each user has their own row in the MySQL database, and they each have a "userdata" file that is in XML format. So I could save it in that XML file, or I could create a new column in the "users" row and save them like "0;5;6;4;" (Badge IDs).

Brian Tompsett - 汤莱恩
  • 5,753
  • 72
  • 57
  • 129
user1059057
  • 37
  • 1
  • 8
  • Earlier SO work on the subject summarized in an answer to http://stackoverflow.com/questions/9835321/designing-a-pluggable-points-and-badges-system – Walter K Mar 28 '12 at 21:00

4 Answers4

2

Im doing similar with a webiste. And I've yet to come across a conclusive answer. But don't store the badges like 0;5;6;4

Instead have a table called user_badges

Table user_badges
     user_id
     badge_id

And have a table for the badges

 Table badges
    badge_id
    badge_name
    ..
    etc
Sean H Jenkins
  • 1,770
  • 3
  • 21
  • 29
1

This is a pretty broad question, and there's really no way for us to say definitively which of those methods is "best" for your specific site configuration and goals. Either way could potentially work well. Or, you could add a new mysql skinny/relational table called badges_users, which associates user ids with achieved badge ids (which if I'm understanding your goal correctly would be how I would recommend accomplishing it).

Rylab
  • 1,236
  • 7
  • 17
0

Depends on any number of variables such as project timeline, resources, and traffic. If you want to incorporate a nosql solution for storing on the filesystem then you will inevitably get better performance gains because you will not being creating a database connection to retrieve the badge information. But chances are you are already establishing a database connection to get the other user information in the first place so you will probably end up with negligible gains. The SQL solution would be quicker to implement and unless you are handling a lot of traffic, have no noticeable performance degradation.

I think the biggest bang for your buck in this situation is to implement a caching layer for your user information so you don't need to hit the persistent store for every request for user information.

If you opt for the database solution you may want to consider creating a separate table for badges vs adding another column to the users table. A 1:M relationship between users and badges will allow you to add more badges in the future, and you could expand badges without having to mess with user schema.

Mike Purcell
  • 19,847
  • 10
  • 52
  • 89
-1

For this kind of data I’d recommend going with the database, it’ll be faster than reading and writing XML files.

Zoe Edwards
  • 12,999
  • 3
  • 24
  • 43
  • I didn't down-vote, but careful with this statement. There are several nosql solutions available which are much faster than using a traditional RDBMS. http://nosql-database.org/ – Mike Purcell Dec 03 '11 at 00:15
  • Appreciate the comment, but if he’s already making a query to call user data, adding the badges won’t hurt – so it makes sense in this context for this kind of data. – Zoe Edwards Dec 03 '11 at 00:16
  • Agreed, I said pretty much the same thing with that regard in my answer. – Mike Purcell Dec 03 '11 at 00:18
  • Yes you did, I just echoed your comment for no real good reason :) – Zoe Edwards Dec 03 '11 at 00:23