Table-agnostic Foreign Keys?

Question

So, a simple question. First, I did read this StackOverflow question, so no need to point me towards it.

I'm working on a similar problem right now. Specifically, I have a database with an Auditing table that is used to store auditing info about other tables within the db. The basic form of this table is:

ID, EntityID, EntityTypeID, ActionTypeID, DateTime

Now, as you can guess, the generic nature of EntityID means that foreign key relationships to and from this table are tricky to manage, especially with you through an ORM system into the mix.

Of course, the grunt-work solution is to do the required queries manually and use the ORM stuff where it works, which I'm fine with.

However, the issue did raise the question in my mind as to whether or not there exists any RDBMS out there that allows for a Foreign Key relationship of the form: Table:ID to be defined.

In other words, in such an RDBMS, the EntityTypeID column might contains values such as

'TableA:1' and 'TableB:somekey'

So...

Is there any RDBMS which does this?

Answer 1

You could look at the option of storing the audit in a separate database, perhaps an Object Oriented database like db4o. This might give you additional flexibility in terms of storage

Answer 2

Note: this is a comment, not an answer but seemed too long for a comment...

I've had the same question mulling around about a Tagging system. A Tag Table with fields: table, user, tag, created_date. It feels simple. But it breaks the ability to use foreign keys.

I think EAV's are bad for entire db's but for a single table, it seems like a valid choice. The question seems to be if your datalayer supports it. If you're using an ORM, you might be causing more headaches than it's worth.

Because of this, I'll probably just go with a tag table for each taggable table. But I'm curious to see what others think about this topic.

Answer 3

Audit tables usually cannot have referential integrity constraints on them. An audit table, A, records information about a row of data, R, in some table, T, and contains a series of records for R, each representing R at some a different time. When R is subsequently modified, the information in A does not change, and is not allowed to prevent changes to R. When R is subsequently deleted, the presence of the audit records in A is not allowed to stop that deletion.

Answer 4

I think no RDBMS are supporting similar feature. Relational structure is what you must provide for them first, and they normally don't help you to establish it.

On the other hand, I can describe a nice feature of DataObjects.Net that is quite related to this question: automatic registration of generic instances.

Imagine you have 3 persistent types:

[HierarchyRoot]
public class A : Entity 
{
  [Field, Key]
  long Id { get; set; }

  // ...
}

[HierarchyRoot]
public class B : Entity 
{
  [Field, Key]
  int Id { get; set; }

  // ...
}

// Note: it is a descendant of B
public class C : B
{
  // ...
}

And add one more persistent class:

[HierarchyRoot]
public class AuditData<T> : Entity
  where T: Entity
{
  [Field]
  [Association(OnTargetRemove = OnRemoveAction.None)] // This ensures 
  // FK won't be created
  T Source { get; set; }

  // ...
}

DataObjects.Net will automatically provide persistence for two instances of this type:

• AuditData(Of A)
• AuditData(Of B)

But it won't allow you to create AuditData(Of C), since there is AuditData(Of B). So it makes the decision of what to register based on generic type constraints. It's also shown it's easy to avoid creation of foreign key.

Answer 5

Btw, obviously, you can implement similar feature with just an RDBMS - by implementing some logic updating the structure of audit tables by the structure of all other tables. You have to:

1. Extract the structure of all the tables you're going to audit
2. Find the "hierarchy roots" between them: in fact, you must keep only the tables which primary key isn't marked as foreign key in another table.
3. Create (or restructure) the audit tables for each of hierarchy roots. Here you need a template with replaceable set of columns containing source table key.

So in general, this isn't an easy task. Even if you have a tool like SQL DOM is capable of extracting the schema and building its parts.