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

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?

Discussion (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 Author

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?