tag:blogger.com,1999:blog-25983539134636871082024-02-21T08:33:38.030-08:001Schema.comAt 1Schema.com, we aim to unify traditional SQL design with NoSQL design by introducing new, powerful distinctions!
<br><br>
Please try our software at <a href="www.1Schema.com">www.1Schema.com</a>1Schema.comhttp://www.blogger.com/profile/01478882099267314745noreply@blogger.comBlogger5125tag:blogger.com,1999:blog-2598353913463687108.post-22853178851529090152017-01-09T13:03:00.003-08:002017-01-21T12:22:18.728-08:00<meta name="title" content="Convert from SQL to NoSQL in 60 Seconds" />
<meta name="keywords" content="SQL,NoSQL,database,design,conversion,fast,schema,data,containment,caching,embedding,nesting,table,foreignkey,document,object,JSON" />
<meta name="description" content="Convert from SQL to NoSQL in 60 Seconds" />
<span style="font-size: x-large;font-family: "arial" , "helvetica" , sans-serif;">
<b>Convert from SQL to NoSQL in 60 Seconds</b>
</span>
<br /><br />
<span style="font-size: small;font-family: "arial" , "helvetica" , sans-serif;">
Today, we are launching the <a href="http://www.1schema.com/DatabaseConverter" target="_blank">1Schema Database Converter</a>, which you can use to easily convert your existing SQL schema and data to a NoSQL database technology of your choosing.
<br /><br />
We built this converter to ease the transition when switching between database technologies.
<br /><br />
We want to help you find the technology that works best for your particular application!
</span>
<br /><br />
<span style="font-size: large;font-family: "arial" , "helvetica" , sans-serif;">
<b>What you need to get started:</b>
</span>
<br /><br />
<span style="font-size: small;font-family: "arial" , "helvetica" , sans-serif;">
Before you begin, you will need to export the SQL code for your existing database as a plain text (not binary).
<br /><br />
This file must contain all the CREATE TABLE and ALTER TABLE statements necessary to create your Tables, Primary Keys, Unique Constraints, and Foreign Keys.
<br /><br />
Also, the file may include data, so long as all data is encoded as INSERT statements and the total size of your file remains under 5 MB. Your data will be transformed and included in your converted database script.
<br /><br />
If you need to convert more data than 5 MB will allow, please <a href="mailto:support@1schema.com" target="_blank">contact us</a> for help.
<br /><br />
If you want to use the same database that we use for this tutorial, download our SQL file:
<br /> <a href="https://drive.google.com/open?id=0Bzu0WfdJcv2TeGp5SnlDejkxbkk" target="_blank">SQL Server Demo DB</a>
<br /> <a href="https://drive.google.com/open?id=0Bzu0WfdJcv2TVmNRcUZJS3dUZGM" target="_blank">PostgreSQL Demo DB</a>
</span>
<br /><br />
<span style="font-size: large;font-family: "arial" , "helvetica" , sans-serif;">
<b>What to think about next:</b>
</span>
<br /><br />
<span style="font-size: small;font-family: "arial" , "helvetica" , sans-serif;">
So you have your SQL schema file... you can now proceed to the 1Schema Converter and convert your file.
<br /><br />
However, before doing so, it is worth thinking about how you want to configure the 1Schema Converter so that you get the desired result.
<br /><br />
So for each SQL Table, consider how you want the Foreign Keys to be mapped during conversion:
</span>
<br />
<ol>
<li><span style="font-size: small;font-family: "arial" , "helvetica" , sans-serif;">Does the FKEY indicate the "Parent Table", inside which the Current Table should be embedded (aka nested)?</span></li>
<li><span style="font-size: small;font-family: "arial" , "helvetica" , sans-serif;">For all other FKEYs, should the list of related IDs from the Current Table be stored in the Parent Table?</span></li>
<li><span style="font-size: small;font-family: "arial" , "helvetica" , sans-serif;">EXPERIMENTAL: Should IDs be traversed and cached within the database?</span></li>
</ol>
<span style="font-size: small;font-family: "arial" , "helvetica" , sans-serif;">
The answer to these questions will directly determine the settings that you should select in the 1Schema Converter.
<br /><br />
If these questions are not immediately clear, do not worry, as the default conversion settings will provide you with a solid starting point.
</span>
<br /><br />
<span style="font-size: large;font-family: "arial" , "helvetica" , sans-serif;">
<b>How to use the 1Schema Converter:</b>
</span>
<br /><br />
<span style="font-size: small;font-family: "arial" , "helvetica" , sans-serif;">
You are now ready to use the 1Schema Converter to convert your SQL database:
<br /><br />
1) Go to "http://www.1schema.com/DatabaseConverter" and you should see the following page:
</span>
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjcLT-1oI6cSYDpC6yHJmO7Pc7RHP2vMIk5futGXzr18Vra1zXgzHWji88kSll0GnjeHUeW8zOAXpje52XuJ4cusE7al27jQOnNkRLF1BbWc3pIlYW47KRSpS3viUsRAw2WtXfgYKdtnLw/s1600/Step_01__Go_to_Converter.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="207" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjcLT-1oI6cSYDpC6yHJmO7Pc7RHP2vMIk5futGXzr18Vra1zXgzHWji88kSll0GnjeHUeW8zOAXpje52XuJ4cusE7al27jQOnNkRLF1BbWc3pIlYW47KRSpS3viUsRAw2WtXfgYKdtnLw/s1600/Step_01__Go_to_Converter.png" width="400" /></a>
</div>
<ul>
<li><span style="font-size: small;font-family: "arial" , "helvetica" , sans-serif;">Select the "SQL Database Type" that corresponds to the source of your input file (for our demo, we will use "Microsoft SQL Server ®")</span></li>
<li><span style="font-size: small;font-family: "arial" , "helvetica" , sans-serif;">Select the "NoSQL Database Type" that corresponds to the type of output you want (currently, only "MongoDB ®" is implemented)</span></li>
<li><span style="font-size: small;font-family: "arial" , "helvetica" , sans-serif;">Select the "Schema Conversion Mode" depending on whether you want to use the experimental code to store cached data in the database</span></li>
<li><span style="font-size: small;font-family: "arial" , "helvetica" , sans-serif;">Select the SQL file that contains your original schema</span></li>
</ul>
<br />
<span style="font-size: small;font-family: "arial" , "helvetica" , sans-serif;">
2) Wait until the grid below populates
</span>
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjxcJYkbo7HRM9o-sTKoH9j_6lTYfD0SwqSHMDVPl2jYm1l6AuzpXKbxhr4yURyygQu8DKQyPP1XpCfn0-dmoOXh8CMa4mLcoy0pu0QYT5wp282V6QBX4QiEUjhxVyZvpEcmBqnN9s0sKU/s1600/Step_02__Load_DB_File.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="208" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjxcJYkbo7HRM9o-sTKoH9j_6lTYfD0SwqSHMDVPl2jYm1l6AuzpXKbxhr4yURyygQu8DKQyPP1XpCfn0-dmoOXh8CMa4mLcoy0pu0QYT5wp282V6QBX4QiEUjhxVyZvpEcmBqnN9s0sKU/s1600/Step_02__Load_DB_File.png" width="400" /></a>
</div>
<span style="font-size: small;font-family: "arial" , "helvetica" , sans-serif;">
3) Use the grid to select your desired settings
</span>
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhyi36Q0n-z_bN7fu6b5vS5mB4jQE0aoiUWMdsPDKhSWPog-3Y9e6nuQkGBSNuZ0laOTM2r-cgXVjfxXKG75nFJfieOBuxBFgZzb9IGmv-9bbuiWEzv1AaNJAoXMWnGgJrmAWS5xpACKIs/s1600/Step_03__Specify_Settings.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="208" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhyi36Q0n-z_bN7fu6b5vS5mB4jQE0aoiUWMdsPDKhSWPog-3Y9e6nuQkGBSNuZ0laOTM2r-cgXVjfxXKG75nFJfieOBuxBFgZzb9IGmv-9bbuiWEzv1AaNJAoXMWnGgJrmAWS5xpACKIs/s1600/Step_03__Specify_Settings.png" width="400" /></a>
</div>
<span style="font-size: small;font-family: "arial" , "helvetica" , sans-serif;">
4) Scroll to the bottom of the page and click the "Convert Database" button.
<br /><br />
5) Download your converted file.
</span>
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiZ6bwUAswlWYFQbRjlLzE-85z5h46vS0gpZuU-QxPEU951TUVucS8wzPfke2R6-G013eYJKrkrnaOWND_zrcZGyhP9tIzp1raVuHvSoMfe-IOi3sAcUAPDm1uK8wq68x1RjXwCRQiTsA8/s1600/Step_05__Save_File.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="208" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiZ6bwUAswlWYFQbRjlLzE-85z5h46vS0gpZuU-QxPEU951TUVucS8wzPfke2R6-G013eYJKrkrnaOWND_zrcZGyhP9tIzp1raVuHvSoMfe-IOi3sAcUAPDm1uK8wq68x1RjXwCRQiTsA8/s1600/Step_05__Save_File.png" width="400" /></a>
</div>
<span style="font-size: small;font-family: "arial" , "helvetica" , sans-serif;">
NOTE: Your browser may warn you about downloading JavaScript files. Please download your converted file anyways.
<br /><br />
5) Load your converted file into your NoSQL database.
</span>1Schema.comhttp://www.blogger.com/profile/01478882099267314745noreply@blogger.com0tag:blogger.com,1999:blog-2598353913463687108.post-65489206338598018272017-01-08T18:12:00.002-08:002017-01-19T13:20:48.191-08:00<meta name="title" content="Thoughts on Converting from SQL to NoSQL" />
<meta name="keywords" content="SQL,NoSQL,database,design,conversion,containment,caching,embedding,nesting,table,foreignkey,document,object,JSON" />
<meta name="description" content="Thoughts on Converting from SQL to NoSQL" />
<span style="font-size: x-large;font-family: "arial" , "helvetica" , sans-serif;">
<b>Thoughts on Converting from SQL to NoSQL</b>
</span>
<br /><br />
<span style="font-size: small;font-family: "arial" , "helvetica" , sans-serif;">
From our previous posts, you know that while we do support conversion to NoSQL using ODM libraries, we invested significant effort building our own customized approach to managing cross-collection references.
<br /><br />
We want to take this opportunity to clarify some important points, so you understand why we developed our own approach and how this approach can help you achieve your goals.
<br /><br />
We also explain how you can use the <a href="http://www.1schema.com/DatabaseConverter" target="_blank">1Schema Database Converter</a> to help perform this process quickly, easily, and correctly.
</span>
<br /><br />
<span style="font-size: large;font-family: "arial" , "helvetica" , sans-serif;">
<b>The Use-Case for Consideration:</b>
</span>
<br /><br />
<span style="font-size: small;font-family: "arial" , "helvetica" , sans-serif;">
We are focused on the use case where an engineering team is trying to optimize performance and scalability of a website or app by switching to a NoSQL database technology.
<br /><br />
In this case, the promise of NoSQL technologies is that by storing larger clusters of information (including nested information), the amount of server processing necessary to render a page view can be minimized.
<br /><br />
Ideally, you could render a page view with only 1 read from the database (although this goal is often not possible in practice).
<br /><br />
The cost of this approach is that more disk space is used to store these data structures, as information is often duplicated in multiple places.
<br /><br />
However, since disk space is relatively cheap, it can be extremely beneficial to sacrifice disk space to improve processing time.
<br /><br />
If you believe in the benefits of this approach, you should try converting your SQL database using our custom document caching techniques, which solve the problems laid out below.
</span>
<br /><br />
<span style="font-size: large;font-family: "arial" , "helvetica" , sans-serif;">
<b>The Problem with this Use-Case:</b>
</span>
<br /><br />
<span style="font-size: small;font-family: "arial" , "helvetica" , sans-serif;">
Often, when SQL-based engineers start learning about NoSQL technologies, they see the ability to embed (or nest) information within a given document (i.e. NoSQL equivalent of a row) as a really promising feature.
<br /><br />
They come from a world of flat rows, where storing nested information requires creating a separate child table and creating a foreign key constraint between the two tables.
<br /><br />
The idea of just adding nested information is often quite appealing.
<br /><br />
However, the shine usually wears off when they start to realize that they need to nest the same information in multiple places.
<br /><br />
To prevent the confusion caused by duplication, they often change their NoSQL design to use a more normalized approach to avoid duplication.
<br /><br />
BUT THIS DEFEATS THE PURPOSE OF SWITCHING TO NOSQL!!!
<br /><br />
This lack of clarity really hurts NoSQL adoption, because here is where many engineers revert to using normalized schemas in traditional SQL databases.
<br /><br />
So the question becomes, how do we
<br />
</span>
<ol>
<li><span style="font-size: small;font-family: "arial" , "helvetica" , sans-serif;">Take advantage of NoSQL technologies to reduce server processing and increase performance and scalability AND</span></li>
<li><span style="font-size: small;font-family: "arial" , "helvetica" , sans-serif;">Still know what the heck is going on</span></li>
</ol>
<br />
<span style="font-size: large;font-family: "arial" , "helvetica" , sans-serif;">
<b>How 1Schema Solves This:</b>
</span>
<br /><br />
<span style="font-size: small;font-family: "arial" , "helvetica" , sans-serif;">
At 1Schema, we built our custom NoSQL converter from the ground-up to help you differentiate between data that is truly embedded within your root document (<b>contained data</b>) and data that you are copying there to improve performance (<b>cached data</b>).
<br /><br />
NOTE: For a more detailed explanation of the difference between <b>contained data</b> and <b>cached data</b>, see <a href="http://1schema.blogspot.com/2017/01/Why-Caching-Matters.html" target="_blank">this post</a>...
<br /><br />
All <b>contained data</b> actually exists within its parent document, so you know to go to the parent document to update it. In the 1Schema Converter, you specify a single "Parent Table" if you want that your table to be <b>contained</b> within the parent table. The options for "Parent Table" will be obtained by examining the Foreign Key Constraints specified for a particular table.
<br /><br />
Conversely, all <b>cached data</b> is copied locally by our update management code, but actually exists elsewhere. All Foreign Key Constraints other than those used to specify the "Parent Table" can be used to configure <b>caching</b>.
<br /><br />
Caching actually occurs when our change management code traverses an ID reference and copies the relevant information locally. Furthermore, the change management code will automatically update this data when the document in the foreign collection changes.
<br /><br />
By using caching, you can accomplish the goal of larger clusters of information that reduce the processing necessary to render a page view, but prevent the confusion that duplication often causes within a data model.
<br /><br />
The catch is that you are not guaranteed this <b>cached data</b> is always up-to-date, since it is copied from the place that it is actually stored.
<br /><br />
However, our converter provides you with code that automatically updates your <b>cached data</b>, and you can configure this code to run as you desire.
<br /><br />
Also, please note that to simplify the number of settings in the converter, you can either specify to use caching or not for a particular table, but you cannot choose individual foreign keys to use. It is all or nothing.
<br /><br />
If you need more granularity on which ID references are used for caching, you should sign-up for the beta release of our visual Schema Designer.
</span>
<br /><br />
<span style="font-size: x-large;font-family: "arial" , "helvetica" , sans-serif;">
<b>Concluding Thoughts</b>
</span>
<br /><br />
<span style="font-size: small;font-family: "arial" , "helvetica" , sans-serif;">
At 1Schema, we do not advocate either normalization or denormalization... instead, we want to give you control of this choice on a case-by-case basis.
<br /><br />
In fact, it can actually be quite beneficial to start with a normalized SQL schema and proceed from there.
<br /><br />
Then you can intelligently choose when to denormalize specific parts of your data model, depending on the criteria important to you.
</span>1Schema.comhttp://www.blogger.com/profile/01478882099267314745noreply@blogger.com0tag:blogger.com,1999:blog-2598353913463687108.post-45131869423336906582017-01-05T01:34:00.001-08:002017-01-25T11:11:48.658-08:00<meta name="title" content="Why Caching Matters?" />
<meta name="keywords" content="SQL,NoSQL,database,design,containment,caching,embedding,nesting,table,foreignkey,document,object,JSON" />
<meta name="description" content="Why Caching Matters?" />
<span style="font-size: x-large;font-family: "arial" , "helvetica" , sans-serif;">
<b>Why Caching Matters?</b>
</span>
<br /><br />
<span style="font-size: small;font-family: "arial" , "helvetica" , sans-serif;">
Recall from our <a href="http://1schema.blogspot.com/2016/12/why-nosql.html" target="_blank">previous post</a> that embedding can be used to either <b>contain</b> or <b>cache</b> data.
<br /><br />
Now consider the following JSON object (or document):
<br /><br />
<span style="font-size: small;font-family: "courier new" , "courier" , monospace;">
var product = <br />
{ <br />
_id: "P_00001",<br />
Name: "Baseball Bat",<br />
Price: 20,<br />
Category: {<br />
_id: "C_00001",<br />
Name: "Sporting Goods",<br />
URL: "./Sporting_Goods/Index.html"<br />
}<br />
} <br />
<br />
</span>
How do you know whether the information embedded in the "Category" field is <b>contained</b> or <b>cached</b>?
<br /><br />
As a human, you can infer that the Category of "Sporting Goods" is probably relevant for many Products, so the "Category" field probably represents <b>cached</b> data.
<br /><br />
The real Category data probably exists in a collection of Categories elsewhere, but for performance or simplicity, the data was duplicated within the Product.
<br /><br />
It is important to realize this distinction, because caching is intricately connected to one of the major reasons often cited for switching to NoSQL: storing larger objects that reduce the amount of server-side processing necessary to render a given page.
</span>
<br /><br />
<span style="font-size: x-large;font-family: "arial" , "helvetica" , sans-serif;">
<b>What's The Problem?</b>
</span>
<br /><br />
<span style="font-size: small;font-family: "arial" , "helvetica" , sans-serif;">
Consider that you want to change the URL for the Category "Sporting Goods". How do you accomplish this?
</span>
<br /><br />
<span style="font-size: large;font-family: "arial" , "helvetica" , sans-serif;">
<b>Without Schema</b>
</span>
<br /><br />
<span style="font-size: small;font-family: "arial" , "helvetica" , sans-serif;">
Without schema, you must face the fact that this information could be copied anywhere in your database? Are you going to query every document to find the cached copies?
<br /><br />
Doing so would probably not be a very effective use of resources...
</span>
<br /><br />
<span style="font-size: large;font-family: "arial" , "helvetica" , sans-serif;">
<b>With Implied Schema</b>
</span>
<br /><br />
<span style="font-size: small;font-family: "arial" , "helvetica" , sans-serif;">
With implied schema, you can assume which fields store cached data.
<br /><br />
For example, you can assume that the "Category" field of any Product caches a Category document.
<br /><br />
For each database that you build, you could write code to manage updates to cached information.
<br /><br />
But you would need to build this code flexibly enough to handle special cases...
<br /><br />
For instance, if you cached Product data inside a Sales Order, you would not want to update that cached Price value, even if the Product's price changes, because the data would not match what the Customer actually paid.
<br /><br />
Complicated cases require more update management code, which can introduce even more errors.
</span>
<br /><br />
<span style="font-size: large;font-family: "arial" , "helvetica" , sans-serif;">
<b>With ODM Libraries</b>
</span>
<br /><br />
<span style="font-size: small;font-family: "arial" , "helvetica" , sans-serif;">
ODM libraries take an entirely different approach. Instead of managing cached data stored in the database, they dynamically traverse references as data is read into memory on the web server.
<br /><br />
One benefit of this approach is that it should be familiar to most software developers, since many SQL ORMs take the same approach.
<br /><br />
However, if you use this approach, you are making a conscious decision to store smaller, more row-like objects, and you will then lose-out on some of the promised advantages of moving to NoSQL.
<br /><br />
Specifically, this style of usage precludes the benefits of storing larger objects that contain lots of cached data.
<br /><br />
Also, there are limitations for how this approach can be used in terms of referencing sub-documents from other collections.
<br /><br />
Overall, this is a safe approach for converting from SQL to NoSQL, so we use this as the default in our <b>1Schema Database Converter</b>.
<br /><br />
But if you want to take advantage of storing large documents, keep reading...
</span>
<br /><br />
<span style="font-size: large;font-family: "arial" , "helvetica" , sans-serif;">
<b>With 1Schema's Experimental Caching</b>
</span>
<br /><br />
<span style="font-size: small;font-family: "arial" , "helvetica" , sans-serif;">
Perhaps you are either unconvinced of the value of using the ODM approach described above or your compelling reason for switching is tied to caching.
<br /><br />
You may be scratching your head right now, wondering what the point of switch to NoSQL is. Many of the other scalability benefits of NoSQL can be achieved with modern SQL databases.
<br /><br />
<b>If you fall into this category, you should try our in-house approach to caching!</b>
<br /><br />
As mentioned above, if we choose to cache data within the database, we need to make sure that we know 1) what is cached where and 2) how to update it.
<br /><br />
To handle the first point, we introduce conventions that allow us to clearly mark cached data and configure how this data is updated.
<br /><br />
As for the second point, we auto-generate change propagation functions for each collection that caches data, so you do not need to do any extra work.
<br /><br />
We seek to provide the power of caching without and fo the headache, so you can unleash the full power of NoSQL databases.
<br /><br />
<b>How Our Conventions Work</b>
<br /><br />
For caching data, we embed ID references within the root document, similar to how you use ID refereneces in SQL or Mongoose ODM.
<br /><br />
However, at the point of the ID reference, instead of embedding the ObjectID directly, we embed an extra object around this ObjectID, so that we have a place where we can configure how the reference used to <b>cache</b> data.
<br /><br />
This extra level of embedding also provides a clear visual indication as to which data is <b>cached</b>.
<br /><br />
For example, here is how 1Schema would export our original example:
<br /><br />
<span style="font-size: small;font-family: "courier new" , "courier" , monospace;">
var product = <br />
{ <br />
_id: "P_00001",<br />
Name: "Baseball Bat",<br />
Price: 20,<br />
Category: {<br />
_id: "C_00001",<br />
OS_LAST_UPDATE_DATE: "2017-01-04",<br />
OS_MAX_CACHING_DEPTH: 1,<br />
OS_CACHED_DOC: {<br />
_id: "C_00001",<br />
Name: "Sporting Goods",<br />
URL: "./Sporting_Goods/Index.html"<br />
}<br />
}<br />
} <br />
<br />
</span>
Note the special fields "OS_LAST_UPDATE_DATE", "OS_MAX_CACHING_DEPTH", and "OS_CACHED_DOC". These fields configure how changes to the <b>cached</b> document are handled.
<br /><br />
Additionally, the "OS_DO_NOT_UPDATE" field could be used to instruct our update management code to <b>never</b> update the cached value (see Price example above).
<br /><br />
Furthermore, we are working on adding more special fields that allow you to further configure caching behavior based on dynamic constraints.
</span>
<br /><br />
<span style="font-size: x-large;font-family: "arial" , "helvetica" , sans-serif;">
<b>The Benefit to You</b>
</span>
<br /><br />
<span style="font-size: small;font-family: "arial" , "helvetica" , sans-serif;">
So by using the 1Schema approach, you get the following benefits:
<br />
</span>
<ol>
<li><span style="font-size: small;font-family: "arial" , "helvetica" , sans-serif;">Easily differentiate between <b>Contained Data</b> and <b>Cached Data</b></span></li>
<li><span style="font-size: small;font-family: "arial" , "helvetica" , sans-serif;">Automatically update <b>Cached Data</b> without needing to write change handling code</span></li>
<li><span style="font-size: small;font-family: "arial" , "helvetica" , sans-serif;">Leverage the full benefits of NoSQL to decrease the amount of processing necessary to render a page</span></li>
</ol>1Schema.comhttp://www.blogger.com/profile/01478882099267314745noreply@blogger.com0tag:blogger.com,1999:blog-2598353913463687108.post-64453328048007801232016-12-06T14:46:00.002-08:002017-01-12T13:33:17.606-08:00<meta name="title" content="Why convert to NoSQL?" />
<meta name="keywords" content="SQL,NoSQL,database,design,containment,caching,embedding,nesting,table,foreignkey,document,object,JSON" />
<meta name="description" content="Why convert to NoSQL?" />
<span style="font-size: x-large;font-family: "arial" , "helvetica" , sans-serif;">
<b>Why NoSQL?</b>
</span>
<br /><br />
<span style="font-size: small;font-family: "arial" , "helvetica" , sans-serif;">
Traditional SQL databases are row-centric, and they derive their strengths of transactionality and consistency from this focus. Data is managed so that each individual row is always in a consistent state.
<br /><br />
For applications where data integrity is critical, this is a good perspective to take on the system.
<br /><br />
However, for web applications, often performance and scalability are more important than complete transactionality.
<br /><br />
More simply put, it is often better to return data that is mostly up-to-date quickly than data that is 100% up-to-date slowly.
<br /><br />
So NoSQL databases were created to address this need.
<br /><br />
They store bigger chunks of data together so that less server processing is necessary to render a complete page view. However, some of that data may be duplicated from other places within the database, and different values in different places may differ at any given time.
<br /><br />
With 1Schema.com, our goal is to provide a tool that can help you store your system's data in whole-page data models, without needing to worry about what data is up-to-date and what data has been previously duplicated.
</span>
<br /><br />
<span style="font-size: x-large;font-family: "arial" , "helvetica" , sans-serif;">
<b>Embedding Data in NoSQL</b>
</span>
<br /><br />
<span style="font-size: small;font-family: "arial" , "helvetica" , sans-serif;">
From a design perspective, the most noticeable difference between SQL databases and NoSQL databases is the ability in NoSQL to "embed" (or "nest") complex values within a parent row (or document).
<br /><br />
Embedded information can either take the form of a nested object or a nested array.
<br /><br />
Embedding can be used to either:
</span>
<br />
<ol>
<li><span style="font-size: small;font-family: "arial" , "helvetica" , sans-serif;"><b>Contain</b> data that exists locally within the parent document, or</span></li>
<li><span style="font-size: small;font-family: "arial" , "helvetica" , sans-serif;"><b>Cache</b> data that has been duplicated from elsewhere in the database</span></li>
</ol>
<span style="font-size: small;font-family: "arial" , "helvetica" , sans-serif;">
For this post, we are focused on the first use for embedding... <b>containing</b> data within a parent document.
<br /><br />
To explain this, we will use the following example of a Sales Order for a bike shop:
<br /><br />
</span>
<span style="font-size: small;font-family: "courier new" , "courier" , monospace;">
<b>Sales Order #001</b>
<br />
</span>
<ul>
<li><span style="font-size: small;font-family: "courier new" , "courier" , monospace;">Sales Agent = "Bob"</span></li>
<li><span style="font-size: small;font-family: "courier new" , "courier" , monospace;">Customer = "Chris"</span></li>
<li><span style="font-size: small;font-family: "courier new" , "courier" , monospace;">1 x "Speedy Bike" at $60 each = $60</span></li>
<li><span style="font-size: small;font-family: "courier new" , "courier" , monospace;">2 x "Protecto Helmet" at $20 each = $40</span></li>
</ul>
<br />
<span style="font-size: large;font-family: "arial" , "helvetica" , sans-serif;">
<b>SQL Perspective</b>
</span>
<br /><br />
<span style="font-size: small;font-family: "arial" , "helvetica" , sans-serif;">
In SQL, <b>contained</b> data is stored within sub-tables, connected to the main table via relationships.
<br /><br />
So for the example above, we would need at least 2 tables:
</span>
<br />
<ol>
<li><span style="font-size: small;font-family: "arial" , "helvetica" , sans-serif;">Sales_Order (parent table)</span></li>
<li><span style="font-size: small;font-family: "arial" , "helvetica" , sans-serif;">Sales_Order_Item (child table)</span></li>
</ol>
<span style="font-size: small;font-family: "arial" , "helvetica" , sans-serif;">
These tables would be connected using a <b>foreign key</b> on the "SalesOrderID" column.
<br /><br />
By configuring the settings for the foreign key, you could configure whether UPDATE and DELETE statements cause cascading changes.
<br /><br />
However, the rows in these 2 tables are fundamentally different pieces of information and have separate lifespans.
</span>
<br /><br />
<span style="font-size: large;font-family: "arial" , "helvetica" , sans-serif;">
<b>NoSQL Perspective</b>
</span>
<br /><br />
<span style="font-size: small;font-family: "arial" , "helvetica" , sans-serif;">
NoSQL embraces the Object-Oriented perspective that the lifespan of a <b>contained</b> object is existentially tied to that of its root object (or document).
<br /><br />
In the example above, it would be wise to use <b>containment</b> to store the "Name", "Quantity" and "Price" of each <b>Item</b> in the <b>Sales Order</b> within the <b>Sales Order</b> itself.
<br /><br />
So the NoSQL document for this data might look like:
<br /><br />
<span style="font-size: small;font-family: "courier new" , "courier" , monospace;">
var salesOrder_001 = <br />
{ <br />
_id: "001",<br />
Name: "Order #001",<br />
Sales Agent: "Bob",<br />
Customer: "Chris",<br />
Items: [<br />
{ Product_Name: "Speedy Bike", Quantity: 1, Price: 60 },<br />
{ Product_Name: "Protecto Helmet", Quantity: 2, Price: 20 }<br />
]<br />
} <br />
<br />
</span>
As opposed to the SQL case, the <b>Sales Order Items</b> are existentially tied to the <b>Sale Order</b>, since they exist as contained data.
<br /><br />
If you delete the <b>Sale Order</b>, the <b>Sales Order Items</b> will automatically be deleted, as they exist within the <b>Sales Order</b>.
<br /><br />
Since this follows the way you would manage objects in an OOP language, many developers prefer this situation. In fact, NoSQL databases actually follow the <b><a href="https://martinfowler.com/bliki/DDD_Aggregate.html" target="_blank">Aggregate</a></b> pattern of Domain-Driven Design.
</span>
<br /><br />
<span style="font-size: x-large;font-family: "arial" , "helvetica" , sans-serif;">
<b>Importance & Implications</b>
</span>
<br /><br />
<span style="font-size: small;font-family: "arial" , "helvetica" , sans-serif;">
In this post, we explained how to use embedding to <b>contain</b> information within a parent document.
<br /><br />
For <b>containment</b>, the embedded information actually exists within the parent document, so no data duplication was required.
<br /><br />
However, to achieve the design goals of using a NoSQL database, you will sometimes need to <b>cache</b> data that actually exists elsewhere within your data model.
<br /><br />
<b>Caching</b> by-definition requires data duplication.
<br /><br />
We will talk more about this in our next post... How to use embedding to cache duplicated information.
<br /><br />
For more, see our next post...
</span>1Schema.comhttp://www.blogger.com/profile/01478882099267314745noreply@blogger.com0tag:blogger.com,1999:blog-2598353913463687108.post-67601279570963907232016-12-05T14:40:00.002-08:002017-01-12T13:14:52.152-08:00<meta name="title" content="Why use schema in database design?" />
<meta name="keywords" content="SQL,NoSQL,database,design,schema,implied,enforced,collaboration,communication,group,project,management" />
<meta name="description" content="Why use schema in database design?" />
<span style="font-size: x-large;font-family: "arial" , "helvetica" , sans-serif;">
<b>Why Schema?</b>
</span>
<br /><br />
<span style="font-size: small;font-family: "arial" , "helvetica" , sans-serif;">
Modern NoSQL offerings commonly promote themselves as being <b>"schema-less"</b>, as if this is a great feature.
<br /><br />
For most use cases, though, there is at very least an "implied" schema for the information stored. Just because the schema is not enforced does not mean that it does not exist.
<br /><br />
From the standpoint of 1Schema.com, we look at modern NoSQL offerings as instead providing <b>"optional enforcement of schema"</b>.
<br /><br />
For example, think of using MongoDB out of the box (no enforcement) vs. using MongoDB with Mongoose ODM (almost SQL-like enforcement).
<br /><br />
From a flexibility stand-point, the strength of NoSQL is actually more based on its ability to embed sets of nested information (which we will discuss in future posts). Still, this embedded information probably also has an implied schema.
</span>
<br /><br />
<table align="center" cellpadding="0" cellspacing="0" class="tr-caption-container" style="margin-left: auto; margin-right: auto; text-align: center;">
<tbody>
<tr><td style="text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhg2Hv8ey307sz3tJz91_ahAqIr7gamvbn2xuphrQy2duRBBa0k0nUfVve7ALpJuFbSONYNQi00kr6stl3wtOwonE3wKWidMnBAr82AyWzt4cvr27KesIbYPzTTkEIwDySQc1ZOXjEBDiM/s1600/ExampleSchema.png" style="margin-left: auto; margin-right: auto;"><img alt="1Schema schema" border="0" height="216" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhg2Hv8ey307sz3tJz91_ahAqIr7gamvbn2xuphrQy2duRBBa0k0nUfVve7ALpJuFbSONYNQi00kr6stl3wtOwonE3wKWidMnBAr82AyWzt4cvr27KesIbYPzTTkEIwDySQc1ZOXjEBDiM/s400/ExampleSchema.png" title="1Schema schema" width="400" /></a></td></tr>
<tr><td class="tr-caption" style="text-align: center;">This is a 1Schema schema for Sales Orders.</td></tr>
</tbody>
</table>
<br /><br />
<span style="font-size: small;font-family: "arial" , "helvetica" , sans-serif;">
<b>So next time you start a NoSQL project, as yourself:</b>
</span>
<br />
<ul>
<li><span style="font-size: small;font-family: "arial" , "helvetica" , sans-serif;">Do I care about collaborating with other engineers on the project?</span></li>
</ul>
<ul>
<li><span style="font-size: small;font-family: "arial" , "helvetica" , sans-serif;">Do I care about communicating what data is supposed to be stored?</span></li>
</ul>
<ul>
<li><span style="font-size: small;font-family: "arial" , "helvetica" , sans-serif;">Do I expect certain values to exist when I use the data?</span></li>
</ul>
<ul>
<li><span style="font-size: small;font-family: "arial" , "helvetica" , sans-serif;">Do I care about managing how data is duplicated within the database?</span></li>
</ul>
<br />
<span style="font-size: small;font-family: "arial" , "helvetica" , sans-serif;">
... For the last point, check back to see how 1Schema.com can help clarify how and why data is duplicated within your NoSQL database.
</span>1Schema.comhttp://www.blogger.com/profile/01478882099267314745noreply@blogger.com0