The Design of Software (CLOSED)

A public forum for discussing the design of software, from the user interface to the code architecture. Now closed.

The "Design of Software" discussion group has been merged with the main Joel on Software discussion group.

The archives will remain online indefinitely.

Store XML in SQL Server Text Column?

Here's my scenario: I'm working on the design of a new system that retrieves three different kinds of widgets from three distinct systems. More systems with different types may be added soon (company merger). The widgets share about 75% of the same attributes.

The widget data will be merged with templates created in Word to produce customer letters. This data is read-only in the system but will need to be viewable.

At this point my design is leading towards one Widget table with a text column to store the XML representing the widgets. The table would also have a unique ID identity column and a few columns of common widget attributes for use in filtering.

The alternative, of course, is to create a Widget table and a number of related widget attribute tables. Additional tables, one per widget type, can be created to store the unique attributes.

Using the text column approach, the Word merging process reads the XML into a .NET DataSet and populates the document's merge fields (using the Aspose.Word library) to produce a customer letter. Using the multi-table approach I need to write queries to produce the same DataSet. Not difficult at all but the other approach seems more straight forward.

To date I have not used a text column to store XML or BLOBs but I'm drawn to this design because of the various types of widgets that need to be stored.

Any opinions on this one?

BTW, I'm using SQL Server 2000.
Weef Send private email
Tuesday, August 23, 2005
If there's ever even the slightest potential for wanting to query the data that's stored in the XML then it's a bad idea. You've obviously spotted the pitfall because you're going to split out some attributes into separate columns for querying.
mutabled Send private email
Wednesday, August 24, 2005
I agree with mutabled. Sooner or later you're likely to want to query the widgets in some way. As soon as you've copied some of the data that's into XML into other columns, your database structure is redundant. Redundancy is a source of bugs, and should be avoided unless the price is too high.

It's probably better to decompose the widgets into an approprate database structure. Then you can feed that straight into the process that does the merge, or generate XML from it if necessary.
comp.lang.c refugee
Thursday, August 25, 2005
I agree about the redundancy but the XML in the text column makes this design very flexible. With an impending merger, we'll be taking on more, different formatted widgets. These widgets will have the same common attributes (industry standard) but most likely will have yet another set of distinct attributes. I'd rather have to add a new common column every now and then than have to create new table(s) when a new widget is introduced.
Weef Send private email
Thursday, August 25, 2005
In case you didn't know already, this page describes the various issues:

Short version: from what you explained about your actual scenario your idea seems to be the right one; if searches are limited to a small subset of fields and the rest of the document may change relatively often, trying to mimick the XML hierarchy in the RDBMS is not a good idea.
Paolo Marino Send private email
Friday, August 26, 2005

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics
Powered by FogBugz