4

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': '[email protected]' }
{ 'lead_id':3829, 'best_time': 'evening' }
{ 'lead_id':3829, 'dob': '1980-07-01' }
{ 'lead_id':3830, 'email': '[email protected]' }
{ '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?

1

3 Answers 3

0

The MySQL shell can bulk insert JSON and BSON data (see https://mysqlserverteam.com/importing-data-from-mongodb-to-mysql-bson-data-types/) into JSON columns. From there you can use MySQL's JSON functions or use generated columns to extract JSON values into their own columns.

0

Whenever you design a NoSQL data model, you must have in mind what queries you will run against that data. That will determine how you store it. You will store data to make the queries you need easier to write or easier to optimize.

This is in contrast to relational data models, where the design is based on data and data relationships.

You mentioned querying by lead_id or by email, and possibly sorting by date. But that's still pretty vague. You need to be very specific about the queries you will run.

You also need to be specific about which NoSQL database you will choose. You mentioned "Redis, Mongo, etc." but that sounds like you haven't decided. They each have different features when it comes to querying on secondary attributes, so you should pick a technology that supports the types of queries you need to run.

Which brings you back to knowing more specifically the queries you will need.

1
  • To downvoter: Please explain why you think my answer deserves a downvote. Perhaps I can improve it. Aug 29, 2021 at 18:19
-1

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.