CODEWITHSUNDEEP
mysqldatabasedatabase-design
StackOverflowNewbie
StackOverflowNewbie

Reputation: 40643

Database Design: how to store XML/JSON in database?

I have access to an web service that returns an XML or JSON. Since web service calls are expensive, I want to store the XML/JSON in my database so that I can access the data faster. The question I have is if I should just store the entire XML/JSON in a field or should I design a database model that represents the XML/JSON in a normalized way?

If I just needed to have the XML/JSON data available to me, then saving it as a string in a field would be OK.

However, I know that I'll be needed to extract only certain XML/JSON documents -- so I kindda need to be able to query this. For simple queries, maybe I can use something like LIKE %<title>hello world</title>% if I was search for "hello world" between the title tags within an XML document. But I think some of my queries might go beyond string matching (e.g. greater than a certain number or date, etc.). So I feel like I need to model this data properly in my database and populate it with the values from XML/JSON. This will become a painful exercise, though.

Any tips on what I should do? Maybe there is an option I didn't consider.

Upvotes: 1

Views: 2466

Answers (1)

Brian Hoover
Brian Hoover

Reputation: 7991

It really sounds like you have the requirement to translate the XML/JSON document into a distinct set of fields.

There are DBs out there that can return native JSON, but you normally would use the application layer.

I normally store pure XML/JSON when there's really no obvious need to access individual fields. For example, I'll store control data in XML format in a BLOB field, since I don't generally need to search for one particular control string.

One thing that you might want to think about is using a noSQL solution like MongoDB or Couch to store the JSON. You can put the JSON string and pull it out directly as well as access the individual fields.

A document database like MongoDB and Couch gives you the flexibility to natively store and retrieve the JSON document as well as access and search on the individual fields inside the JSON document, without needing to in advance know how the document is going to be used. It is really just eliminating a couple of steps in converting relational data into a structured document and converting a structured document into relational data.

When I do store XML directly in a BLOB, any of the searchable data would be in a field outside the BLOB. Disk space is relatively cheap, and this is a minor de-normalization. Sure, you'll have to make sure to to keep the field updated whenever the JSON/XML document is updated, but that's easy to do at the application layer.

Upvotes: 1

Related Questions