CODEWITHSUNDEEP
mysqlsqltypesmetadata
Erik
Erik

Reputation: 87

How to store metadata datatypes

How do I store metadata to describe assets? Considering that I don't know in advance which metadatafields I'll have.

I have a table with assets (let's say persons), a table with metadata fields ("name", "age", "day of birth", ...) a table with metadata values that links to the metadata fields table ("John Doe", 44, "1968-10-10", ...) and a crosstable metadata that links the metadatafields to the assets.

My problem is how do I handle the different datatypes in the metadata fields table. "John Doe" is text, 44 is int, 1968-10-10 is a date.

Do i store these in a txt-field in my metadatafields table, but will I be able to compare dates?

Or do I store the datatype in this table and do I make 3 fields for txt,int and date. But then I have a lot of empty fields.

Or do I make different metadata fields tables for each datatype (eg: metadatafields_txt, metadatafields_int, metadatafields_date) but then I can't link properly to the metadata table.

What is the best practice here?

tx

Upvotes: 2

Views: 1482

Answers (2)

user359040
user359040

Reputation:

All of these are valid options (with a slight modification in the first case - I would store an additional field to indicate which type of data is being stored, and convert appropriately where required).

The third option should be viable - instead of inner joining to one table, you would left join to the three different tables in your query.

As Eugen says, it depends - do you know in advance roughly how many metadata records (just to an order of magnitude - thousands, millions, more?) you expect to have, and whether they are likely to be overwhelmingly of one type?

Upvotes: 1

Eugen Rieck
Eugen Rieck

Reputation: 65284

The question to your answer depends, on what you want to do with the metadata: If you just want to store them, a text representation in a VARCHAR field might be enough and it will simplify your queries.

The moment you want to query your metadata, stay away from storing a textual representation or you will be burned by 8<10 vs. '8'>'10' and friends. In thsi case I recommend you either have a metadata table with 3 fields, or even have 3 metadata tables. I suspect to be the sweet point to be a single table with 3 fields - queries still quite easy, and space waste manageable (an int is 4 bytes, an empty varchar is 2 or 3 bytes).

BTW: You could make good use of the int field, for other data types: By storing the unix timestamp for a date, you might be able to avoid some UNIX_TIMESTAMP() or FROM_UNIXTIME() magic later. For strings you might want the length (esp. if you use a C-ish API)

Upvotes: 3

Related Questions