I have to relate "attachments" (images and various document types)
to a bunch
of different areas of work-flow.
IE: Product records can have attachments, so can inventory records. Same with SalesOrders and SalesOrderItems, PurchaseOrders and PurchaseOrderItems, ... etc.
All in all, there are 13 tables which need to have relationships defined.
Now, my question is whether it would be better to have a relationship table for each are in the work-flow, or have one big table with all
the relationship info?
My top concerns are performance and maintenance ... performance being the primary.
If I went with 13 relationship tables, that would be a bit more maintenance, however, those tables would be fairly lean and easy to maintain. I'm wondering whether a single table would be a (potential)
bottleneck if there were dozens of select queries being run against it at a time.
General pearls of wisdom or sage advice?