I'm developing a web application which uses MS SQL for various data: including users, user accounts, user licenses, license prices, invoices.
I need to log users' real-time usage of the system, and use that for monthly billing: e.g. log whenever a user gets a specific page/URL and bill users at the end of the month based on the number of pages they fetched.
Should I write these log events into a table in my MS SQL database ?
Should I write these log events into a non-SQL append-only log file?
Should I write these log events into a different log file for each user?
This is not an especially high-volume web site: for example, a maximum of 10,000 users each doing an average of 5 loggable events/day => 50,000 events / day = 30 events / minute = 18,000,000 events / year.
I'm asking because either option seems viable and I don't see whether one has a clear advantage.
The data associated with a billable event is simple, e.g.:
- User ID (foreign key relationship to the Users table in SQL)
- Date and time
- URL of the billable page
My own answer to this question is as follows:
Some benefits of writing the log to a database table:
- Relational integrity: e.g. logged events are associated with valid user IDs (by defining the user ID as a foreign key between the tables)
- Easy to read for billing: e.g.
SELECT COUNT GROUP BY
to get a count of the number of log events per user
Some benefits of writing to log file:
- Easier performance: SQL is used less often e.g. only for user log-in events, and mostly only used for reading
- Easier management: easier to archive old data e.g. at year end, by moving old log files instead of by deleting/archiving from the database
Please let me know if my answer is wrong; or exaggerates the importance of something; or has forgotten some important consideration.
And/or please let me know what your answer is, if it's different from mine.