In designing both audit tables and notification tables, I've run into an issue where I need a row to refer to an entity in another table, but which table that entity is isn't always the same. For example:
- Each time a user makes a post or uploads a new photo to their album, a notification is created. If the notification is for a post, the notification entry must refer to the
posts
table. If it is for a photo, the notification entry must refer to thephotos
table. - Moderators may edit posts, delete posts, or block users. The audit_actions entry must refer to the entity that was acted upon in the corresponding table.
My original approach looked like this (this is heavily simplified, not the actual table structure):
TABLE notification_types:
id INT
type_id INT
entity_id INT
- If
type_id
was 1,entity_id
would refer to an entry in theposts
table - If
type_id
was 2,entity_id
would refer to an entry in thephotos
table - etc
I was starting to use a similar scheme for audit tables. My plan was to make an audit_history table for each auditable table that mirrored the original table (e.g. posts
and post_audit_history
), and maintain a single audit_actions
table that linked an actor and action to each history item
TABLE audit_actions
admin_id INT
action_id INT
entity_id INT
audit_entry_id INT
If action_id was 1 ("edited post"), entity_id would refer to an entry in posts
, audit_entry_id
would refer to an entry in posts_audit_history
, etc
Of course, this approach is terrible. entity_id
can refer to multiple tables, which is confusing and prevents it from being a foreign key. There is no way to enforce integrity without complicated triggers.
I've been trying researching and trying to find solutions but I am stuck. Some approaches I've considered:
- Maintain an
entity_ids
table. Each entity in every table then has a foreign key to an entry in theentity_ids
table; in other words, every single entity in any table has a globally unique ID. This maintains integrity, but when we looked up an item by its entity_id we'd have to search multiple tables to find the entity with that ID - Maintain separate notification tables for each type of notification, and separate audit tables for each type of auditable table. E.g.
post_notifications
,photo_notifications
,post_audit_actions
,profile_audit_actions
, etc. This would require running messy and expensive joins for what were formerly simple queries like 'find all notifications for user X' or 'get all audit records for user Y' or 'get the 100 most recent audit records'
Which approach is best? Is there a better strategy that I haven't thought of here?