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?