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"
}
*/
Single document - Database side
> select * from users;
TRID EMAIL FIRST_NAME LAST_NAME USERNAME
------ --------------------------- -------------- ------------- --------------
2 dwain.jonhson@gmail.com Dwain Jonhson dwainjonhson
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"
}]
}
*/
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
Wanted: Feedbacks!
- How that would help you overcome the common issues working with SQL or Document databases?
- Any suggestions?
Top comments (2)
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/
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?