I read an article on Habré about how anti-spam was created for mail. It became very interesting to me how, with such logging of actions, it is correct to store this same log in the database. What is the approximate structure of a database with this approach? How is information in the database "Possible patterns (screenshot)" . Obviously not a string. This question, which affects the correct construction of the database does not give me rest.

Possible patterns [3]

  • Why not keep the line? If you have an array of possible patterns, then serialize it and store it as a string. (This is provided that it is not necessary to filter the data when sampling from this table). - LANSELOT
  • How not to need? Of course, you will need) - Noneme

1 answer 1

CREATE TABLE actions ( action varchar PRIMARY KEY; ... ); CREATE TABLE possible_patterns ( id int PRIMARY KEY; ... ); CREATE TABLE possible_patterns_actions ( pattern_id int; -- шаблон action varchar; -- действие index int; -- порядковый номер действия в шаблоне FOREIGN KEY (pattern_id) REFERENCES possible_patterns(id); FOREIGN KEY (action) REFERENCES actions(action); ); -- получить действия из паттерна 123 по порядку SELECT a.* FROM possible_patterns_actions pa JOIN actions a ON a.action = pa.action WHERE pa.pattern_id = 123 ORDER BY pa.index