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
2
Answers
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.
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.