SQL: Saving MIME-Type or Extension?

Question

What's the best way to store information about a BLOB in the DB? The File-Extension (.txt, .rar) or the MIME-Type?

Also, what's better: Store the Filename with or without extension ("file" or "file.txt")?

What I'm primarily talking about is a Desktop-App, not a Web-Application.

Answer 1

If we are talking about file upload storage 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) (the MIME type)

The MIME type is important if it is a web based application and you want to allow download of the files at some point. Having the MIME type allow you to tell the browser how to best handle the file.

So the direct answer to your question is to save both the MIME type and the Extension. The reason being that you cannot ensure the correct file extension has been supplied so you need the MIME type to identify the file type. But you should store the extension with the filename so as you can provide a valid filename at download time.