Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>The following is how I would do it. I have some more comments at the bottom after you have seen the schema.</p> <p><strong>Log</strong></p> <p>LogID - unique log ID</p> <p>Time - date/time of event</p> <p>LogType - String or ID</p> <p>(side comment, I would go with an id here so you can use a message table shown below, but if you want quick n dirty you can just just a unique string for each log time (eg "Game Started", "Message Sent", etc)</p> <p><strong>LogActor</strong></p> <p>LogID - external key</p> <p>LogActorType - String or ID (as above, if ID you will need a lookup table)</p> <p>LogActorID - This is a unique id to the table for the type eg User, Group, Game </p> <p>Sequence - this is an ordering of the actors.</p> <p><strong>LogMessage</strong></p> <p>LogType - exernal key</p> <p>Message - long string (varchar(max)?)</p> <p>Language - string(5) so you can key off different language eg "US-en"</p> <p>Example Data (using your 3 examples)</p> <p>Log</p> <pre><code>ID Time LogType 1 1/1/10 1 2 1/1/10 2 3 1/1/10 3 </code></pre> <p>LogActor</p> <pre><code>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 </code></pre> <p>LogMessage</p> <pre><code>LogType Message 1 {0} Made a new friend {1} 2 {0}, {1}, {2} played a game ({3}) 3 {0} joined a group ({1}) </code></pre> <p>User</p> <pre><code>ID Name 1 User A 2 User B 3 User C </code></pre> <p>Game</p> <pre><code>ID Name 1 Name of game </code></pre> <p>Group</p> <pre><code>ID Name 1 Name of group </code></pre> <p>So here are the nice things about this design.</p> <ul> <li><p>It is very easy to extend </p></li> <li><p>It handles multi-language issues independent of the actors</p></li> <li><p>It is self documenting, the LogMessage table explains exactly what the data you are storing should say.</p></li> </ul> <p>Some bad things about it.</p> <ul> <li><p>You have to do some complicated processing to read the messages.</p></li> <li><p>You can't just look at the DB and see what has happened.</p></li> </ul> <p>In my experience the good parts of this kind of a design outweigh the bad bits. What I have done to allow me to do a quick n dirty look at the log is make a view (which I don't use for the application code) that I can look at when I need to see what is going on via the back end.</p> <p>Let me know if you have questions.</p> <p><strong>Update - Some example queries</strong></p> <p>All of my examples are in sqlserver 2005+, let me know if there is a different version you want me to target.</p> <p>View the LogActor table (There are a number of ways to do this, the best depends on many things including data distribution, use cases, etc) Here are two:</p> <p>a)</p> <pre><code>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 </code></pre> <p>b)</p> <pre><code>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 </code></pre> <p>In general I think a) is better than b) For example if you are missing an actor type a) will include it (with a null name). However b) is easier to maintain (because the UNION ALL statements make it more modular.) There are other ways to do this (eg CTE, views, etc). I'm inclined to doing it like b) and from what I've seen that seems to be at least standard practice if not best practice.</p> <p>So, the last 10 items in the log would looks something like this:</p> <pre><code>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 </code></pre> <p>NB - As you can see, it is easier to select all log items from a date than the last X, because we need a (probably very fast) sub-query for this.</p>
    singulars
    1. This table or related slice is empty.
    1. PODatabases: Making a Log of actions, how to handle various references?
      singulars
      1. PTQuestion
    1. PTAnswer
    1. USHogan
    1. USHogan
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PODatabases: Making a Log of actions, how to handle various references?
      singulars
      1. PTQuestion
    1. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTBountyClose
    3. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTAcceptedByOriginator
    1. COClash: see edit above. I think this addressed most of your questions.
      singulars
      1. PO
      1. USHogan
    2. COWow! I had no idea I could do `AND LogActorType = "User"` on a JOIN clause. Amazing! Thanks a lot for the help. Will try it later
      singulars
      1. PO
      1. USClash
    3. COYes, the JOIN clause is mighty. Not knowing how to use it is the reason for most of the mistakes I see on SO (often using a sub-query instead of a join).
      singulars
      1. PO
      1. USHogan
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload