Relational database: indirect reference to a "foreign key"

Question

I have a data schema similar to the following:

USERS:

id
name
email
phone number
...

PHOTOS:

id
width
height
filepath
...

I have an auditing table for any changes to the system

LOGS:

id
acting_user
date
record_type (enum: "users", "photos", "...")
record_id
record_field
new_value

Is there a name for this setup where an enum in one of the fields refers to the name of one of the other table? And effectively, the record_type and record_id together are a foreign key to the record in the other table? Is this an anti-pattern? (Note: new_value, and all the thing we would be logging are the same data type, strings).

If you look at an auditing product like Hibernate Envers, it uses history table per table (contacts_history for contacts) and does not use foreign keys. — Neil McGuigan, Aug 10 '14 at 21:04

score 1 · Answer 1 · edited May 23 '17 at 12:29

Is this an anti-pattern?

Yes. Any pattern that makes you enforce referential integrity manually¹ is an anti-pattern.

Here is why using FOREIGN KEYs is so important and here is what to do in cases like yours.

Is there a name for this setup where an enum in one of the fields refers to the name of one of the other table?

There is no standard term that I know of, but I heard people calling it "generic" or "polymorphic" FKs.

¹ As opposed to FOREIGN KEYs built-into the DBMS.

score 0 · Answer 2 · answered Aug 11 '14 at 15:54

Actually, I think 'Anti-Pattern' is a pretty good name for this set up, but it can be a realistic way to go - especially in this example.

I'll add a similar example with a new table which records LIKES of users' photos, etc, and show why it's bad. Then I'll explain why it might not ne too bad for your LOGS example.

The LIKES table is:

Id
LikedByUserId
RecordType ("users", "photos", "...")
RecordId

This is pretty much the same as the LOGS table. The problem with this is that you cannot make RecordId a foreign key to the USERS table as well as to the PHOTOS table as well as any other tables. If User 1234 is being liked, you couldn't insert it unless there was a PHOTO with ID 1234 and so on. For this reason, all RDBMS's that I know of will not let a Foreign Key be defined with multiple Primary keys - after all, Primary means 'only one' amongst other things.

So you'ld have to create the LIKES table with no relational integrity. This may not be a bad thinbg sometimes, but in this case I'd think I'd want an important table such as LIKES to have valid entries.

To do LIKES properly, I would create the table as:

Id
LikedByUserId (allow null)
PhotoId (allow null)
OtherThingId (allow null)

...and create the appropriate foreign keys. This will actually make queries that read the data easier to read and maintain and probably more efficient too.

However, for a table like LOGS which probably isn't central to the functionality of my system and I'm only doing some ad-hoc querying from to check what's been happening, then I might not want to put in the extra effort and add the complexity that results in more efficient reading. I'm not sure I would actually skip it, though. It is an anti-pattern but depending on usage it might be OK.

To emphasise the point, I would only do this if the system never queried the table; if the only people who look at the data are admin's running ad-hoc queries against it then it might be OK.

Cheers -

Relational database: indirect reference to a "foreign key"

2 Answers2