0

I want to plot the number of players, in the last seven days, of an MMORPG which haves 50~ servers. The system fetches the number of players in each server every 15 minutes and I'm not really sure how to store and update this information in an efficient way.

The easiest (but probably not so efficient) way I can think of is just having three columns, "time_fetched", "server", "no_players". Every 15 minutes it inserts 50 new rows and deletes the 50 oldest rows.

Maybe I could store weekday, hours and minute instead? So instead of deleting and inserting rows it just updates the correct rows? This would mean that I always have a fixed number of rows in the table (50 * 4 * 24 * 7 = 33600).

Also; would it be more efficient to store the server name as an enum instead of just strings (or some other way, maybe one table for each server)?

1
  • 1
    Quick tip: There's almost no difference in PostgreSQL between an UPDATE and DELETE + INSERT. An UPDATE essentially performs a DELETE followed by an INSERT anyway, all you save is possible time executing row selection predicates. Apr 29, 2013 at 5:59

1 Answer 1

3

I would store the timestamp, server identifier, and number of players in just the way you thought of, deleting the oldest (if you need to) and adding new ones every 15 minutes.

Moving the server names into another "server" table and referencing the primary key in your player count table would be a more conventional normalised design.

With reference to the trigger, don't forget that you can perform both the insert and the delete in the same SQL statement with a common table expression: http://sqlfiddle.com/#!1/c8362/5

4
  • Yeah, I guess that would be simplest. I haven't been using databases in a while and completely forgot that creating a server table would be much better instead of creating an enum. Also, I guess the removing of old entries can be done with a trigger.
    – simon
    Apr 28, 2013 at 18:03
  • See modofied answer with regard to the trigger. I'm not a fan of triggers. Apr 28, 2013 at 18:14
  • I don't see the need for a CTE. Just delete old rows. Otherwise, the answer is solid. +1 Apr 28, 2013 at 20:46
  • 2
    I'd expect that the insert would be in bulk anyway, and the deletions would all be carried out in one other statement. I like this combination of adding and removing in a single statement much more than triggers though Apr 28, 2013 at 23:23

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.