Unknown Db file format – best way to find out

I am not very familiar with your needs, but perhaps storing each data point in the database is a bit of overkill. It sound almost like taking the approach of storing an image library by storing each pixel as a separate record in a relational database.

As a general rule, storing binary data in databases is wrong most of the time. There is usually a better way of solving the problem. While it is not inherently wrong to store binary data in relational database, often times the disadvantages outweigh the gains. Relational databases, as the name alludes to, are best suited for storing relational data. Binary data is not relational. It adds size (often significantly) to databases, can hurt performance, and may lead to questions about maintaining billion-record MySQL instances. The good news is that there are databases especially well suited for storing binary data. One of them, while not always readily apparent, is your file system! Simply come up with a directory and file naming structure for your binary files, store those in your MySQL DB together with any other data which may yield value through querying.

Another approach would be using a document-based storage system for your datapoints (and perhaps spectra) data, and using MySQL for the runs (or perhaps putting the runs into the same DB as the others).

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:

1. 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.
2. 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.