0

Consider following table structure, for the backend of a website:

[Article]
ArticleId
Text
TotalViews
AuditModifiedOn
AuditModifiedByUserId

[User]
UserId

An anonymous/not authenticated user visits an article, and this causes the TotalViews counter to increase. What should the AuditModifiedByUserId become when updating the record?

  • NULL (because we have no UserId)
  • The UserId of an "Anonymous" user specifically made to handle these situations.
6
  • 3
    A random user from the internet visiting an article does not constitute an "audit". What do these fields really represent? Just a "last accessed"? Apr 13, 2016 at 13:45
  • @KilianFoth The last website-user who updated the record. The executing SQL user is not the same as the authenticated website-user. This way it's possible to keep track of who changed what (by using triggers + a history table).
    – Aphize_
    Apr 13, 2016 at 14:09
  • Do you permit anonymous / unauthenticated users to change this data, or must they be logged in to change this data? I think the answer to that question fundamentally answers your question.
    – Brandon
    Apr 13, 2016 at 14:39
  • This looks like bad security. Ephemeral data which hardly needs protection (TotalViews) side by side with business-critical data (ArticleId)? Any user obviously should be able to change the view count but not the ID.
    – MSalters
    Apr 13, 2016 at 14:45
  • 1
    If you initiate the ModifiedById column with the id of the article creator, there'd be no reason for that column to ever be null since presumably an article needs to be created by someone/thing. Apr 13, 2016 at 16:11

3 Answers 3

2

The user viewing this doesn't modify the document, and so I wouldn't expect you to update this.

From your comment above, I would expect the user modifying the document to be authenticated, and that's the user record to amend the document entry with. If it was modified by (say) an automated scheduled task then you may want a SYSTEM user or similar, but otherwise I think you should be getting an authenticated user from the web request.

I certainly don't think an audit column should be NULLable.

Looking at this, it seems the modelling is incorrect. I would suggest perhaps you need a table representing the document, the version, audit info etc., and a separate table storing viewing information (with a foreign key linking to the document table).

1
  • I agree with this answer. I'd model it [Article], [ArticleView], [User]. Article View would just be the ArticleId, AccessDate and the User name since the user accessing the article may be anonymous. Also for historical data, a user may access an article and then delete their account.
    – Fran
    Apr 13, 2016 at 15:05
1

There are several ways to handle your audit. Most professional websites consider a user also unregisterd people tracking them with cookies. So your table could be (This is a very simplified example):

[User]
UserId
SessionId
Registered

Where Registered is a boolean value. Amazon and a lot of big websites tracks your actions when you are not registered with cookies; then when you register it associates a mail and a username (and other information you put in the register form) with your previous actions generating a more complete profile even before your registration.

So in the beginning you can identify a user with cookies stored in the SessionId column and eventually , if the user registers identify him with a username.

0

In my experience, the user column is usually filled in with a "system" account or some other indicator that the update was performed automatically rather than by an explicit user request. I do agree with the others, though, that this particular part of the database design should be refactored. I'd suggest a separate table containing the article ID, a timestamp (when the article was read) and a user ID. If required, you could create a view that mimics the current schema, with the TotalViews column as the sum of the count of article reads.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.