2

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

gwt
  • 2,331
  • 4
  • 37
  • 59
  • Are you trying to store file data in database,if so i recommend you look at Filestream::https://learn.microsoft.com/en-us/sql/relational-databases/blob/filestream-sql-server – TheGameiswar Sep 16 '17 at 18:43
  • @TheGameiswar I want to allow users to configure whether the files will be saved in database or as file system. So yes files might be saved in database. – gwt Sep 16 '17 at 18:53
  • Ok,any reason for doing this `I want to allow users to configure whether the files will be saved in database or as file system` – TheGameiswar Sep 16 '17 at 18:54
  • @TheGameiswar In my perspective not really! I just want to consider the user wishes ... Some users and database administrators might prefere to save files in database and some others might prefere saving them as system files. My concern is just to reach to a flexible and high performance design – gwt Sep 16 '17 at 18:57
  • IMHO users shouldn't be the ones deciding where & how their data & files should be stored by the application. They'll usually not understand the options and make the wrong choice. That said, SQL Server isn't a file server, so the cases where storing files in it is a good solution are limited. – alroc Sep 17 '17 at 16:08
  • @alroc actually I am developing a software development framework and by users I meant software developers who will decide whether to store data in database or file system in each project they do. My main question here is in case when they decide to save files in database does it make any difference to store whole files in one single table rather than saving them in several different tables for each database table ... – gwt Sep 17 '17 at 16:48

1 Answers1

1

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.

usr
  • 168,620
  • 35
  • 240
  • 369