I have three entities: Document, Asset, and Client
A Document can be linked to several assets. An Asset can have many documents.
A Document can be linked to several clients. A Client can have many documents.
Is it better to have two join tables…
asset_document
asset_id
document_id
...more fields
client_document
client_id
document_id
...more fields (same fields as in asset_document)
Or just one join table?
document_relationships
document_id
object_id (could be a client_id or asset_id)
type (whether it's a client or asset)
...more fields
My guess is that the first one is better but I can't explain why. I'm looking for a strong argument against the second one. My co-developer prefers the second one to reduce duplication and lesser tables to maintain and lesser tables to join in queries.
Take note their could be more entities where Document will be linked to but ...more fields will stay the same.
Best Answer
You should definitely two separate tables!
As it is anyway with your documents_relationship table, you are most likely going to have fields that apply to clients and not assets and vice versa!
This will mean
NULLs. Good frule of thumb IMHO, a field should never be NULL by design for its entire life. Note: this does not mean thatNULLs should never be used!