Database design: use a single “Author” associative entity related to different entities

Let’s say my application deals with multiple entities (Articles, Files, Images, Documents, …).

I also have multiple Users (another entity).

Every entity must have one or more “Author” User(s) associated to it.

Build a ManyToMany relation between each entity and the User table.

  • Articles <–> Articles_Users <–> Users
  • Files <–> Files_Users <–> Users

This is a “classic” approach. The main advantage for me (a HUGE one!) is that you can enforce foreign key and be sure, database-level, that the relation is sound.

The main con is that this requires a different table for each new entity type. This plain sucks on oh-so-many-levels.

The second design is to build just a single associative entity with and additional field to map each record to its correct entity:

| entity_id | user_id | entity_type

The main pro here is that you don’t need a new associative entity for every relation. This is a HUGE pro for me and I like this design much better.

The main con is that you cannot manage foreign keys checks at the database level. Yes, I can do it at the application level, but it’s not as solid.

So… which one should I go for? Would the second one be a good practice or would it be consider a bad design?