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?