Review of Log Messages Database Schema

so i have been tasked to design a log database and i would appreciate some feedback about my design.

I have an application that consists of three basic parts:

  • A frontend
  • A backend
  • A low level component

Each part can create a log message which needs to be stored in a database. The parts(front end, backend, low level component) that create the log messages should be uniquely identified. At the same time when looking at a message it should be possible to see which part created the message.

Each message has a specific type assigned to it and the type can be one of the following destinct values

  • Error,
  • Warning,
  • Info,
  • Debug

The message itself should also be unique, it should have a text that says what is the problem and possibly also a description with extra information about the problem and under which circumstances it could have happened. In addition the time the message was created is very important. Because of the low level component we need microsecond accuracy.


Message : Pump Failure

Description: The pump is not pumping enough oil. Check the amount of oil and also check the temperature of the system.

Finally there are some extra "requirements" that in my opinion could affect the design of the system: The low level component produces a lot of messages in a short amount of time. This could lead to the database reaching its storage limit relatively fast. In that case the older messages should be deleted first. However there are rules that need to be taken into consideration before deleting a message. An info is less important than a warning and a warning is less important than an error. Another rule is unless I have reached a specific threshold I am not allowed to delete messages of a specific type e.g Only if have more than 500 errors am I allowed to start deleting the older errors.

My current design is the following:

Message     Id (PK)     Name varchar     Description varchar 
MessageType     Id (PK)     Name Varchar 
Sender     Id (PK)     Name Varchar 
MessagesLog     Id (PK)     MessageId (FK)     SenderId (FK)     NotificationTypeId(FK)     Date BigInt 

However taking into consideration these extra requirements and thinking that I will need to do a lot of checking on an application level if certain criteria are fullfiled before i delete a record from the database, i thought about creating a seperate table for each message type:

Message     Id (PK)     Name varchar     Description 
Sender     Id (PK)     Name Varchar 
MessagesLogError     Id (PK)     MessageId (FK)     SenderId (FK)     Date BigInt 
MessagesLogWarning     Id (PK)     MessageId (FK)     SenderId (FK)     Date BigInt 
MessagesLogInfo     Id (PK)     MessageId (FK)     SenderId (FK)     Date BigInt 
MessagesLogDebug     Id (PK)     MessageId (FK)     SenderId (FK)     Date BigInt 

What do you think?