List:General Discussion« Previous MessageNext Message »
From:Carsten Pedersen Date:May 31 2013 11:05am
Subject:Re: Audit Table storage for Primary Key(s)
View as plain text  
Based on the little information available, I would make a lookup field 
consisting of tablename and primary keys.

(although I still believe that storing this information in the database 
in the first place is probably the wrong approach, but to each his own)

/ Carsten

On 31-05-2013 12:58, Neil Tompkins wrote:
> The kind of look ups will be trying to diagnose when and by who applied
> a update.  So the primary key of the audit is important.  My question is
> for performance, should the primary key be stored as a indexed field
> like I mentioned before, or should I have a actual individual field per
> primary key
>
>
> On Fri, May 31, 2013 at 11:03 AM, Carsten Pedersen <carsten@stripped
> <mailto:carsten@stripped>> wrote:
>
>     Again: Unless you can give some idea as to the kind of lookups you
>     will be performing (which fields? Temporal values? etc.), it is
>     impossible to give advice on the table structure. I wouldn't blame
>     anyone for not being able to do so; saving data for debugging will
>     always be a moving target and almost by definition you don't know
>     today what you'll be looking for tomorrow.
>
>     That's why I think that using CSV tables _the contents of which can
>     subsequently be analyzed using any of a number of text file
>     processing tools_ may indeed be your best initial option.
>
>     On UUIDs vs. INTs: (1) Please do yourself a favor and read up on how
>     UUIDs are generated. If it's the same server that generates all the
>     UUIDs, you won't get a lot of uniqueness for the amount of space
>     you'll be using for your data and index; (2) Please do the math of
>     just how many inserts you can do per second over the next 1.000
>     years if you use a longint auto-increment field for your PK.
>
>     / Carsten
>
>     On 31-05-2013 11 <tel:31-05-2013%2011>:14, Neil Tompkins wrote:
>
>         Thanks for your response.  We expect to use the Audit log when
>         looking into
>         exceptions and/or any need to debug table updates.  I don't
>         think a CSV
>         table would be sufficient as we are wanting to use a interface
>         to query
>         this data at least on a daily basis if not weekly.
>
>         I use UUID because we have currently 54 tables, of which
>         probably 30 will
>         be audited.  So a INT PK wouldn't work because of the number of
>         updates we
>         are applying.
>
>
>         On Fri, May 31, 2013 at 9:58 AM, Carsten Pedersen
>         <carsten@stripped <mailto:carsten@stripped>>wrote:
>
>             On 30-05-2013 09:27, Neil Tompkins wrote:
>
>                 Hi,
>
>                 I've created a Audit table which tracks any changed
>                 fields for multiple
>                 tables.  In my Audit table I'm using a UUID for the
>                 primary key.  However
>                 I
>                 need to have a reference back to the primary key(s) of
>                 the table audited.
>
>                 At the moment I've a VARCHAR field which stores primary
>                 keys like
>
>                 1
>                 1|2013-05-29
>                 2|2013-05-29
>                 2
>                 3
>                 1|2|2
>                 etc
>
>                 Is this the best approach, or should I have a individual
>                 field in the
>                 audit
>                 table for all primary keys.  At the moment I think the
>                 max number of
>                 primary keys on any given table is 3
>
>                 Thanks
>                 Neil
>
>
>             First you need to ask yourself how you expect to use the
>             table in the
>             future. Will you be looking up the data on a regular basis?
>             Or will lookups
>             only be something you will do in exceptional situtions?
>
>             What is the intended goal of having a UUID for the primary
>             key rather
>             than, say, an integer - or having no PK at all?
>
>             My immediate thought when reading this was "why even store
>             that data in a
>             table?" - if it's a simple log, use a log file. Especially
>             if you don't
>             know how you intend to search for data later on. There are
>             many tools that
>             are far superior to SQL when it comes to searching for text
>             strings. You
>             could even consider having a CSV table, which will give you
>             an SQL
>             interface to said text file.
>
>             / Carsten
>
>
>
>             --
>             MySQL General Mailing List
>             For list archives: http://lists.mysql.com/mysql
>             To unsubscribe: http://lists.mysql.com/mysql
>
>
>
>
>     --
>     MySQL General Mailing List
>     For list archives: http://lists.mysql.com/mysql
>     To unsubscribe: http://lists.mysql.com/mysql
>
>
Thread
Audit Table storage for Primary Key(s)Neil Tompkins30 May
  • Fwd: Audit Table storage for Primary Key(s)Neil Tompkins31 May
    • RE: Audit Table storage for Primary Key(s)Jason Trebilcock31 May
  • Re: Audit Table storage for Primary Key(s)Carsten Pedersen31 May
    • Re: Audit Table storage for Primary Key(s)Neil Tompkins31 May
      • Re: Audit Table storage for Primary Key(s)Carsten Pedersen31 May
        • Re: Audit Table storage for Primary Key(s)Neil Tompkins31 May
          • Re: Audit Table storage for Primary Key(s)Carsten Pedersen31 May
            • Re: Audit Table storage for Primary Key(s)Andrew Moore31 May
            • RE: Audit Table storage for Primary Key(s)Rick James31 May
              • Re: Audit Table storage for Primary Key(s)Andrew Moore31 May