Storing Nonrelational metadata in a MySQL database

Question

I was wondering what the best way to store non-relational metadata is in a MySQL database would be? The data won't be queried, so no indexes need to be applied to the data.

Here is the first example I tried, it seemed good in theory, but it turns out that the ORM I'm using makes it difficult to parse into a useful structure:

CREATE TABLE test (
  id INT(11) PRIMARY KEY AUTO_INCREMENT,
  username VARCHAR(255)
);
CREATE TABLE test_metadata (
  id INT(11) PRIMARY KEY AUTO_INCREMENT,
  test_id INT(11),
  name VARCHAR(255),
  value TEXT,

  FOREIGN KEY (test_id)
  REFERENCES test(id),
  UNIQUE KEY `uk_test_id_name` (test_id, name)
);

Full example: http://sqlfiddle.com/#!2/9d84f1

Then the other solution I was thinking of trying would be just storing the metadata as JSON in another column in the schema, like so:

CREATE TABLE test2 (
  id INT(11) PRIMARY KEY AUTO_INCREMENT,
  username VARCHAR(255),
  metadata TEXT
);

There are probably a lot of downsides to doing it this way though, off the top off my head I can think updating the metadata would require an additional SELECT before calling UPDATE

Which of these solutions would be best to store metadata in a relational DB? Or is there a better way I'm not seeing?

Since the data won't be queried, how about storing metadata in JSON format? — kums, Oct 16 '14 at 18:49
@kums: That's what I was sort of thinking in the second example, in the metadata field, I forgot to add that to the post. I'll edit it to make that clear. — Nick, Oct 16 '14 at 18:50
@MarkusMalkusch: I mean some of the data that will be stored is optional, and some of the data being stored will only be available for one record, so there isn't 70+ database columns filled with NULL or reusing columns (like a `shoe_size` column being reused to store monthly income, or something) — Nick, Oct 16 '14 at 18:53
@MarkusMalkusch, Entity-Attribute-Value tables are *not* relational. — Bill Karwin, Oct 16 '14 at 18:54
@BillKarwin I was hoping that first one wasn't EAV. I heard that if you do use EAV you'll wake up in the middle of the night to a few DBAs wiedling crowbars and bats. But I couldn't seem to find a good resource on exactly was EAV was, so I was hoping that that first example was NOT EAV — Nick, Oct 16 '14 at 18:56
This was kind of already answered: http://stackoverflow.com/a/15367769/2745300 — kums, Oct 16 '14 at 18:56

Bill Karwin · Accepted Answer · 2014-10-16T19:03:38.203

5

I wrote a presentation about different options for storing semi-structured data in MySQL.

Extensible Data Modeling with MySQL

I cover several alternatives and compare them for pros and cons:

Extra Columns
Entity-Attribute-Value
Class Table Inheritance
Serialized LOB & Inverted Indexes
Online Schema Changes
Non-Relational Databases

The best solution often depends on how you're going to use the data. None of the solutions are perfect, because the bottom line is that you're storing non-relational data in an RDBMS. So you have to make some compromises somewhere. The trick is to make the compromises in features that aren't important to your application's usage of the data.

You mentioned that you're not familiar with EAV. Here are a couple of resources:

edited Oct 16 '14 at 19:03

answered Oct 16 '14 at 18:58

Bill Karwin

538,548
86
673
828

Thanks for the awesome presentation, and EAV links. I guess serializing the data is the best option for what I need – Nick Oct 16 '14 at 19:54
Glad to help. Yes, I would expect serialized LOB is the best option if you don't need to query individual fields of the semi-structured data. – Bill Karwin Oct 16 '14 at 19:59
Really Nice! And like you said non of the solutions are perfect. Now i have to look at my data to figure out which one is the best in my case. But this was a really good help. Thanks – tiran Apr 05 '15 at 07:51

Storing Nonrelational metadata in a MySQL database

1 Answers1