CODEWITHSUNDEEP
database-designarchitecture
Tony
Tony

Reputation: 19151

Is it better to track events in separate models or one event model?

I realize this question is general, but I think people with a lot of experience in event tracking will have good insight.

I have a website where I want to track file downloads by user. Two methods come to mind:

1) Create a model called AssetDownload and populate that with the data.
2) Create a model called Event or Activity and have that be a general model for tracking events.

I see the following pros and cons:

Method 1:

Method 2:

I am tempted to go with method 1 because it's sort of a minimum viable product thought process. Just make what I need, I'll eventually add 5 models, 1 for each event type. If I add 20 models then I can refactor using a Single Table Inheritance scheme. But at least at that point, I will know what I am refactoring, whereas designing for the future involves some guesswork right now.

However, I am successfully using Method 2 on another website right now. Just want to see what other people are doing.

Update

I want to mention that the events I am logging will need to be accessed quite often. I will be providing a dashboard where users can view file downloads by user and by date. Please consider this if your answer involves using an Audit log model

Upvotes: 3

Views: 1967

Answers (3)

Chris K
Chris K

Reputation: 12341

There is a 3rd option: 

event_header: 
  id
  date
  time
  type
  code
  ... 

event_type_data: 
  PK(id)
  FK(event_id)
  special_field1
  special_field2

Your download query knows that the event type is say 4, so do a join on the event_data table 

select ev.*, evd.* from event_header ev, event_type_data evd where evd.event_id = ev.id and ev.type = 4

Overcomplication? Perhaps. Slower? Probably. Confusing for future developers? Yes. Viable? Certainly.

Me, I'd probably go with method 2 and have a text field for special data in a JSON or XML format, or simply "key:value,key:value"

Upvotes: 2

David
David

Reputation: 218837

I've generally used Method 2 in my designs over the years. The table width has never come up as an issue because it's generally been very string-heavy for event descriptions. I suppose this means that any audit review will involve a good bit of manual parsing from the auditor, but when you're at the point of an audit that kind of detective work is usually found in any design.

One way to address table width recently, for me, has been to store a lot of details about the event in an XML blob. MSSQL supports it well enough these days, and I can build any simple reporting tool to pull from it anyway. In terms of re-factoring out specific events, etc... this often comes down to just the reporting tools. I'm certainly no data model expert and I can't advise you on very large scale tables, but working with the database folks in the past they've always preferred method 2 as well and have build views/reports/etc. around that.

Upvotes: 1

Richard Harrison
Richard Harrison

Reputation: 19393

Method 2 is the right way of doing it. It's the way I've always done it, except I call it an Audit log and make it very generic and use it for lots of things.

Don't make the table wide instead have multiple entries if you need to make more than one type of entry.

Pseudo DDL - the types may vary.

CREATE TABLE Audit
   Type           # FK identifying the entry type
   DateTime       # entry time
   RequesterID    # FK identifying the user/process initiating the request
   Object         # Filename etc.
   ObjectClass    # FK defining type of the object 
   AccessType     # FK defining the type of access (download etc.)
   AccessOverride # FK set if accessed via impersonation
   Status         # FK result of operation - success / fail
 ;

NOTE: Originally this was loosely based on the VMS Audit log model.

Upvotes: 3

Related Questions