What is the best practice to encode this relationship in a postgres database schema?

Given the following entities: Document, Profile, Message

And the following relationships:

One Profile to many Documents

One Message to many Documents

Essentially documents are a shared resource used by other entities. Should I use different Join Tables for each related entity? Is there some other way to set this up? It is possible in the future that other entities will reference Documents

CodePudding user response：

For anyone who finds this post and wants an actual answer: The foreign key for the relation HAS to be on the many-to-one side of the relationship or there has to be a join table mapping the relation. You cannot store multiple foreign keys in a single column on the one-to-many side of the relationship.

CodePudding user response：

A one-to-many relationship is modeled with a foreign key, in this case in the documents table. If you need two such relationships, define two foreign keys.