How to store schema-less data efficiently in a relational database?

Question

I'm trying to get a nice balance of searchability and speed. I'm experimenting with different methods.

Option 1: Serialize the array as JSON/PHP serialized array and store it in a 'meta' column.

   id  |  name   | meta  
1       Bob       {"city":"GoTown","birthdate":"1980\/8\/14","cat":"Felix"}
2       Alice     {"city":"Streamville","birthdate":"1986\/6\/6","dog":"Rex"}

Option 2: Store keys and values together in a stack.

user_id  |    key   |   value   
1         name       Bob
1         city       GoTown
1         birthdate  1980/8/14
1         cat        Felix
2         name       Alice
2         city       Streamville
2         birthdate  1986/6/6
2         dog        Rex

Option 3: 3 tables: entites, keys, values. Store each key name only once.

user_id  |   name   
1         Bob
2         Alice

key_id   |   keyname   
1         city
2         birthdate
3         cat
4         dog

user_id   |   key_id   |   value
1          1            GoTown
1          2            1980/8/14
1          3            Felix
2          1            Streamville
2          2            1986/6/6
2          4            Rex

Are there any pitfalls with using any of these strategies? I'd like to eliminate some of them if they have insurmountable disadvantages.

EDIT: Added some data to represent schema-less data

Answer 1

Some really nasty problems would happen with these tables (idea)

1. Data Redundancy (duplicated data) what you need to keep in synchronisation (and you do you check this because MySQL doesn't have an nice JSON to records function?)

2. You can't force correct values on the database (say goodbye to data integrity and hello to garbage-in garbage-out data) example key birthdate could have value "hello"

And you need some kind of pivot query to fetch the key/values

If you really need dynamic key values (EAV) storage there are some more options:

• Maybe MySQL 5.6 NoSQL using the InnoDB memcached Plugin
• XML -> query with xpath
• RDF
• NoSQL like MongoDB
• MariaDB dynamic columns and MariaDB also supports json.

Answer 2

Given your requirements (MySQL and "schema-less"), I think it might be worth considering Option 1 with a twist. Consider XML instead of JSON.

Why? Because MySql doesn't give you an easy way to process JSON, but it does with xml.

Consider this:

id  |  name   | meta  
1      Bob      <city>GoTown</city><birthdate>1980-08-14&</birthdate>
2      Alice    <city>Streamville</city><birthdate>1986-06-06<birthdate>

Now you can do a sql query using xpath expressions on the content of the meta field. Like so:

SELECT name, extractValue( meta, '//birthdate' ) as birthdate
from tbl

You can even do selects based on the the city (which of course will be VERY inefficient since they will have to parse the contents of the fields that match the where clause, but:

SELECT name<BR>
from tbl<br>
where id < 100
having extractValue( meta, '//city' ) = 'Streamille'

You can even use UpdateXML (another MySQL function) to make changes to the content of your field all within MySQL.

While I know my response is late, I hope someone finds this useful / interesting. :)

Answer 3

From your sample data i actually see a schema for the table

UserID | UserName | City | Birthdate

If you're using a relational database with the right indexing a search on that would be very fast. If some values could be null then allow nullable columns.

A key value store is great in situations but can become very cumbersome when it comes to data validation. If you are using a relational database product put it to work and let it do the validation on the input side with properly typed columns.

If you really want to use a key value store, a NoSQL product optimized for them would probably be the better way to go.

Answer 4

_{Community wiki answer:}

Postgres has a lot more NoSQL features than MySQL. The JSON implementation is much more efficient (you can index JSON documents) and it has a very efficient key/value store (also indexable). Postgres is probably the most advanced "NoSQL relational database".

Answer 5

If you may need to seach all customers with birthday between 08/10 and 08/17, then option 1 is not suitable.

Options 2 and 3 are similar. Personally I prefer option 3 because I like to search or group by integer codes rather than by texts.

And by the way. I advice to store months and days as 2 numbers. Then you can sort them as strings and it still will be correct: 1970/10/10 will be after 1970/08/08, but it would be before 1970/8/8.

Answer 6

If you are not firmly settled upon using MySQL, you may consider DB2 as an option. It has native support for JSON data, which allows you to query and manipulate JSON fragments easily (http://publib.boulder.ibm.com/infocenter/db2luw/v10r5/topic/com.ibm.swg.im.dbclient.json.doc/doc/c0061316.html).

Another alternative is XML. Again, DB2 has native support for it, including indexing of XPath expressions for quick searches. (http://publib.boulder.ibm.com/infocenter/db2luw/v10r5/topic/com.ibm.db2.luw.xml.doc/doc/c0022308.html)

DB2 Express-C is the free edition, with no restriction on production use or database size.

Answer 7

You can also store json data in MySQL, MySQL 5.7 has native support of json data.

See mysql json data type

Answer 8

Have you consider NoSQL?

Because I work for a NoSQL vendor, I am a bit partial but I was trying to take your problem definition and view it from a lens of what technology is best. This description is ideal for NoSQL given it supports unstructured data and all the options you shared are doable in NoSQL. In my opinion, option 3 is pretty attractive because in a minimal amount of time you can create those tables and be done with it. And if you need to add additional columns, its trivial and you can keep versions of your data. And most of the NoSQL DBs support querying using many different ways and most of these fall within your model.

Most of the NoSQL DBs like FatDB, and RavenDB come to mind and would work well for this for the Windows space. MongoDB, etc, for others.