Базы данных: Ведение журнала действий, как обрабатывать различные ссылки?

Ниже описано, как я бы это сделал. У меня есть еще несколько комментариев внизу после того, как вы увидели схему.

Журнал

LogID - уникальный идентификатор журнала

Время - дата/время события

LogType — строка или идентификатор

(побочный комментарий, я бы пошел с идентификатором здесь, чтобы вы могли использовать таблицу сообщений, показанную ниже, но если вы хотите быстро и грязно, вы можете просто уникальную строку для каждого времени журнала (например, «Игра началась», «Сообщение отправлено» , так далее)

ЛогАктер

LogID - внешний ключ

LogActorType — строка или идентификатор (как указано выше, для идентификатора вам понадобится таблица поиска)

LogActorID — это уникальный идентификатор таблицы для типа, например «Пользователь», «Группа», «Игра».

Последовательность - это упорядоченность актеров.

Сообщение журнала

LogType - внешний ключ

Сообщение - длинная строка (varchar(max)?)

Язык - строка (5), поэтому вы можете выбрать другой язык, например, "US-en"

Пример данных (используя ваши 3 примера)

Журнал

ID  Time   LogType 
1   1/1/10 1
2   1/1/10 2
3   1/1/10 3

Логактер

LogID LogActorType LogActorID Sequence
1     User         1          1
1     User         2          2
2     User         1          1
2     User         2          2
2     User         2          3
2     Game         1          4
3     User         3          1
3     Group        1          2

LogMessage

LogType Message 
1       {0} Made a new friend {1}
2       {0}, {1}, {2} played a game ({3})
3       {0} joined a group ({1})

Пользователь

ID Name
1  User A
2  User B
3  User C

Игра

ID Name
1  Name of game

Группа

ID Name
1  Name of group

Итак, вот что приятного в этом дизайне.

• Очень легко расширить

• Он обрабатывает многоязычные проблемы независимо от участников

• Это самодокументируемая таблица LogMessage, которая точно объясняет, что должны говорить данные, которые вы сохраняете.

Некоторые плохие вещи об этом.

• Вы должны сделать сложную обработку, чтобы прочитать сообщения.

• Вы не можете просто посмотреть на БД и посмотреть, что произошло.

По моему опыту, хорошие стороны такого дизайна перевешивают плохие. Что я сделал, чтобы позволить мне быстро и грязно просмотреть журнал, так это создать представление (которое я не использую для кода приложения), на которое я могу смотреть, когда мне нужно увидеть, что происходит через заднюю часть. конец.

Дайте мне знать, если у вас есть вопросы.

Обновление – несколько примеров запросов

Все мои примеры находятся в sqlserver 2005+, дайте мне знать, если есть другая версия, которую вы хотите, чтобы я выбрал.

Просмотрите таблицу LogActor (есть несколько способов сделать это, лучший зависит от многих вещей, включая распределение данных, варианты использования и т. д.) Вот два:

a)

SELECT 
  LogId,
  COLLESCE(U.Name,Ga.Name,Go.Name) AS Name,
  Sequence
FROM LogActor A
LEFT JOIN User U ON A.LogActorID = U.[ID] AND LogActorType = "User"
LEFT JOIN Game Ga ON A.LogActorID = Ga.[ID] AND LogActorType = "Game"
LEFT JOIN Group Go ON A.LogActorID = Go.[ID] AND LogActorType = "Group"
ORDER BY LogID, Sequence

b)

SELECT 
  LogId,
  U.Name AS Name,
  Sequence
FROM LogActor A
INNER JOIN User U ON A.LogActorID = U.[ID] AND LogActorType = "User"
UNION ALL
SELECT 
  LogId,
  Ga.Name AS Name,
  Sequence
FROM LogActor A
INNER JOIN Game Ga ON A.LogActorID = Ga.[ID] AND LogActorType = "Game"
UNION ALL
SELECT 
  LogId,
  Go.Name AS Name,
  Sequence
FROM LogActor A
INNER JOIN Group Go ON A.LogActorID = Go.[ID] AND LogActorType = "Group"
ORDER BY LogID, Sequence

В общем, я думаю, что а) лучше, чем б) Например, если вам не хватает актера, тип а) будет включать его (с нулевым именем). Однако b) легче поддерживать (поскольку операторы UNION ALL делают его более модульным). Существуют и другие способы сделать это (например, CTE, представления и т. д.). Я склонен делать это как b), и из того, что я видел, это кажется по крайней мере стандартной практикой, если не лучшей практикой.

Таким образом, последние 10 элементов в журнале будут выглядеть примерно так:

SELECT 
  LogId,
  M.Message,
  COLLESCE(U.Name,Ga.Name,Go.Name) AS Name,
  Time,
  A.Sequence
FROM Log
LEFT JOIN LogActor A ON Log.LogID = A.LogID
LEFT JOIN User U ON A.LogActorID = U.[ID] AND LogActorType = "User"
LEFT JOIN Game Ga ON A.LogActorID = Ga.[ID] AND LogActorType = "Game"
LEFT JOIN Group Go ON A.LogActorID = Go.[ID] AND LogActorType = "Group"
LEFT JOIN LogMessage M ON Log.LogType = M.LogMessage
WHERE LogID IN (SELECT Top 10 LogID FROM Log ORDER BY Date DESC)
ORDER BY Date, LogID, A.Sequence

NB — Как видите, проще выбрать все элементы журнала по дате, чем по последнему X, потому что для этого нам нужен (вероятно, очень быстрый) подзапрос.

Мой ответ на Что лучше стратегия хранения данных журнала в базе данных?:

Это зависит от цели регистрации. Для целей отладки и общего мониторинга будет полезна единая таблица журнала с динамическим уровнем журнала, чтобы вы могли в хронологическом порядке просматривать, через что проходит система.

С другой стороны, для целей аудита нет ничего лучше, чем иметь дублирующую таблицу для всех таблиц с каждым действием CRUD. Таким образом, каждая информация, захваченная в таблице платежей или что-то еще, будет захвачена в вашей таблице аудита.

Итак, ответ оба.

Редактировать: чтобы реализовать его чисто с ссылочной целостностью и иметь всю гибкость, я предлагаю иметь дублирующую таблицу контрольного журнала для всех CRUD для каждой таблицы, даже если она "тяжелая". В любом случае бизнес-правила более изменчивы по сравнению со структурами данных, поэтому, сохраняя логическую логику в коде/запросе, вы сохраняете гибкость. Например, предположим, что вы решили не отслеживать, когда пользователи покидают группу. Позже клиенты спросили, что очень важно отслеживать информацию. Все, что вам нужно сделать сейчас, это изменить запрос, чтобы удаление записи user_group было частью результата.

Вам это нужно для регистрации/отслеживания или для отображения пользователям и администратору? Если вы используете для ведения журнала/отслеживания (т.е. для чтения компьютером), вам, вероятно, следует разделить ведение журнала на несколько таблиц, как вы указали.

Однако, если вы хотите это для своих пользователей или отображать на экране, почему бы просто не сохранить его в базовом html? Таким образом, вы можете легко вывести его на экран и просмотреть.

Например, «Пользователь A (ссылка на пользователя), B (ссылка на пользователя) и C (ссылка на пользователя) играли в игру (ссылка на игру)» будет

<a href="/users/showuser.php?id=2341">User A</a>
, <a href="/users/showuser.php?id=311">User B</a>
, and <a href="/users/showuser.php?id=89">User C</a>
played a game of <a href="/games/gameoverview.php?id=3">Chess</a>.

1. Сделайте его простым и расширяемым
2. Не позволяйте накладным расходам на перевод влиять на производительность, перевод необходимо выполнять только для целей вывода.

Предложение:

 LogId       DateTime   Action   Role  Entity

 e.g.

 30303     1/1/10    43        Sender   John
 30303     1/1/10    43        Receiver Sam
 30304     1/1/10    44        Game      game43
 30304     1/1/10    44        Player    Sue
 30304     1/1/10    44        Player    Mike

(В приведенной выше таблице «Сообщение», «Отправитель», «Джон», «game43» и т. д. не будут текстом, а будут внешними ключами в таблице действий, ролей или сущностей. Я написал ключи для «Действия», но не для «Роли» или «Сущности», но они также будут ключами.

Теперь вместо текстового действия, Роли, Сущности у вас могут быть ключи и хранить их в отдельной таблице. Это можно использовать для вывода, например.

 Action Table

 Id    ActionKey   Text      Language
 1     43          JoinGame  English
 2     43          Jeu       French
 3     44          Message   English
 ...
 ...

 Role Table
 Id   RoleKey  Text  Language
 1    1        Sender   English
 2    1        Sendeur  French        (I don't know french :)
 ....

 Entity Table
 EntityKey   Text
 1           Sam
 2           game43
 3           Sam

Обратите внимание, что в таблице сущностей 2 или более записей могут иметь одинаковое текстовое представление, так как может быть более 1 пользователя с именем Сэм. Если вы хотите представить различную ортогональную информацию о каждом объекте, вы можете включить EntityKey в соответствующую таблицу, например.

Person Table
Id    EntityKey   FirstName  LastName ....
1      1          Sam         Johnson

Game Instance Table
Id    EntityKey   GameType  
1     2           444

Game Table
Id   Name   MaxPlayers ...
444  Quake    10

По сути, мы отображаем предикат (в смысле логики предикатов первого порядка, набор кортежей) в форму бинарного отношения (путем создания искусственного объекта, ключа действия). Таким образом, таблица Entity в основном содержит различные столбцы/аргументы отношения, и поэтому это может быть практически все, что может быть аргументом отношения. Преимущество этого представления в том, что оно бесконечно расширяемо для новых отношений, которые вы можете регистрировать без изменения схемы. Все в ActionTable должно быть ключом или внешним ключом, я просто не помещал это туда, потому что это может быть труднее читать.