Storing metadata of various data types in a MySQL database

Question

I am currently designing a MySQL database that will store a variety of documents in different digital formats (mostly docs, spreadsheets, and pdfs). Each document will have several metadata values associated with it (a receipt might have metadata fields for date, store, and price, while a recipe might have metadata entries for various types of ingredients, serving size, and meal type). In order to be clear, the metadata I am storing is not about the table or database itself but is information about each type of document being stored and will have a different value associated with the metadata type for each document. I don't want to create a new table for each type of metadata being stored, but rather allow the users of the database to create additional metadata fields for each type of document as needed.

The problem I'm running into is the metadata's data type will be different depending on what the field is (for example, a business name field would be a varchar, while a dollar amount field would be a decimal type). I want to be able to search based on this data (all receipts with a date issued value before January 2nd for example), so I can't just store everything as a varchar. Currently I only see two ways to design this database:

1. Create a separate table for storing each data type (ex. a text_metadata table, decimal_metadata table, and date_metadata table) and another table keeping track of the field name and what type it is.
2. Have a single table for storing the values of all metadata, but have a different column for each data type and only use the column specific to that metadata field to store the actual data.

Both of these solutions seem really clunky. Does anyone have a more elegant approach to storing these types of metadata values?

Answer 1

What I have done in the past is to use something like this:

1) Each document has a logical type associated with it.

2) Set up tables for metadata for each logical type. Each row can store all metadata associated with the document.

The other approach is that of key-value-modelling which can actually be ok or not depending on what you are doing with it. In this case you have a metadata table which stores all metadata, one value per record, for all documents. This works best if you find a reasonable way to aggregate the data in result sets, and if you aren't doing complex searches across multiple metadata fields.