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 <cars...@bitbybit.dk>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: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 <cars...@bitbybit.dk >> >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 > >
