Is this a "overkill" design to store XML data in a relational database? One point is that these web API REST services can later be used by other applications.

enter image description here

  • 2
    This could use some more detail. But it is better than the contents of one question at Stack Overflow, deleted years ago – user22815 Aug 4 '17 at 15:05
  • Yes, well it is the overall design here that I am unsure of. Like what is the common practise in receiving xml documents and save content to a db with use of webapi. In my case the dataset could be relative large. However process time is of no relevance. – Hasle Aug 5 '17 at 9:19

It depends on your data complexity and volume, and the importance of this "data ingest" or ETL (Extract, Transform, Load) service.

For very large datasets (100K or more of inbound XML-encoded objects), which will keep coming in over time, and which are an important part of a business process, you could argue this is "suited to the job."

For most "load the database" jobs, however, it's vast overkill. I've run multi-million record ingest systems with parallel workers yet no splitter service, only a simple queue, and no segmentation of the API type. The number of different kind of entities you have described here seems to go against Occam's Razor, which is often a wise guide for those building software systems.

Beyond the probable over-complexity of the pipeline, several specific concerns:

  1. Do you really want to deserialize from XML only to re-serialize in JSON? Presumably you are not storing that JSON directly in a document-structured DBMS nor verbatim in a relational text or blob field. You're going to have to deserialize the JSON yet again, making for two semantic hops. Now, JSON's a good format in general for Web APIs, but XML is also perfectly cromulent for Web API workers to ingest directly. Not clear why there should be so many preparatory steps before those API workers. Seems likely the preparations greatly increase the opportunity for errors to be introduced.

  2. The idea of splitting XML documents into separate entity types seems risky. Documents that serialize data often share a common context (related entities, implied relationship consistency, or entities that need to be added to the datastore atomically). Splitting so early in your pipeline and recombining later is fraught with opportunities for introducing errors and semantic mismatches. You would need to be extremely careful not to do so if your documents have any relationship complexity to speak of. And by splitting serialized documents so early, you would be ignoring and declining to use the facilities that are built into every last relational DBMS for managing such consistency requirements. While not impossible to imagine a situation where that would be a legitimate choice, it's much rarer than the simpler alternatives.

Update: Here's a diagram of what a simplified process might entail:

| improve this answer | |
  • Thank you very much for the answere. I will make some changes here. The datasets are large, but it is not critical if it takes a lot of time. – Hasle Aug 5 '17 at 9:22
  • One issue, if the step 3, call to webapi service 3, fails. The step 1 and 2 have already finished updating the db. So there is no way rolling this back. Or is there a solution to this problem? – Hasle Aug 5 '17 at 9:31
  • @Carlson Atomic rollback is one of the key reasons you might want to have your DBMS writers (the boxes labeled "Web Api RestService <n>" in your diagram) read whole XML documents, not entities split out previously. That way, if an error occurs during their update transactions, they can be rolled back wholesale, as a unit. – Jonathan Eunice Aug 5 '17 at 13:03
  • @Carlson I added a diagram to make the simplified process I use more clear. – Jonathan Eunice Aug 5 '17 at 13:39

Not the answer you're looking for? Browse other questions tagged or ask your own question.