Results 1 to 4 of 4
  1. #1
    Join Date
    Mar 2013
    Posts
    2

    Unanswered: Question about Best Practice - Table/Column Relations

    Im building a new fairly complex project and I want to ensure the infrastructure I'm putting together follows the "best practice" for the type of solution Im designing.

    My quandary revolves around the design of a system that utilizes time stamp / event / source of data entries.

    For example (keeping things simple), I have Table A, which is designed to keep track of "Comments". Traditionally, I would store the AccountID, DateTime, and other common columns within this table; however, my project is log-intensive. All key events (of taking an action) are properly logged, and those events have the AccountID, DateTime, etc. embedded in them; so if I have a log system that stores that information (e.g. AccountID, DateTime, EventType, etc.). As such, the same details in Table A (AccountID, etc) become redundant/duplicate.

    I figured omitting the AccountID and DateTime, etc. from the Comments table was the way to go as I could JOIN any queries of the comments to the logs that match to obtain the AccountID.

    But am I overanalyzing this? Should I store AccountID, DataTime, etc. in the comment table itself in the event theres data degradation and treat the event table as a separate thing? Or is the act of needing to JOIN an event table (which will be very large) in order to obtain standard query information too burdensome?

    Keeping in mind adjustments and history changes are a part of this as well. What if the person updates the comment 3 times? The event system tracks that.

    What are the best practice techniques for something like this? Granted I can make it work either way; the question is once a system like this is being hit by thousands of users at a time (if not more), what system is the best route to go considering the duplicate/time issues?

    Thanks!

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Seems to me that your Comments table is an actual entity, while your log tables are for archiving.
    As such, I'd never exclude a field from a database's schema just because its also being recorded for auditing purposes.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Mar 2013
    Posts
    2

    Question

    In a 1-1 or 2-1 ratio, I would agree; but what about having 10 different tables that track different things, but all share a similar AccountID, DateTime, etc. field - and the event log tracks all of that anyway?

    Is it a common practice to say "any event tracking must stand on its own" even if it replicates the details across the board?

    Also, there's the issue of multiple entries. Using my Table A Comments example, what about comments that are updated, deleted, and edited? The event logging table will track all of those details, but do I also want to redundantly track the same in the comments table as well?

    Where does "best practice" kick in relative to redundancy based on table "goal" (e.g. this is a "comments" table) vs. proper event logging that will always resolve to the source table the event is related to?

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Production tables show the current state of the application.
    Event log and archive tables show historical "snapshot" points-in-time.
    Regular "normalization" rules regarding repeating data in multiple tables don't apply, because relational integrity is not the goal of event log or archive tables.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •