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?