List:General Discussion« Previous MessageNext Message »
From:Carsten Pedersen Date:May 31 2013 10:03am
Subject:Re: Audit Table storage for Primary Key(s)
View as plain text  
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: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>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
>>
>>
>
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