Where should linking tables be stored? [closed]

Question

I have a system where properties can be stored, and linked with multiple other entities (each with their own schema). Let's say that Documents can be linked with Users and Assets. What is the best practice format for linking these things together?

Here are some example tables that exist (each of the schemas containing these core tables has many more tables related):

Users.Users:
    UserId PK
    Username
    Name
    Email

Assets.Assets:
    AssetId PK
    Name
    Description
    Value

Documents.Documents:
    DocumentId PK
    Name
    Version

Here are the options I am considering for how to link the documents to the other entities:

1. Store a linking table within the Documents schema. This has the advantage of a single place to look for all document links, but requires the Documents schema to refer back to other schemas, when the linking direction feels like it should be the other way.

   • (1A) With a table that has a column for each entity that can link to a document:

   Documents.DocumentLinks
       DocumentId FK NOT NULL
       UserId FK NULL
       AssetId FK NULL
       -- Future columns as needed
   

   • (1B) With a table that maps link types and IDs (an extra advantage here is that the schema never needs to change, but loses foreign key integrity):

   Documents.DocumentLinkTypes
       DocumentLinkTypeId PK
       Name
       -- Sample data: (1, 'User'), (2, 'Asset')
   
   Documents.DocumentLinks
       DocumentId FK
       DocumentLinkTypeId FK
       LinkedItemId --Cannot be FK
   

2. Each table that links to documents stores its own linking table. This has the advantage of matching the direction in which we think about the data, but has the disadvantage of requiring a new table to be added whenever a new property can have documents linked to it (which could grow to be many places).

   Users.UserDocuments
       UserId FK
       DocumentId FK
   
   Assets.AssetDocuments
       AssetId FK
       DocumentId FK
   

3. Any other options out there?

I can imagine that the choice comes down to what is most likely to happen - option 2 feel like the most logical to me in terms of data order, but if new links to documents are added regularly then option 1B is better. 1A seems like the "least technically good" option, but also is quick and easy to understand. I'm wondering if commenters have previous experience showing that one option is clearly not cut out, or if there is indeed a well-known best practice for this problem.

Answer 1

The multiple schema thing seems weird unless you have a lot of other stuff in the the schemas related to users/documents/assets. As far as linking tables in SQL the standard hasn't really changed in forever.

• If the relation is one to many, add a FK to the many side.
• If the relation is many to many, add a table with FKs to both tables
• If the relation is one to one, pick a side to have a FK and add a unique constraint as well

The tricky part for you would be deciding what schema to add a join table to for many to many relationships since you seem to have too granular of a schema.