14

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.

Tulains Córdova
  • 39,201
  • 12
  • 97
  • 154
ChrisW
  • 3,387
  • 2
  • 20
  • 27
  • 3
    You need to separate out the information used for making business decisions from general logging information. Store anything that will be later used by your application in the database, the other stuff in log files. – tom Dec 19 '14 at 09:27
  • 1
    If you're using a well-known package like log4net - and it would probably be a good idea rather than rolling your own - it should be just a matter of flipping configuration to do either. @tom - separation is nice, but why not simply have two databases, one for live data and one for archiving logs etc.? – Julia Hayward Dec 19 '14 at 09:29
  • @Julia, two databases is definitely an option, but this does look like classic access log information so should probably stay like that unless there's a decent reason to save it all to db. That way it can be rolled/analysed using normal server admin tools. – tom Dec 19 '14 at 09:35
  • 2
    The OP says it's for monthly billing - so I'd have expected applying the billing logic to a db would be far easier than flat files? – Julia Hayward Dec 19 '14 at 10:04
  • To do billing I need to count the number of URLs read (i.e. of logged events), for each user, within the billing period. That means reading/counting lines (of text) from one or more log files, or using SELECT COUNT in SQL (neither of which seems too difficult). – ChrisW Dec 19 '14 at 13:32
  • 1
    You used the term "SQL" when you meant "database". I made some corrections. SQL is a language you use to read and write to databases. MS SQL Server is the name of a RDBMS. "SQL" alone doesn't mean "MS SQL Server database". – Tulains Córdova Dec 19 '14 at 16:01
  • possible duplicate of [Why is filesystem preferred for logs instead of RDBMS?](http://programmers.stackexchange.com/questions/92186/why-is-filesystem-preferred-for-logs-instead-of-rdbms) – gnat Mar 23 '16 at 11:50
  • 1
    @gnat I don't think this is a duplicate: the other was about logging errors whereas this is about logging usage for billing (and in this case the upvoted and accepted answer was to use a RDBMS). – ChrisW Mar 23 '16 at 11:56

1 Answers1

15

Since you are using this information for billing purposes, I do not see why you would not want it in the database where it can be easily queried, aggregated, reported on, and joined to other data.

I also think it's much easier to maintain a single database table containing the log information than a bunch of separate log files. Same with your concern about the load on the server - there are much better ways of dealing with that than resorting to keeping data in flat files.

Your third option, by the way, is to do both. Use the database for most needs, but have the log file for auditing purposes.

GrandmasterB
  • 37,990
  • 7
  • 78
  • 131
  • 3
    Another bonus to using a database is utilizing triggers for certain logging. No additional code would be necessary. If data gets inserted into table A, then insert log message X. – Greg Burghardt Dec 19 '14 at 13:16