I would like to track certain user activity and limit this activity using a time constraint. An example of this could be the votes per day limit enforced by SO. Which of the following methods provides a better solution in terms of scalability and ease of use?
Method 1:
Method 2
I'm using ColdFusion with MySQL, if this matters.
Assuming you only care about this data on a per-person basis, and won't be getting aggregates of this, I'd suggest you go with a key/val store that supports TTL. Redis sounds like it fits your requirements pretty well.
Though it runs in memory, it does snapshot to disk periodically (which mean it's fast, and if something goes down, you'll get most everything back).
Easy interaction, as with your "method 2", but without having to worry about managing a large in-memory structure.
Example Setup
Going with the SO points-per-day scenario, we'll have a time-frame of one day, and our value will be a simple value that increments on some user activity.
Our key would be a combination of the user's id and the current timeframe (day, in this case). After we have set the key, we set a ttl, which means after today is up, the key will eventually expire and clean itself up.
redis> incr user123-2011-10-09
(integer) 1 # no value found for key, defaulting to 1
# if this key already had a value it would be incremented.
redis> expire user123-2011-10-09 86400
(integer) 1 # expiration of 1 day was successful
redis> get user123-2011-10-09
"1" # current value of user key
More info on these commands (incr, get, expire, and ttl would be of most interest to you) can be found on the redis commands reference (complete with an interactive shell to try these things out).
Edit
This method could obviously be applied with any number of k/v stores, I've used Redis here as a concrete example since I believe it meets the requirements closely.