Best way to define related resources db model
This is definitely a workable solution. The one thing that this prevents is the use of built-in referential integrity options that your DBMS may provide.

As an example, resource_id and related_id cannot be foreign keys to another table - because both could reference multiple other tables.

So, maintenance of the relationship between a "document" resource_id and the document table must be handled in your code. You need to be sure that (for example) when you delete a document row, you remove rows from your resource_link table with that "document" resource_id or related_id.

In general, it's easiest to allow the database to maintain such information appropriately by itself. It's much less likely that you'll inadvertently allow a document row to be deleted without cleaning up connections elsewhere.

To avoid this, you'd need a solution where either:


you have 2 bridge tables for each possible pair of resource types (document_media, document_event, media_document, media_event, event_document, event_media, etc.; possibly even document_document, media_media and event_event); or
each resource type would need a bridge table with a column for each possible related resource (e.g., document_bridge would have the columns document_id, media_id, event_id, related_document_id, etc.), with a constraint to ensure one and only one of the related IDs is set.


Each of your options has its pros and cons. If you're confident that you'll be able to enforce data referential integrity "manually", your solution may be the cleanest, and the easiest for another person to follow. If the available resource type are expected to grow dramatically, then the other solutions (both of which would require adding or modifying multiple tables) will become more and more awkward to work with over time.

On the other hand, if the resource types are expected to be static, and there aren't more than a couple more, using one of the other solutions would ensure referential integrity (if properly set up with foreign keys). Also note that, with either of the other two solutions, it would be possible to create a view matching your proposed single table; if simply having a single place to get a simple list of what's related to something is considered a plus, you can have that with any option.

The queries to retrieve all related resources will be a little messy regardless of which option you take.

In terms of performance, the more indirection you have, the more work the DB engine will have to do - technically, at least. In practice, I suspect the performance difference between what you propose and (say) the "resource X resource" version (specific tables for each possible relationship, for both possible directions) would be small enough to be ignorable.

So, I don't think there is a single "best" way to do this. Ultimately, the comfort level of the development and support teams with the solution is likely to be the most critical determining factor. If your development team has experience with manually maintaining data referential integrity, your solution is perfectly reasonable, and might well be the easiest for someone else to follow.