Use relational database as document database: get the best of both worlds
Tags: database, agile, mongodb, postgres
Published: 2021-09-23 12:48:33

**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**

```js
  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"
      }
  */
```

**Single document - Database side**

```sql
> select * from users;

TRID   EMAIL                       FIRST_NAME     LAST_NAME     USERNAME
------ --------------------------- -------------- ------------- --------------
     2 dwain.jonhson@gmail.com     Dwain          Jonhson       dwainjonhson
```

**Nested documents - code side**

```js
  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" 
         }]
      }
  */
```

**Nested documents - database side**

```sql
> 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      
```

## Wanted: Feedbacks!

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