2

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?

2
  • So, what problem does "a separate table for each message" solve? And why do [plural] you think that a relational database, and not a text search engine (like Elasticsearch), is a good choice for storing logs?
    – mustaccio
    Oct 22, 2020 at 23:33
  • @mustaccio The reason why i believe this could be a "good" choice is because I will have to check very often if a message is of a specific type and if I am allowed to delete it. Having the messageType in a separate table might be better in terms of normalization but it will also require making an extra join every time I want to find the type of a specific message.Assigning indexes on the correct fields might help speed up the queries of course. I have never used elastic search so I am not familiar with the technology, however like I said every hint/Feedback is always welcome.
    – Sotiris
    Oct 26, 2020 at 12:39

1 Answer 1

2

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

2
  • Hi thank you for your positive feedback and especially for the advice to keep the deletion as part of the same transaction. I only have one clarification question. When you say make that a small number you mean the transactions?
    – Sotiris
    Oct 20, 2020 at 14:25
  • Sorry, I meant a small number of "old" messages to delete, so you don't get bogged down deleting a bunch at a single time. If delete 1 old message and add 1 new one works for you then that is probably good enough. Oct 20, 2020 at 15:03

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.