January 26, 2012 at 9:43 am
Hello,
I'm wondering if there is any best practice or established convention about dealing with the following:
I'm designing a job that cleans up log tables by deleting rows older than a certain date. I am working with multiple log tables, so I would like to log which table was processed. But now that I'm designing the log cleanup log table (I know, this can get a little convoluted), I don't know whether to:
1. Find the table's object ID in the SQL Server metadata and store it as an int.
2. Create my own table lookup table and use that as a foreign key.
3. Store just the name of each table and make a conscious decision to avoid normalization of the log table.
#1 seems like the best choice if only by default, given that #2 and #3 seem to violate basic db design principles (#2 seems redundant and #3 subject to update anomalies). But on the other hand I wonder if normalizing is overkill for a log table like this. The job will run once per day so would have only around 1500 rows per year added to it. (4 logging tables in question, so 365*4=1460).
Does anyone have ideas or recommendations on the accepted way to handle this type of logging?
Thanks in advance for any help or advice anyone can provide.
- webrunner
-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html
January 26, 2012 at 11:31 am
webrunner (1/26/2012)
Hello,I'm wondering if there is any best practice or established convention about dealing with the following:
I'm designing a job that cleans up log tables by deleting rows older than a certain date. I am working with multiple log tables, so I would like to log which table was processed. But now that I'm designing the log cleanup log table (I know, this can get a little convoluted), I don't know whether to:
1. Find the table's object ID in the SQL Server metadata and store it as an int.
No. You can't trust this value in different environments. IE: Move the table to a warehouse and your OIDs don't match.
2. Create my own table lookup table and use that as a foreign key.
Sounds like overkill to me, and annoying to maintain.
3. Store just the name of each table and make a conscious decision to avoid normalization of the log table.
This would be my personal preference. I like my log tables to be as self-contained as possible though. It's also not denormalized, at least as far as 3rd normal form is concerned. The TableName is part of the key that controls the table and is atomic. Extra surrogate keys just make for less user-entry mistakes, not likely in this case.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
January 26, 2012 at 2:56 pm
Evil Kraig F (1/26/2012)
webrunner (1/26/2012)
Hello,I'm wondering if there is any best practice or established convention about dealing with the following:
I'm designing a job that cleans up log tables by deleting rows older than a certain date. I am working with multiple log tables, so I would like to log which table was processed. But now that I'm designing the log cleanup log table (I know, this can get a little convoluted), I don't know whether to:
1. Find the table's object ID in the SQL Server metadata and store it as an int.
No. You can't trust this value in different environments. IE: Move the table to a warehouse and your OIDs don't match.
2. Create my own table lookup table and use that as a foreign key.
Sounds like overkill to me, and annoying to maintain.
3. Store just the name of each table and make a conscious decision to avoid normalization of the log table.
This would be my personal preference. I like my log tables to be as self-contained as possible though. It's also not denormalized, at least as far as 3rd normal form is concerned. The TableName is part of the key that controls the table and is atomic. Extra surrogate keys just make for less user-entry mistakes, not likely in this case.
This is great, and exactly what I wanted to know. Thanks for your guidance.
- webrunner
-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply