CODEWITHSUNDEEP
mysql
Tony
Tony

Reputation: 12705

MySQL: Many tables with the FK to one table - yes or no?

I'm about to extend my DB schema (in MySQL) of the another 50+ tables. About 15-20 of them will be referencing to the table File (wchich stores data of the created or uploaded files in my project) The question is, what proceeding should I use:

I'm asking because, if I choose the 1st option, at some point that one File will have the big amount of rows which it will make the CRUD operations (with the where clausule) slow (I suppose). So, what do You think, what would be the best idea ?

Upvotes: 0

Views: 36

Answers (1)

Michael Berkowski
Michael Berkowski

Reputation: 270687

Keep one central File table which is referenced by all others, unless there are to be significantly different attributes for different types of rows stored in File. In other words, if you are storing different sets of file metadata in the File table for different association types, you could find it necessary to have different file storage tables for different metadata types.

But, if you are storing the same set of file binary data, filename, MIME type, etc, commonly for all associated types, it belongs in one table.

If the table is expected to grow very large, consider partitioning its storage. With proper indexing, CRUD operations can be performant over millions of rows.

Upvotes: 1

Related Questions