Is it ok to use a XML column to store extra data?

Question

If it ok and recommanded to use a xml column to store any extra data that the user interface might provide?

For example, suppose an Employee table

CREATE TABLE Employee
(
    EmployeeId int not null,
    Name nvarchar(300) not null,
    Phone varchar(30) null,
    Email varchar(320) null,
    Address nvarchar(max) null,
    Data xml null
)

Data could contains many values like additional phone numbers, comments ...

We expect that all our customer will be asking for different fields in Employee, and we do not want to mess with the database structure every time they think of a new field to add.

We expect the data stored in the xml column to be infrequently accessed data. Beside being vieweable while browsing the list of employee, the data might need to be printed. So we do need to store the data type along the data (a little like a dataset is serializing its data)

Is this is a good approach for storing unknown extra data?

Edited Gave a better example

Update I haven't selected an answer yet because I'm discussing with my team of the different approach you guys suggested.

Answer 1

You can do it like this:

[EmployeeField]
-----------------------------------------------------
EmployeeID    EmployeeFieldName    EmployeeFieldValue

It's in fact the same approach .NET Membership uses for user profiles with dynamic fields. Many business applications use the same approach to store dynamic customer-specific inputs.

Credited to Spencer Ruport's comment, this approach is known as the Entity-Attribute-Value model (EAV).

Answer 2

Depends on what the data is. If it is not easy to model in a relational structure, then what you propose (it is really just a Serialized LOB) is OK.

Answer 3

You are cutting yourself off at the knees using a relational database model for this. Relational databases are built around the idea of a static structure of data and being able to query easily and quickly. Even adopting a EAV structure as "New in town" suggests goes against this, though it's arguably better than a simple XML data dump into the column.

If customer is an oddity and the rest of your data is OK, then it's probably fine to do this, though I would definitely go with the EAV approach. If most of your tables look this way, then it's time to rethink your approach to data storage.

Answer 4

I personally prefer to add extra, unknown data as a separate table. This allows you to allow an infinite number of options (just have ID, Name, Type(? - optional), and Data columns), but provides the added benefit of allowing selective updates/deletes.

If you do it as a single data blob, any time you change any single portion of the aux data, you'll have to replace the entire data field.

Answer 5

If you want to use the data in a query, I wouldn't recommend putting it into an xml field. Yes, there are ways to query data from xml fields, but I've not found them as effecient.

Answer 6

Yes, it is OK. Whether it is recommended depends heavily on your specific situation. We do something similar using Oracle and the flexibility is great but our custom web framework (Java servlet that produces web pages/apps based on modules written in XML) makes heavy use of XML and we have specific systems in place to deal with storing data in a single XML column so my opinion and experience is based on scenarios where it is convenient.

For example (and I know this might sound horrid to people), if you are going to be searching based on data inside your XML this might initially pose a problem because running XPaths and extracting data from the XML on every row every time you search is a massive performance hit. We have a system in place similar to materialised views which utilizes triggers and a stored query. Any time a row in the basetable (containing the XML column) is altered, the triggers fire, your query is run and extracts data out from the XML and inserts it into a relational table which in turn has a view over it to ensure you don't modify the relational data (since this does not reflect back into the XML).

For the purposes of something like large numbers of web based forms doing CRUD operations where the schema and thus data model may often change it is fantastic. It means you could pull out your XML fragment and instantly have the underlying model for your page and saving it is as simple as simply sticking the XML fragment back in. For fast read-only access we have instant access to a relational view of the XML.

Answer 7

I assume you'll also have to query and search for Employee attributes in the XML.

It can be OK if you use an XML schema and create necessary XML indexes. You can also use a EAV relational model and will probably be more performant. You must read the Best Practices for Semantic Data Modeling for Performance and Scalability white-paper if you are going to implement an EAV model, this white-paper written by the SQL Customer Advisory Team deals with common pitfalls and problems that occur with badly designed EAV models.

As a side note, storing only ID and NAME for an entity as rich in attributes as an employee sounds like you're really ditching the ball short of the goal. You should at least add the common attributes you will expect your majority of customers to use and only rely on extensible models (XML or EAV) for the extra attributes that you can't forsee.

Update

Since we're at it, here are the SQL CAT white-papers on XML best practices too:

• XML Best Practices for Microsoft SQL Server 2005
• Performance Optimizations for the XML Data Type in SQL Server 2005

Answer 8

Yes, provided the extra data does not “link” to any other table, and you don’t need to often search on it. If you will every have more then one user editing different parts of the extra data for the same employee, then think very carefully about your design.

So if your extra data is just a “notes” fields, and happens to have per customer predefined headings then it is ok.

There no advantage to putting this extra data in a [EmployeeField] table, as it just makes the data access code more complex without enabling you to take advantage of the power of the database.

Rather the storing the type with each data item, I think you should have a meta data table that stores the type, name, and “display name” of each bit of extra data a given customer wishes to have. You may also need to store the form layout the customer wishes to use to enter the extra data.