DEV Community

Cover image for Use relational database as document database: get the best of both worlds
Nicolas Penot
Nicolas Penot

Posted on

Use relational database as document database: get the best of both worlds

#database #agile #mongodb #postgres

Problem with document database: can rapidly become a mess, it's difficult to rearrange and can consume a lot of resources when scanning.

Problem with relational database: schemas (tables/columns) are too static and must be maintained on the database side and code side.

Solution: A database that internally stores documents in tables by breaking down documents' attributes into rights tables' fields "on the fly".

Benefits of the solution:

  • Schemaless structures maintain in the code
  • Performance and efficiency of relational database
  • Easier to maintain in the long term
  • Consume much fewer resources (cheaper in the cloud)

Examples

Single document - code side

 var doc = { email: "dwain.jonhson@gmail.com", firstname: "Dwain", lastname: "Jonhson", username: "dwainjonhson" }; doc.save(); collection("users").find({username: "dwainjonhson"}); /* { trid : 2, // auto id generation email: "dwain.jonhson@gmail.com", firstname: "Dwain", lastname: "Jonhson", username: "dwainjonhson" } */
Enter fullscreen mode Exit fullscreen mode

Single document - Database side

> select * from users; TRID EMAIL FIRST_NAME LAST_NAME USERNAME ------ --------------------------- -------------- ------------- -------------- 2 dwain.jonhson@gmail.com Dwain Jonhson dwainjonhson
Enter fullscreen mode Exit fullscreen mode

Nested documents - code side

 var doc = { email: "dwain.jonhson@gmail.com", firstname: "Dwain", lastname: "Jonhson", username: "dwainjonhson", phones: [{ alias: "home", number: "+1-202-555-0143" },{ alias: "mobile", number: "+1-202-555-0156" }] }; doc.save(); collection("users").find({username: "dwainjonhson"}); /* { trid : 2, // auto id generation email: "dwain.jonhson@gmail.com", firstname: "Dwain", lastname: "Jonhson", username: "dwainjonhson" phones: [{ trid : 1, // auto id generation alias: "home", number: "+1-202-555-0143" },{ trid : 2, // auto id generation alias: "mobile", number: "+1-202-555-0156" }] } */
Enter fullscreen mode Exit fullscreen mode

Nested documents - database side

> select * from users; TRID EMAIL FIRST_NAME LAST_NAME USERNAME ------ --------------------------- -------------- ------------- -------------- 2 dwain.jonhson@gmail.com Dwain Jonhson dwainjonhson -- Nested phone documents automatically organized in table with the proper relationship. > select * from users_phones; TRID USERD_TRID ALIAS NUMBER ------ ----------- ----------------- ------------------------ 1 2 home +1-202-555-0143 2 2 mobile +1-202-555-0156
Enter fullscreen mode Exit fullscreen mode

Wanted: Feedbacks!

  • How that would help you overcome the common issues working with SQL or Document databases?
  • Any suggestions?

Top comments (2)

Collapse
 
darkain profile image
Vincent Milum Jr •

Modern "relational" databases natively support JSON data as well, and this can be used along side existing schema based data.

mariadb.com/kb/en/json-data-type/
mariadb.com/kb/en/json-functions/

Collapse
 
niolap profile image
Nicolas Penot •

Indeed Vincent. But the idea here is that you don't have to define a data model at all. The database will cast your JSON in tables transparently. Thus you can have the benefit of a document database with the benefit of a relational database.
Have you ever use Mongodb in a project?