![]() ![]() I do a substantial amount of work in healthcare and insurance systems. Use Case for JSON in an RDBMSįirst, I’ll start by describing the use case that I’m going to leverage for this series. I’m actually going to start with Microsoft SQL Server because the work I’m doing right this moment is more focused there, so I decided to start with what’s top of mind. I figured that others might have similar situations, so hopefully you find this series useful. Then I read about PostgreSQL adding the json and jsonb data types and in my particular situation it makes perfect sense to utilize these structures. I was dreading having to dive in, configure a new server, and complicate my deployment footprint. I decided to do this because I recently did run into a situation where I was contemplating using a MongoDB as a data store for an application that already runs on PostgreSQL. I’ll cover MS SQL Server, PostgreSQL, and MySQL. I’m going to do a three part series on using JSON in relational databases. ![]() It’s important to use the right tool for the job at hand, otherwise you may come to regret it later and find yourself migrating a bunch of data from an RDBMS table over to a MongoDB instance anyway. Give some clear thought to how you will utilize the data that you’re storing and under what conditions you may need to access it. Just because you can’t doesn’t always mean that you should. One caution that I will add at this point is that you should make sure that you’re very intentional in making decisions like these. This means that you can now contemplate using a single database platform to solve for scenarios where JSON documents are needed as well as places where a relational structure makes more sense. This means that in SQL Server, PostgreSQL, Oracle, and MySQL you can now store and interact with JSON documents that are stored in a table, right there among your other columns. And then there’s the matter of sharing data between the two.Īs a result of the increasing market share that NoSQL databases have started to capture, most SQL database platforms have responded by adding functionality to support storage of and interaction with JSON documents as a built-in feature. You’re also going to need to deploy instances of each in your non-production and production environments. You need people who can administer both and people who can develop with both. The problem here though is that in order to support this, you’re increasing the level of complexity in your environment as well as the costs that go into it. I have seen some companies who take a hybrid approach – deploying an RDBMS like PostgreSQL to handle certain functions while other data sets are stored in MongoDB. As we know, these have their own pros and cons. At the same time, many companies are still bound to their traditional relational SQL databases. The concept of being able to store data as a document is a powerful idea in an age where it seems that everything is becoming more connected through the power of REST APIs. In the meantime, I became curious about working with JSON in a relational database, like SQL Server. But I have yet to build anything substantial using this particular technology, so it’s on my list of things to dive deeper into in the coming year. I understand them conceptually and have worked hands-on with MongoDB and AWS DynamoDB in some learning and exploratory environments. I have to begin by admitting that I’m no expert with NoSQL databases. Working with JSON in an RDBMS – Part 1, MS SQL Server ![]()
0 Comments
Leave a Reply. |
Details
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |