Generic database logging system: Storing a reference to another database.table.column

Question

My question is, how can I link a reference stored in the Log table to a piece of data in another database?

We're building a system (Called Fusion) that will perform certain key tasks for all our other systems, one of these is a logging.

The idea is that any other system will be able to use Fusion to log specific operation.

CREATE TABLE [Log]
(
[LogID] [int] IDENTITY(1,1) NOT NULL,
[UserID] [int] NOT NULL,
[LoggedOn] [datetime] NOT NULL,
[ReferenceID] [int] NOT NULL,
[ReferenceLocation] [varchar](250) NOT NULL
)

So in the simplified table design above the ReferenceID column would store the foreign key from another database column. So the StoryID from a news database or the PersonID from a person database.

Then the ReferenceLocation would store the database.table.column location for the ReferenceID column.

The idea being that a SQL query could be written (using dynamic SQL or another method) so that the referenced data for each row can be returned when the Log table is queried.

Is this the way to do it? Is there a better way? Should we re-think the reasoning behind this endeavour in general?

Answer 1

Why not just use schema.tablename and rowid?

Answer 2

I would store database.schema.table in ReferenceLocation, and have another field for the primary key column names, or just use standard "ID", like in:.

CREATE PROCEDURE p_GetFromLog(@LogId int)
AS
BEGIN
    DECLARE 
     @exe nvarchar(1000)
     ,@RefID int
     ,@RefTbl varchar(200)

    SET @RefTbl = SELECT [ReferenceLocation] FROM dbo.[Log] WHERE [LogID] = @LogId
    SET @RefID = SELECT [ReferenceID] FROM dbo.[Log] WHERE [LogID] = @LogId

    SET @exe= N'select * from database.schema.table_here WHERE [ID] = refrence_id_here'
    SET @exe = replace(@exe, 'database.schema.table_here', @RefTbl)
    SET @exe = replace(@exe, 'refrence_id_here', cast(@RefID AS varchar(12)))
    EXEC sp_executesql @exe
END

Answer 3

There's a lot of "it depends" here. Some ideas:

• Add a column for Database ("DBName", since "database" is a reserved word). Useful if similarly named objects are in multiple databases (such as if you have to support one instance per customer).

• Add a column for object schema, if (again) there are similar objects stored across schemas. If you're a lazy slob (like I usually am) and everything's in dbo, don't bother.

• Add a column for application. If multiple things use the same object, it could be useful to know which one did it this time.

• Add a column for, err, column. Might you sometimes want to track data distinctly, and sometimes in aggregate?

• I'd guess this is all for activity on "this" SQL instance. I don't recommend logging activity on one SQL instance in another SQL instance, particularly if it's hosted on a different server.

• Will "UserID" be adequate? Will the relevant lookup (or login) table always be available? Might you get more mileage out of tracking login name?

The common thread in my ideas is normalization. I wouldn't lump too much data (such as DB, table, column) in one column, as--depending on what you want to get out of logging--that could make subsequent queries very awkward.