In my web application I want to log certain user interactions etc, like "User ABC joined Group XYZ" Therefore I want to set up a logging mechanism that logs into a Mssql database. I'm now trying to find a good database design to achieve flexibility.
First of all, I don't want to log strings like "User ABC joined Group XYZ". I'd like to separate the content "ABC" and "XYZ" from the template. So I'm looking for a templated logging database.
Something like this:
table: UserLog
IDLog int primary key
IDUserLogTemplate int foreign key
date datetime
table UserLogTemplate
IDUserLogTemplate int PK
TemplateString varchar (like 'User {0} joined Group {1}')
IDUserLogType int FK
table UserLogType
IDUserLogType int PK
Name varchar
Description varchar
table UserLogContent
IDUserLogContent int PK
IDLog int FK
PlaceholderPosition int (like '0')
Value varchar (like "ABC")
I think this database structure would give me enough flexibility to add/remove/edit specific log types (like 'User ABC did something else'). For example I might want to add some information to a specific log entry for future entries. Therefore I create a new UserLogTemplate that references the same UserLogType. That is how I would achieve downward compatibility.
Is that a database design that will work? Do you suggest a better design?