I have three entities:
Document can be linked to several assets. An
Asset can have many documents.
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.