CODEWITHSUNDEEP
mysqlsqlmetadatarelational
Nick
Nick

Reputation: 156

Storing Nonrelational metadata in a MySQL database

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?

Upvotes: -1

Views: 3176

Answers (1)

Bill Karwin
Bill Karwin

Reputation: 562310

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:

Upvotes: 6

Related Questions