Using a relational database vs JSON objects for event/activity data

Question

I am working on a project where I am trying to decide between using a standard SQL relational database or JSON objects to store data about an event or activity.

The project will store data on multiple event types so I have decided to just describe one event type for this question.

The live music event (described in full using the JSON schema at the bottom of this question) is an object that stores data such as where the event will take place, the time/date of the event and the cost of the event. The live music event object has both one-to-one (event--> name, event --> description) and one-to-many (event--> venues, event--> dates, event--> ticket types) relationships. Furthermore, the event object can contain one or more performer IDs, which link to the performer object. The performer object stores data on musicians who are performing at the live music event.

The data will be queried by users using both simple ("Find me events with 'x' name") and complex ("Find me events with 'x' music genre and 'y' cost within a radius of 'z' from my current location") queries. The data will be submitted by users using a web form.

As you can probably tell from the defined JSON schema, I was originally going to use JSON objects to store this data but I've heard from some people who say that because my data is purely relational, I should stick to the older methods.

I would appreciate any thoughts on the pros and cons of each approach given my needs. If you need anything clarified, please feel free to ask.

{
    "event": {
        "eventID":{
            "type":"string"
        },  
        "eventType":{
            "type":"array",
            "eventTypeItem":{
                "type":"string"
            }
        },
        "eventName":{
            "type":"string"
        },      
        "eventDescription":{
            "type":"string"
        },
        "eventVenueList":{
            "type":"array",
            "eventVenueListID":{
                "type":"integer"
            }
        },
        "eventURL":{
            "type":"string"
        },
        "eventTwitter":{
            "type":"string"
        },
        "eventFB":{
            "type":"string"
        },
        "eventInstagram":{
            "type":"string"
        },
        "eventEmail":{
            "type":"string",
            "format":"email"
        },
        "eventContactPerson":{
            "type":"string"
        },
        "eventDoorTime": {
            "type":"string",
            "format":"date-time"
        },  
        "eventPerformerIDList":{
            "type":"array",
            "liveMusicPerformerID":{
                "type":"integer"
            }
        },  
        "eventSetList":{
            "type":"array",
            "eventPerformerID":{
                "type":"integer"
            },
            "eventPerformerStartTime":{
                "type":"string",
                "format":"date-time"
            },
            "eventPerformerEndTime":{
                "type":"string",
                "format":"date-time"
            }                                   
        },
        "eventDateList": {
            "type":"array",
            "eventDateItem": {
                "type":"string",
                "format":"date-time"
            }   
        },
        "eventDateStartTime": {
            "type":"string",
            "format":"date-time"
        },
        "eventDateEndTime": {
            "type":"string",
            "format":"date-time"
        },
        "eventTicket":{ 
            "type":"array",
            "eventTicketType":{
                "type":"string" 
            },
            "eventTicketLowPrice":{
                "type":"number"
            },
            "eventTicketHighPrice":{
                "type":"number" 
            },
            "eventDatesAdvancePrice": {
                "type":"number"
            }   
        }
    },  
    "performer": {
        "performerID": {
            "type":"integer"
        },
        "performerType": {
            "type":"string"
        },
        "performerName": {
            "type":"string"
        },
        "performerAlternateName": {
            "type":"array",
            "performerAlterateNameItem":{
                "type":"string"
            }
        },
        "performerGenreList": {
            "type":"array",
            "performerGenreItem":{
                "type":"string"
            }
        },
        "performerURL": {
            "type":"string"
        }                                       
    }
}   

Answer 1

I think your question really boils down to: When should I use a NoSQL approach vs. RDBMS? You settled on JSON early (a NoSQL-ish decision), perhaps because you've got Ajax consumers.

The answer of course to when to use NoSQL approaches vs. RDBMS's is basically about what type of data you're working with and what consumers you anticipate having. If your data is essentially relational (fairly flat hierarchies, no weird data types like images or audio, predictable relationships between the schemas that can be easily described in keys), and your consumers are anticipated to eventually include people who want to do Business Intelligence queries (ad hoc querying), then an RDBMS is the way to go. It's fairly easy to turn a query into a JSON representation, so it doesn't significantly burden your Ajax consumers -- it just adds a little transformation coding into your endpoints (REST/SOAP/whatever). Conversely, if your data is very hierarchical (deep schemas), contains weird data types like images, audio, video, etc., there are few relationships between entities, and you know that your end users will not be doing BI, then NoSQL/storing JSON may be appropriate.

Of course, even these general guidelines aren't rock solid. The reason Google developed Google File System, MapReduce (work which was used by Doug Cutting to build Hadoop at Yahoo) and later BigQuery (a NoSQL oriented [schemaless] way of managing large scale data) was precisely because they had a lot of ad hoc BI requests, and they couldn't get relational approaches to scale up to the tera/peta/exa/zetta/yotta scales they were trying to manage. The only practical approach was to scale out, sacrificing some of ad-hoc-query user friendliness that an RDBMS provides, and substituting a simple algorithm (MapReduce) that could be coded fairly easily for any given query.

Given your schema above, my question would basically be: Why wouldn't you use an RDBMS? I don't see much of a reason not to. Our profession is supposed to be engineering oriented, not fashion oriented, so our instinct should be to pick the easiest solution that works, right? I mean, your endpoints may have to do a little translation if your consumers are Ajaxy, but your data looks very flat and it seems likely that business users are going to want to do all kinds of ad hoc querying on things like music events (Which event was most attended within 50 miles of our capital city last year?)

'Go not to the elves for counsel, for they will say both no and yes.' -- Frodo

Answer 2

I believe there are more considerations here that you may not be looking for. There are two broad concerns here:

• Storage
• Search and Retrieval

Storage

There are plenty of opinions on why to use no-sql or RDBMS store for your data. One of the most important items that we thought was useful is that we can easily define and store json objects in storage without having to worry about defining it's full structure or relationship between different types of objects. Some of the other reasons to use a NoSql db would be ability to auto shard data, location based searches and easy maintenance. There are many good NoSql databases out there, my personal preference is MongoDB. However, if you have not used NoSql database before, there is a definite learnign curve as you learn to re-wire your mind. Most of us have been using RDBMS for a while now and it takes conscious effort to break out of that habit. Plus you will find yourself wanting to redo your data model as you proceed along with your efforts and have a better usnderstanding of concepts. If ability to refactor or remodel is not an options for your project I would suggest to stick with what you already know best.

Search

If you intend to provide any kind of search that is usable, I strongly suggest that you use a dedicated text search engine such as SOLR to perform your searches. Text searches are slow and if you have multiple shards then even more so slower. SOLR supports blazing fast text searches including weighted search params, location based searches and much more. SOLR however is not suited as a primary store of your data. This does mean that you will have to create mechanisms for dual insert and update to both you primary database and your SOLR layer when adding or updating events. Plus you will have to keep the SOLR later updated by removing any outdated/ended events.

Although this does seem like lot of extra work you will thank yourself for foresight of using a full text search engine later on. None of the NoSql databases or RDBMS come close to performance and agility of SOLR/Lucene.

Answer 3

First, if you're trying to Store JSON data in any storage but not a NoSQL database, I'd definitely discourage you to use JSON. The reason is that if you store your data as a JSON file, for example, then it will be extremely slow to open it, parse it, loop through it, etc.

That begin said, I can narrow your question to: What are the pros and cons of NoSQL and RDBMS? And it has been already answered thousand of times on the 'net.

Regrading your project, you can of course use either NoSQL or RDBMS; However what I can generally recommend to you is to think out of the box and look for the other less-visible factors that might help you decide between the two options. Try to see which option could speed up the development? Which is more suitable for the other team members - if you are not a sole developer. If you're selling this, which one is cheaper, easier and generally more suitable for your non-developer customers?

In this way you can finally decide which way to go, otherwise it will be really hard to decide based on the given information as both options could fit quite well.

Answer 4

In most applications there are requirements to

1. Input data, perform some processing, save the data,retrieve the data and query the data. There may also be a requirement to generate reports on the data.
2. Exchange data between different parts of the system or with external systems

In order to achieve the requirements for Item 1 a method of persisting data is required. Typically if the volume of data is very small and the type of data is simple and does not require extensive search capabilities then a simple file structure may be used. As data becomes more complex a XML (or even JSON) structure may be used with the data still stored in files. Searching though becomes more problematic. As the volume of data increases and the complexity of searches increases a database is normally selected which provides industry standard methods for data persistence, querying etc. The databases can designed to handle large volumes of data and store,retrieve and search the data quickly and efficiently.

In order to achieve the requirements for Item 2 there are various different methods for allowing data interchange between systems including XML, JSON etc.

These methods allow the data structure to be defined by a user and are language independent allowing dissimilar system to exchange data.

In your particular case you are correctly using JSON is describing a set of music events. While you could store the data in JSON format searching this data as the number of music events grows will be slow and inefficient.

Using a separation of concerns approach then better approach is to collect the data, store in a database, perform your query based on user input in the database and then return the results in JSON format to the Client side to display the data.

An additional problem with the JSON approach is a changing data structure. Currently your structure is relatively simple. You may use this structure for several months and then an additional field is identified. What do you then do with all of your existing JSON objects ? Updating these would be problematic.

If you used a database then adding an additional field is relatively simple and only your code to generate the JSON would need to be modified in a single place thus giving you all new JSON with the new field.

In short use each piece of technology for what it was designed for JSON for data interchange and a Database for data persistence.

Answer 5

I think you will have better success with using NoSQL than SQL for storing this data, because of the queries you need to do.

Also just because some data is purely relational does not mean, anymore, it must be persisted into some RDBMS (SQL). IMO relational data would translate better into graph databases.

Of course you can also write the queries in SQL but the performance will get terrible because of the number of joins you will need to have (considering your data will be somewhat normalized and not all of it into one Event table).

But in conclusion you will have more freedom by using NoSQL (thus JSON or some other format supported by the database) considering you can modify your schema in the future without taking into account already persisted data.

Considering NoSQL you could also look into graph databases if you plan to use very complex queries, since these will give you advantages in creating them easily, and also executing them very fast.

Answer 6

I think you should use both and I don't see it as a 'versus' decision.

A relational database makes sense for fast and efficient storage and retrieval of data that has relational properties.

JSON is a great data format because it is simple, lightweight and ideal for passing around raw data in a very basic format with a syntax suited to storing and exchanging text information. It's great for passing small amounts of data between a browser and a server. It's not in such an easy format to start using for relational type data queries.

So I would recommend SQL for the data storage and JSON for the data transport format.

It is true that there are noSQL key-value options such as Mongo, Redis, etc. These would have the advantage of possibly simpler mapping to the JSON format but are generally a little harder to use for queries. The main hurdle with them is unfamiliarity by the general IT community especially when compared to SQL which is so well known and hast a vast array of resources and knowledge available for almost every situation imaginable.