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 <cars...@bitbybit.dk
<mailto: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 <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
<cars...@bitbybit.dk <mailto: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
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql
