Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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)?

share|improve this question
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. –  Craig Ringer Apr 29 '13 at 5:59
add comment

1 Answer 1

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

share|improve this answer
    
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. –  gurka Apr 28 '13 at 18:03
    
See modofied answer with regard to the trigger. I'm not a fan of triggers. –  David Aldridge Apr 28 '13 at 18:14
    
I don't see the need for a CTE. Just delete old rows. Otherwise, the answer is solid. +1 –  Erwin Brandstetter Apr 28 '13 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 –  David Aldridge Apr 28 '13 at 23:23
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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