SQL: saving a MIME type or extension?

Question

What is the best way to store BLOB information in a database? File extension (.txt, .rar) or MIME type?

Also, which is better: save the file name with or without the extension ("file" or ".txt file")?

First of all, I'm talking about the Desktop-App, not the web application.

Answer 1

If we are talking about downloading files, for example, I will always store the following fields:

• File - varbinary (MAX)
• FileName - nvarchar (255) (including file extension, for example "myfile.txt")
• FileType - nvarchar (255) (MIME type)

The MIME type is important if it is a web application and you want to allow file downloads at some point. Having a MIME type allows you to tell the browser how to best process the file.

So the direct answer to your question is to save the MIME type and extension. The reason is that you cannot provide the correct file extension, so you need a MIME type to identify the file type. But you must save the extension with the file name so that you can specify a valid file name at boot time.