one table for all blob with a one to one table for metadata which link to one or many tables? I'm going to start by saying that I'm not a DBA but i got that role in my current project so I'm doing my best.


The application must be able to store files(attachments, official
documents, etc)
The application is using entity framework 6 (EF6) to query the
database.
Sql server that I'm using (2012) doesn't have the
FileStream/FileTables functionality activated and I have to assume
it will never be activated.
I don't have access to a fileserver.


I'm not the only one that could change the DAL in the application and I know that if you are not careful with how you write the query with EF6, it's by default select * from so while doing the database I made the choice, to be safe, of creating one table for all blob with a identity as primary key and a one to one mapping to another table which contain the metadata (filename, uploaded time, uploaded by, etc).

A file can have relation to many others tables(dataset) and a dataset can have multiple files.

I see two way of design, I'm showing 2 datasets here(notice and noticemaster) but I could have more;

Option 1

Option 2


My question is; which one is a proper implementation? is there an option 3?

Bonus question; did i made a good choice to have 1 table for all files and 1 table for all metadata?
Either of these models look reasonable. The NoticeMasterFile intersection table is not strictly needed since you can get the information through either model. The question is: How do you intend to use the NoticeMasterFile information? Perhaps this change would support some future plans that you have.

Of course, the problem is that looking at a data model does not really reveal the thinking behind the model.

Nonetheless, separating the blob seems like a very good idea to me. If nothing else it protects you when someone runs SELECT * on the FileMeta table.

As an aside and for what it is worth, if you use sp_tableoption to set 'large value types out of row'=1 the blobs will leave a 16-byte key that references the blob pages.  The blob will still be considered part of the table. The separation would allow you to query the other columns of FileMeta without the processing overhead of the blob data.  (This is the default setting.)

Questions: How many documents do you expect to accumulate in your database? How large is the average size? Et cetera?

If your document load will be relatively light, then this should be fine. But remember that every document is included in the backups that you take.

If you think it likely that you will eventually move into the Terabyte size range, you should do some further planning for how to store the documents. 

Perhaps:


Consider creating a series of filegroups for the documents as the data grows. At the proper time, set the older filegroups to read only. This protects the data and gives you the option of backing them up less frequently.
Ask your business to reconsider the value of storing documents in the file system.


For what it is worth, we have done it both ways over the years and both ways have their issues. But more systems that were using embedded documents are now using a file share instead.