Enterprise Library 2.0 Logging...

[cdigs]

Something is confusing me about this block (and since I have this forum open, I'll post away :D ):

Why are the categories put into a seperate table??? I'm not quite sure I understand the reasoning behind this design. What purpose could it possible serve to have logging categories broken out into a seperate table and then a relation table mapping log entries to categories? Doesn't this seem terribly inefficient for the purposes of logging with little benefit at all (if any)?

Furthermore, looking at the SQL statement for inserting a new Category, I found this:

CREATE PROCEDURE [dbo].[AddCategory]
-- Add the parameters for the function here
@CategoryName nvarchar(64),
@LogID int
AS
BEGIN
SET NOCOUNT ON;
DECLARE @CatID INT
SELECT @CatID = CategoryID FROM Category WHERE CategoryName = @CategoryName
IF @CatID IS NULL
BEGIN
INSERT INTO Category (CategoryName) VALUES(@CategoryName)
SELECT @CatID = @@IDENTITY
END

EXEC InsertCategoryLog @CatID, @LogID

RETURN @CatID
END
That's right; you can only insert a category name once:
SELECT @CatID = CategoryID FROM Category WHERE CategoryName = @CategoryName
IF @CatID IS NULL
BEGIN
INSERT INTO...
END

So is it just me, or is this totally puzzling? Were they trying to save space in the row by removing the category string??? If so, that is just really weird database design from some smart guys.

Am I totally missing something?

[Bruno Baia]

Hi Charles,

It tests if the category name is already in base, in this case, it returns the category id of the existing category name, otherwise, it creates a category name and returns the associated id.


nice blog btw ;)
-Bruno

[cdigs]

Bruno,

That much is clear.

But the real question is why?

From a database design perspective, if you look at the SQL in profiler, it's two distinct client calls. Why not make it one call to the WriteLog SQL and simply pass the category in that call? On top of that, just to write an entry into the log requires a minimum of 14 reads. I don't quite understand why they designed it like this.

Why bother putting the category values into a seperate table and force that to be a seperate write?