I have a system where certain clients actions are being logged to a central database for statistical reporting. The problem is a small number of users can generate tens of thousands of small records a day. Within a month we're into a million records added to a single table.
My question is, what's the best design
pattern to handle this kind of usage? There's a few solutions I can think of, but some guidance for those that have experienced this kind of pattern would be nice:
Leave it as it is. Eventually backing-up/restoring the database becomes a time consuming operation even though the disc space of the database isn't growing as fast. As mentioned, the system definitely slows down as the table growsWipe the table to disk daily. Good solution although then statistical reporting becomes a process of merging text files. The advantage is the table stays small in the long runNormalization/Schema changes: I've considered concatenating database records via normalization although this will great greater database connection and the number of records would still be the sameDisconnect the statistic database from the operations database. Good solution for clarity of data, but if the two databases are on the same machine and the operations are synchronous, it doesn't really solve the problem [ April 16, 2008: Message edited by: Scott Selikoff ]