1

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 the photos 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 the posts table
  • If type_id was 2, entity_id would refer to an entry in the photos 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 the entity_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?

Improve this question
2
  • 1
    You could have a single audit table with columns like post_id, photo_id, ... which are foreigh keys but nullable. Of course, then you need to have as many columns as you have entities which you want to audit, which may or may not be acceptable, depending on your use case. Commented May 29, 2015 at 6:15
  • Thanks for the suggestion, @zgguy, I suppose we'll have to go with that unless someone has a better suggestion. Commented Jun 1, 2015 at 23:08

1 Answer 1

Reset to default
1

One idea comes from extending the analogy of interfaces in object-oriented programming. You can have an IGeneratesNotification "interface" entity that the Posts and Photos entities "implement", as well as an IAuditable "interface" entity that the Posts and Users entities "implement". It would be possible for an entity to implement multiple interface entities, such as in the case of Posts. Here's a diagram of how this can be done.

enter image description here

Note that the fields containing references to the interface entities would be unique.

Advantages:

  • You can enforce referential integrity to the interface entity that can be implemented by one of several entities.
  • If there are fields common to all implementing entities, they can be abstracted out to the interface entity.

Disadvantages:

  • It is possible for the id of an interface entity to be present in 0, or more than one, implementing entities.
  • The service layer would be required to determine which entity the notification or audit action applies to before knowing which table to query.
Improve this answer

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.