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
