Using an XML Column for Sparse Attributes
For the special case of hybrid classes, the database/XML guru Dejan Sarka, in the very instructive text “Inside Microsoft SQL Server 2008: T-SQL Programming” (Itzik Ben-Gan et al., Microsoft Press) illustrates that XML can be used as an alternative to EAV. This is not surprising: XML is a kind of attribute-value data representation that is based on structured text rather than relational tables. The high-end RDBMSs support XML as a native data type (including the ability to associate a specific XML schema with a specific column for validation purposes).
Sarka’s solution depends on first defining a temporary table with all the columns that you would consider using EAV for, associating each column with the necessary validation constraints (which are defined using your vendor’s dialect of SQL) and then using your database’s XML-schema-generating capability to define an XML equivalent of this table. Then this table is dropped, and the physical table that forms the core of your hybrid class is modified by adding an XML column, whose associated schema is then based on the one just generated.
This solution is undoubtedly much more CPU-efficient than the EAV approach for displaying the content of a single object – it is not scattered in multiple type-specific tables, but is right there in the same row of the physical table. While the contents of an XML column can be indexed, the indexes are nowhere as efficient or compact as those for traditional columns, so that in circumstances where cross-object query based on the sparse attributes would be important, it would arguably be less efficient.
However, the XML is only an alternative storage mechanism: this solution does not bypass the need to describe the sparse attributes in metadata. It does not solve the user-interface generation problem (including presenting the data in a friendly fashion and allowing its editing through forms). By the same token, without metadata, it does not address the issue of validating the user’s input and providing end-user-comprehensible error messages (as opposed to the non-informative computer-ese that would be emitted by default if the XML content failed validation).
A programming issue that you need to be aware of is that, when you are retrieving heterogeneous data using a SQL generator that considers how individual classes are represented, you will have to generate XQuery code instead of SQL. Code generation may be considerably more complicated unless you use a standard design approach for structuring your XML. Further, individual vendors’ implementations of XQuery vary with respect to which features of the XQuery 1.0 standard are/are not supported. Finally, while an implementation will allow you to access the contents of non-XML columns in your XQuery code, the functions that will let you do so will necessary be vendor-specific.
Microsoft SQL Server Sparse Columns
Another approach to modeling hybrid classes is Microsoft SQL Server 2008s sparse column mechanism. Columns with a basic (string, numeric, datetime) data type (e.g., numeric, varchar or datetime columns) can be designated as sparse: up to 30,000 columns per table can be created this way. Sparse columns take up zero space for NULL valuesm and only rows containing values are indexed. When listing the contents of a table containing sparse columns with a “SELECT * ” statement, all sparse columns’ contents are concatenated into a chunk of XML, where each value is sandwiched within open-and close-column-name tags.
Once again, as in the case of XML, sparse columns are only a storage mechanism. Do not delude yourself into thinking that they will save you the task of defining metadata: as a matter of fact, SQL constraints (i.e., any validation other than type checking) cannot be defined on sparse columns (simply because Microsoft says so). Unless you define such constraints in metadata, and implement them in software, using sparse columns “as is” is an invitation to trouble. In fact, I consider the current implementation of sparse columns as a poorly-conceived design trap for the naive.