Multiple Table Joins to Improve Performance?
- by EdenMachine
If I have a table structure like this:
Transaction [TransID, ...]
Document [DocID, TransID, ...]
Signer [SignerID, ...]
Signature [SigID, DocID, SignerID, ...]
And the business logic is like this:
Transactions can have multiple documents
Documents can have multiple signatures
And the same signer can
have multiple signatures in
multiple documents within the
same transaction
So, now to my actual question:
If I wanted to find all the documents in a particular transaction, would it be better, performance-wise, if I also stored the TransID and the DocID in the Signer table as well so I have smaller joins. Otherwise, I'd have to join through the Signature Document Transaction Documents to get all the documents in the transaction for that signer.
I think it's really messy to have that many relationships in the Signer table though and it doesn't seem "correct" to do it that way (also seems like an update nightmare) but I can see that it might be better performance for direct joins. Thoughts?
TIA!
