-1

I am developing a web app that uses a mysql database. I recently encountered some changes that require me to store 3rd party data having an inconsistent schema. I've decided to use mongodb as a solution for storing these entities. Furthermore, these entities have a many-to-many relationship with an existing table in the mysql database.

My solution (outlined below) is to store the 3rd party entities in mongodb as collection C, and change the schema of table B to reference the _id property of the documents stored in C.


Initial design

MySQL
Table R (one-to-many relations with A )
Table A
Table B
JoinTable AB

New requirement: The exact structure of entity B is unknown

New Design

MySQL
Table R (one-to-many relations with A)
Table A
Table B
JoinTable AB

MongoDB
Table C (referenced to by B.c_id)


With the initial design, the end user performs a get request for root entity R. A query is made for the desired rows of table R, and its child rows in table A (joining table A and table B on table AB).
provided is the query statement executed during a GET request:

    select {A.}, {B.}, {AB.*}
    from B b, A a
    join AB ab on AB.a_id=a.id
    where b.id = ab.b_id
    and a.r_id in (:rIdList)

The entity returned to the user is

{
    Rs:[{
        ...,
        As:[{
            ...,
            Bs:[{...}]
        }]
    }]
}

In the new Design, the same process takes place, but an additional query is made to mongodb finding all documents in collection C with _id = B.c_id. The results of which are included in the final dto returned to the end user. With this approach I don't have to migrate my entire data model to mongodb, but I've got an additional query being made to a another database.

I'd like to get some feedback (if possible) on other pros and cons regarding this approach. Is there a better solution to this problem? I'm also not sure how this will scale in the long run. Given my data structure would it be better to eventually put everything in mongodb? I've read that mysql does better with join operations, which is another reason why I went with this hybrid solution, but does that performance become negligible at scale when having to make an additional transaction?

1

MySQL has native JSON type.

https://dev.mysql.com/doc/refman/8.0/en/json.html

It may be easier to store all your data in one database.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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