Should we use multiple tables, or one big one??

[mOrloff]

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.

Thoughts?
Experiences?
General pearls of wisdom or sage advice?

Thanks-a-bunch,
~ Mo

[Old Pedant]

Not even clear how you would design a single table.

For each image/doc would you have one row with many foreign keys into the various tables? With NULLs for the foreign keys that don't apply for this particular image/doc?

That would work, but I don't think it's considered good design.

Multiple use of read-only (or even read-mostly) tables in MySQL is never an issue. I don't see this kind of table ever causing a bottleneck. Since a single table would eat less memory (buffered tables, that is) than several small one, it would probably actually be *more* efficient.

Ehhh...good DB design or no, I might be tempted to go with it.

I think maybe maintenance should be the deciding factor.

[mOrloff]

Thanks.
I think I'm just going with the single-table route then

My thoughts on the table was just to have a single row for each attachment, and have a single column for all the FK's.
Then, I could go one of two ways.
- either have a TYPE column for a token code (ie: 'SO' for SalesOrders, 'SI' for SalesOrderItems, etc...)
or
- have a 13 boolean columns for each of the types (ie: isSalesOrder, isSalesOrderItem, etc...)

What thoughts do you have? Do you see any benefits on one idea over the others??
~ Mo

[Old Pedant]

How can you have "a single column for all the FKs" if a given attachment is used by more than one other table? Won't the FK values be different for each table?

Or are you saying that a given attachment can *NEVER* be applied to more than one other table??

If the latter is the case, then I'd go with a single "type" column.

But if it's ever possible for an attachment to be used by more than one other table, then I think you have it bass ackwards.

I think that your "attachment" table should have 13 foreign keys.

So now you know *WHICH* record in each of those 13 tables the attachment is related to!

[mOrloff]

Originally Posted by Old Pedant

... are you saying that a given attachment can *NEVER* be applied to more than one other table?? ...

Hmmmm ... definitely worth digging into.
Off the top of my head, I'd say "yeah, never", but I'll do a little more research.
Regardless of what the dept heads say, thanks to you, I now have a direction.

Thanks-a-bunch (again)
~ Mo