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.
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.
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?
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.
If it's not practically useful, then it's practically useless.
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.