Keeping member statistics

[amjohnno]

I'm after a little bit of advice.

 

I aim to keep statistics on my website, placing members in various leagues. During testing, I'm keeping a daily record of the top ten of each league each day, which I will then be able to create charts and tables with over time. What I would like to be able to do is to keep each members statistics permanently so that they can keep track of their own league positions and their various values over time. These would be updated daily. I understand that this will probably take up quite a bit of space (although I don't have a huge number of members at present), but I simply can't figure out the best way to handle the information.

 

At present the only way I can think of would be to create a new table each time a new member signs up - does anyone know of a better way of doing this, or should I simply give up now?! 

[Wildbug]

Another (better) way would be to create a statistics table with member, rank, and date columns (at least).  Then you could use GROUP BY and ORDER BY clauses to sort the data.  Each day, enter the top ten members with their relative rank and the date.

[amjohnno]

Thanks for your reply.

 

I'm saving the top ten members in testing at present, and it works well for establishing the top ten over a period of time. But I wondered if there was an efficient way of storing every member's stats permanently. Each day, every member gets their position in the league recorded, as well as their current score. So, for example, a member could go onto the site and chart their own progression on a daily basis since they joined the site (I'd end up plotting this data on a chart). Whilst I don't have a huge amount of visitors at the minute, I would like to plan for any future eventuality - the system would therefore need to cope with adding a several thousand stats to the database each day.

 

I understand that this would no doubt take up quite a bit of disc space over time, but my main concern would be the efficiency of the system. Whilst I could create new tables for each new member, I'm sure this would be completely inefficient - but I can't think of any other way of allowing me to save everyone's details.

 

That said, I may have misunderstood what you wrote - are you saying I could simply save all stats for all members in the same table and simply have a unique key in that table for each member?

 

If it seems like an impossible task, then let me know - I'll stick to the top ten 

[Wildbug]

No, you don't need a seperate table per member, just a member column.  You can save everyone's stats in a single table.  Maybe an example would be clearer:

 

CREATE TABLE saved_stats (member VARCHAR(255),score INT UNSIGNED,date DATE);
INSERT INTO saved_stats VALUES('John',12,NOW()),('Bob',15,NOW()),
('John',16,NOW() + INTERVAL 1 DAY),('Bob',25,NOW() + INTERVAL 1 DAY);

# Find John's stats:
SELECT * FROM saved_stats WHERE member='John';

member  score  date
John    12     2007-05-21
John    16     2007-05-22

# Find Top Ten for a given day:
SELECT * FROM saved_stats WHERE date='2007-05-21' ORDER BY score DESC LIMIT 10;

member  score  date
Bob     15     2007-05-21
John    12     2007-05-21

 

For a large table, you could define an index on member and/or one on date, depending on what kinds of queries you run against it.

[amjohnno]

Thanks very much for your answer - it certainly does seem like a much better idea than clogging the database up with new tables! I shall give it a go!