There are a couple of answers on SE comparing single-row table vs. name-value-pair table used for config settings, but I haven't seen anyone arguing for using XML (or JSON) as a name-value pair model.

Option A

UserID     AppID     ParamID    Value
1          1         10         5.40
1          1         11         John Smith
2          1         10         4.40
2          1         11         Jane Smith

Option B

UserID     AppID     Settings
1          1         <params><param><ID>10</ID><value>5.40</value></param><param><ID>11</ID><value>John Smith</value></param></params>
2          1         <params><param><ID>10</ID><value>4.40</value></param><param><ID>11</ID><value>Jane Smith</value></param></params>

Option A is plain key-value pair solution, and Option B uses only one serialized XML settings per user/app.

Is there any value in using XML instead of a plain key-value pair?


I'll refer to option A as the "structured" approach, i.e. the one where the structure of the data is known to and understood by the database, and option B as the "blob" approach, where the data is just an opaque blob that the database knows nothing about.

The main things to consider are:

  • Storing this data in a structured way allows you to perform queries on it directly. Do you expect to have a use for queries like SELECT UserId WHERE ParamID = 10 AND Value > 5 at any point in the future?

  • The "structured" approach forces your data to fit into this particular relational database table. If this data was in "blob" form, then it would be entirely up to you what shape the data takes. In the case of XML blobs, you're imposing a tree-like shape on your data, which is significantly less restrictive than rows in a single relational table.

  • If you store the data in blob form, then the structure of your data blob is not dictated by the database schema. This should mean it has a separate schema (in your case, an .xsd file somewhere). That means you can change the blob's structure without migrating the whole database to a new schema, but it does mean more schemas/versions to keep track of.

So which one you use depends on how much you'll want to write queries that can "see inside" this data, how likely it is that you'll need the flexibility of the more freeform structure, and whether having two schemas to worry about is a net benefit. Where I work, some of our data is structured, and some of it is "blobbed". It depends.

  • 1
    Finally...a real answer. All expected and well explained scenarios. From my experience storing config settings in XML format allows queries to be performed rather quickly (although querying config settings is usually not a top priority since settings are..... settings). Main negative aspect of this approach is that you need to update the entire entry in order to update a single parameter (which can potentially lead to data loss). How would a structured approach handle need for multiple values entered for one parameter? (comma delimited or xml, both not dictated by database schema)? – Igor F. Dec 27 '15 at 19:26
  • @IgorF. Settings with array/object values would have to be represented by additional database tables to make their content fully queryable. Going that far is usually past the point of diminishing returns in my experience. – Ixrec Dec 27 '15 at 22:06
  • What do you mean by "past the point of diminishing returns"? – Igor F. Dec 29 '15 at 23:23

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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