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?

• Doc Brown: None of these designs are generally "good" or "bad". Both have their pros and cons. One additional restriction of the second approach is that you need uniform IDs in all entities. And foreign key checks at the database level can be implemented using stored procedures in some databases (but it is not trivial to get them right).
• Doc Brown: ... and to your issue with "different table for each new entity type" - if you add an entity, you already going to extend the DB schema, because you need a new table for the entity itself. Adding a second table then for the (few?) cases where entities requie a group of authors does probably not sound that much additional effort from that perspective, I guess.
• Dr. Gianluigi Zane Zanettini: "One additional restriction of the second approach is that you need uniform IDs in all entities." no, you don't: the entity_type field allows you to use whatever IDs you want in each entity. Thanks for stored procedure idea, sounds great.
• Doc Brown: Ok, entity_type can be a string or VARCHAR field, which might be suitable for most of kind if IDs, but at least you should not have composite primary keys. In reality, I am pretty sure you can avoid some quirks by choosing the same type for all of those IDs.
• JacquesB: @Dr.GianluigiZaneZanettini: The first one seem the most sensible to me, the second one looks like a hack. Why is it a big problem that the first solution requires an extra table per entity?

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:

enter image description here

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.

  enter image description here

• 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:

enter image description here

• Dr. Gianluigi Zane Zanettini: Whoa, thanks man for your great illustraton! Yes, that sums up the available options greatly.
• Christophe: @Dr.GianluigiZaneZanettini Thank you very much for your nice feedback! If this answer helped you, don’t hesitate to accept it ;-)