I want to create a generic mechanism for saving files in my application and database and to do that I have come up with the idea of creating two tables with following schema in order to save files related to any row in any database table:
FileInfo
=================================================================
ID FileName ContentType FileSize DatabaseTableName RowID
and creating the following table with a OneToOne relationship to save file data in a seperate table so that querying the FileInfo table could be performed faster:
FileData
=================================================================
ID FileData
Well I am not an expert in terms of database performance and that's why I would like to know whether such a design that is going to save all files for all tables in one single table will lead to performance issues and is it a bad practice?
And if it will, could you provide me with a better solution?
Thanks in advance
I feel the question cannot be answered without an essay. Basically it is OK to store files in the database. Database and filesystem have vastly different properties. It is commendable that you want to give users of your framework the option to pick the right choice for their case.
Splitting this into many tables (manual partitioning) or any other form of partitioning will not help. SQL Server has no inherent problems dealing with extremely large tables.
Blobs in the database cause some specific drawbacks. It does not matter much where those blobs live.
I like the split into two tables as you made it. Normally, this is not necessary. If queries are properly written and are only pulling the columns that are needed then SQL Server will not touch unused blob columns at all.
That said it is often convenient to split off the large blobs as you did. ORMs do not like huge rows. Tools (and admins running a simple manual select *) are now able to look into the FileInfo table without failing because of the large data.
The split is not necessary but can make working with the database easier.