asp.net CMS solution: best mechanism for storing the data in SQL

Question

I've worked on a number of CMS systems using the .NET platform (the CMS management system is in ASP.NET, and the site which renders the content is both ASP.NET and PHP).

I've traditionally stored the generated content in classes that serialize to XML, which is stored in MSSQL 2005/2008 in a varchar(max) field. This has made it convenient structuarally for different consumers of the content to work with, and it can be bound to classes (for re-opening the CMS record and editing it, or for the consuming website).

However, I've been wondering what type of storage mechanism is popular with other CMS shops, and if anyone likes or has serious issue with the approach I'm most familiar with.

Good, bad, ugly? What would you do?

Answer 1

I personally treat the data as any other data that would be stored in the system, for example I have a module built for DNN that stores collection of text data, I have the following collection of columns in a table for it.

• EntryId
• UpdatedBy
• UpdatedDate
• CreatedBy
• CreatedDate
• SortOrder
• Content (NTEXT)
• Hidden (Bit)

I find that this keeps the data easy to find, and it is easy to manipulate regardless of the caller.

Answer 2

In our CMS we use a similar structure to the one Mitchel Sellers describe, but we separate the content in its own table. This is useful when optimizing the database and allows us to easily share the functions for versioning of content. We then have one table per "entity type" like documents, products and so on, where we describe the entity.

Answer 3

We have an ASP.NET CMS as our main product so I'm familiar with the problem. It depends what you mean by "generated content". If you mean user-entered, then we store HTML as ntext and other page elements using a table structure that allows us to have a flexible set of fields per page element (some are configured using one or two fields, others have dozens).

The main benefit of this is that the data in the database is representative of what is actually entered by the user. Storing XML in a SQL database is just adding a layer of re-direction that we don't need. You might be trying to get SQL to act like an OO store, which is a typical problem that ORMs help solve.

One problem you may come across is difficulty in re-using existing SQL search mechanisms with XML data.