Mysql – Best practices for relating NoSQL data to RDMBS

MySQLnosql

I am looking to use both a NoSQL solution (Redis, Mongo, etc) with a mySQL/Laravel application. My reasons for this is that I need a schemaless key value storage to go with my relational data. I could easily make a key value storage within mySQL, but I think that this is a case where NoSQL could shine and would rather not take the easy way out. Other than coming here for help, that is.

What I am a little foggy is how to tie these two together. An example scenario is I have 2 forms that generate leads. One form has email, best time to call, and date of birth. The other has email and are you wearing pants? Is it as simple as storing data in my NoSQL table as follows:

{ 'lead_id':3829, 'email': '3829@test.com' }
{ 'lead_id':3829, 'best_time': 'evening' }
{ 'lead_id':3829, 'dob': '1980-07-01' }
{ 'lead_id':3830, 'email': '3830@test.com' }
{ 'lead_id':3829, 'pants': 'off' }

Then my mySQL Lead table could look like:

id   | form_id | timestamp
3829 | 1       | 2014-09-18 11:02:32  
3830 | 2       | 2014-09-18 09:12:10

Since this will be my first NoSQL excursion, I am also not certain as to the functionality available to my NoSQL data. Would it still be possible to retrieve data based on my lead_id and sort on a particular key? For example, filter form #2 results by email containing 'foo' sorted by the date they were submitted?

Best Answer

There are two ways I would approach this.

1.) Dump your json from Mongo into a tmp_retrieval_table in MySQL, then write corresponding db functions to perform the parsing / ETL to the schemata of choice in what ever fashion you see fit.

2.) Utilize MySQL's partitioning functionality. Where you could in deed sort the records by a specific key or timestamp.

MySQL Partitioning