Review of Log Messages Database Schema

Question

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.

Examples

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
    MessageTypeId(FK)
    Description varchar

MessageType
    Id (PK)
    Name Varchar

Sender
    Id (PK)
    Name Varchar

MessagesLog
    Id (PK)
    MessageId (FK)
    SenderId (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 fulfilled before i delete a record from the database, i thought about creating a separate 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?

Answer 1

First, I love that you have isolated your actual messages into it's own table, that was going to be my first suggestion but you had already gotten there.

Otherwise, I think your first design is good. By keeping the message log itself (the meta data) isolated from the message body you will (with proper indexes) be able to find messages very quickly.

My only word of advice for you would be to do the delete check as part of the transaction that is writing the message (and to make that a small number). This will make the cleanup action part of the message writing logic and keep things moving along quickly.

Something like:

Delete TOP (100) old messages that satisfy criteria

Insert new message