Re: Database theory question

On Apr 17, 2008, at 2:43 PM, Mag Gam wrote:
> Hi All,
>
> While reading this article, History tables and event logging --
> http://www.ibm.com/developerworks/web/library/wa-dbdsgn2.html, I
> realized I try to do event logging in SQL.
>
> My question are: Is SQL a good tool for event logging? Does anyone
> have a sample table sctucture for the most optimal way of event
> logging? Current I have 2 tables. 1 table with timestamps, another
> with event. Can anyone recommend a better way?


Hrm... I'm on a plane so I can't look at the article right now, but a
separate table for timestamps doesn't sound so useful. If you had a
*lot* of events for each timestamp, maybe... since timestamps take 8
bytes you could possibly save space by referencing them with an int
instead; you would save 4 bytes per event. But you'd use at least 24
extra bytes to store the timestamp in a separate table, depending on
Postgres version and CPU architecture.

As for "does it make sense", that depends both on the data that
you're storing and how you're using it. From a space standpoint,
you'll be hard-pressed to beat a text logfile and gzip/bzip2. Even if
you need to search the data, grep can often suffice. On the other
hand, if you're doing a lot of searching or other processing, or if
you have a lot of numeric values that you can store in either int or
float4, a database makes more sense.
--
Decibel!, aka Jim C. Nasby, Database Architect  decibel@decibel.org
Give your computer some brain candy! www.distributed.net Team #1828