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

Question

My application deals with multiple entities (Articles, Files, Images, Documents, ...) and multiple Users (another entity). Every entity must have one or more "Author" User(s) associated to it.

First design

Build a many-to-many relation between each entity table and the User table.

Articles <--> Articles_Users <--> Users
Files    <-->   Files_Users  <--> Users
...      <-->    ..._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.

Second design

Build just a single associative entity with an 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.

What to do?

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

Answer 1

According to your narrative, you have abstract Contents, which are potentially very different and therefore need be specialised into concrete Articles, Files, etc.... In an OO perspective, the classes would look like:

It appears that you want to implement an association between Content and User:

• The first design uses concrete table inheritance: there is no table for Content, but there are tables for each of its concrete specializations. Since there is no Content, you need to duplicate the association with the abstract class with each of its concrete specialization.

  

• The second design tries to manage the association as if there was an abstract Content. But there is no table for it, and therfore, you need to to the consistency check in your code.

• A third design would use class table inheritance: there would be a table for the abstract Contents, with the only common property id. And you'd have tables for each derived class, with only the missing properties (in your case all, since an id would anyway be needed). The derived tables would each be relationally linked with the abstract content (whether you use the same id or different ids does not matter: you can add a foreign key constraint). And the table for the association table would lokk very similar as today, with a FK constraint to Content which can very well be enforced at DB level.

This last design still requires some thoughts (one content id reused for the specialization? two independent ids? how to avoid duplicate use of the content id, etcc.)but it could look somewhat like: