I am creating a logging system using MySQL and SQLite with the goal of maximum optimization. This has resulted in a monstrosity of a table structure which breaks the first normal form due to its repeating groups. Here's what I mean by this:
The format of a log in this system looks similar to this:
{1} used tool {2} on a {3} created by {4}
Each {X} is replaced with relevant information to create an informative log on the client. This allows for translating logs into different languages. My original idea was to break the first normal form and use repeating groups to store these replacements.
Here's an example of a processed & replaced log which would be shown to the client:
Jeff used tool remover on an entity created by Steven
The information that replaced the placeholders is stored in the replacement_X_... columns that are shown in the screenshot above.
Advantage: This, as far as I know, should be faster than using a separate table to store the replacements which would require joins or even multiple SELECTs.
Advantage: This structure is friendly for a search feature
Advantage: This structure allows for quick INSERTs, which there are a lot of!
Disadvantage: There is a maximum amount of replacements that are possible, in my case above, six, rather than a variable amount
Disadvantage: Breaks the first normal form, uses repeating groups and is generally just really ugly and difficult to make & process queries for
Is breaking the first normal form, in this case, justified? Or is there a better way of going about this which won't hinder performance and is also appropriate for both MySQL and SQLite?
