Blogs

How to create a more flexible relational environment

Program Director, Core Database Technical Sales, IBM

For years, the relational database has been the stalwart backbone of organizational operations.

Its set structure of rows and columns is perfectly suited to capture the data being generated from daily transactions reliably and efficiently. However, more organizations have recently been looking to embrace greater flexibility in their data management environments through NoSQL databases to better meet the demands of a rapidly changing marketplace. This has led many to ask whether there is an ideal solution that offers the flexibility of NoSQL without sacrificing existing investments in skilled people and technology tied to relational databases.

Why relational databases remain important

Because relational databases were made to accommodate high volumes of structured transactional data, they have typically had equally structured table designs and querying processes. Users needed to start with a well-defined plan that took into account all the information that had to be captured because tables are defined early on with column names, types, keys and numerous other settings.

Unfortunately, due to its highly structured nature, changing the table design can often be complex or impossible, requiring a complete rebuild. Not only does rebuilding the table take time and delay the speed at which data for new fields can be added and queried, but also those delays can increase based on how well developers coordinate the changes with the database administrators (DBAs).

Why NoSQL is rising in popularity

NoSQL databases are drawing considerable interest based on their reputation for flexibility. They enable users to dynamically change the structure of the data being stored and queried. While a large number of fields will remain static, such as a unique key (customerID) or identity information (lastname, firstname), NoSQL databases allow additional ad-hoc fields such as nickname or preferred name. If one of those fields isn’t required for an entry, it is simply not placed into the record, with no complex table rebuild required.

Still, NoSQL databases are not the perfect solution because such an approach ignores the considerable amount of investment into relational database technology that companies have already made. Organizations have spent time and money to hire and train individuals with a relational database skillset and knowledge base. Moreover, some organizations worry about turning away from the secure, reliable and high-performance environment to which they have grown accustomed.

Creating a flexible relational database

Presented with a choice between more flexibility and using the skills in which they had already invested, data architects were clamoring for a better option. With that, in late 2016, the International Organization for Standardization (ISO) published an update to the SQL standard that included new SQL functions to store, retrieve and publish JSON data within a relational database. 

The combination of relational databases with the flexibility of JSON is promising, and major relational databases such as IBM Db2 are already supporting it. One of the most exciting elements is that existing applications can be modified to accept new data without having to rebuild the existing tables. In addition, storing JSON data locally avoids having to extract and reload data from other systems.

How to get started with JSON

There are two approaches that can be used when integrating JSON into a database for greater flexibility: the first is to place everything into one column of a relational table and use JSON search and publish functions to extract what is required. Another approach is to have the static fields defined using standard SQL data types and then create one column to store any sparsely used values.

Using this approach means that queries against the static fields can be optimized using indexing and other techniques, while values within the JSON column can be extracted or searched when necessary. Databases such as Db2 even include the ability to index fields within JSON columns. This means you can index rarely used values within a JSON document if you need quick access to them.

Increased flexibility is just one element of up-to-date data management. To discover more ways to enhance your data management strategy, check out our latest eBook, “Six Reasons to Upgrade your Data Management” or explore Db2 at your own pace with several demos, tutorials and trials.