Hi,

I am facing the following challange: for a university project
I have created a database. One of it's tables shall store logging
data (for example when a app user has been created/edited or
some other object has been created).
The problem is, that I want to store a relation to the object which
actually caused the log entry. Consider the following example:

ID Date Event Object_Id
========================
1 1.3.08 User_Created 1 => Referencing the users table here
2 4.6.08 Product_Created 2 => Referencing prod. table here

So I want to reference multiple tables from one column.
How can I do this in the most efficient way? I am using SQL Server 2008 and LINQ to SQL.

Many thanks in advance.
Robert

You use the word relation - you mean relationship right?

You want a bucket to log all changes in all tables (or more than one table anyway) and you want to store the pk of the changed rows, and you want relational integrity? That dog won't hunt, Monsignor.

One thing you haven't covered is what you want to happen if a row is deleted - if you want a log of that too then not only can you not physically implement relational integrity, you aren't even conceptually working with relational integrity.

pootle flump ur codings are working excelent.

Not much to go on but here's an example that may help:

CREATE TABLE YourTable
(ID INT NOT NULL PRIMARY KEY,
Date DATE NOT NULL,
Object_Id INT NOT NULL REFERENCES Objects (Object_Id));

CREATE TABLE Users (Object_Id INT NOT NULL REFERENCES Objects (Object_Id), ...);

CREATE TABLE Products (Object_Id INT NOT NULL REFERENCES Objects (Object_Id), ...);

nice one, dportas, that's elegant and simple

looks like the dog hunts just fine

straightforward application of supertype/subtype concept

requires multiple LEFT OUTER JOINs to retrieve details, though

I misunderstoodulated the problem

Let's see if I understand these things right now: users and products should have check constraints to ensure the value of object_id can be one value only. So object_id is not surrogate. Do you guys like columns with one value and one value only in them? Should we call the tables tblObjects, tblProducts, tblUsers etc?

pootle flump ur codings are working excelent.

...I'm also not quite sure of the value of what is being recorded. Without a reference back to the pk of the created row I don't really see what you can do with the information.

pootle flump ur codings are working excelent.

......and.....clearly I am being slow today as it doesn't look sub\ super to Yours Truly nor do I see where outies are required.

To be fair it is beer o'clock in Blighty now and me noggin is feeling a bit dehydrated.

pootle flump ur codings are working excelent.

I wouldn't join based on Object_ID. That's likely to cause havoc if you need to drop and recreate the database or share data between instances, as there is no guarantee that the same object_id will be assigned from one install or restore to another.
Link on the object name instead if you insist on enforcing relational integrity with the physical database schema.

sigh

and because that's not 10 characters, i say again, "sigh"

bear with me on this, poots

if you wanted to have a supertype that encompasses both users and products, at least insofar as logging changes to both types was concerned, what would you call the supertype?

substitute your word for "object" in dportas' reply

the log entry refers to the [yourword] which it detected was changed

the [yourword] has a unique identifier, and it is either a user or product

user and product are subtypes of [yourword]

as for the LEFT OUTER JOINs, you'd want two of them, one to users and one to products, if you wanted specific details of what it was that was logged

Aw - poor Rudy having to bear with dumb ickle me

I'm offski for the weekend and can't elaborate now but no - I don't like the schema and I don't like the whole premise of the problem. I will throw out one thunkle to answer your question - object_id does not compose any part of any primary key other than in the object table. As such it is not sub\ super - it is a looky uppy.

I'm also in shock because I think blindman is advocating a natural key over a GUID (or any sort of surrogate). Obviously I have not yet drunk enough booze - that will be a transient problem.

pootle flump ur codings are working excelent.

Ha ha. I thought about that as I typing it, but I figured Rudy would call me on it first.
What's up, Rudy? Were you napping?

i have another part of my life where i'm AFK for several hours at a time

so i like it when people fill in for me

i just got back home, and as soon as i can slip into something more comfortable, ...

ooops, wrong thread