Enterprise Software

Two ways to design a database for a .NET-based CMS

A key aspect of a CMS is its backend database. Take a closer look at two of the design options available for your database: the fixed data model and the template data model.

When developing a .NET-based content management system, there are a many ways to logically set up your database. However, in most cases, one of two approaches will work. The first, the fixed data model, uses the approach that the content you will display will always contain the same data elements. This approach works well if you are modeling your CMS after a newspaper or magazine, and it's easy to develop. The second approach, the template data model, is a template-driven system where the data elements are chosen based on the Web page template selected. This system provides more flexibility.

Let's take a look at how both of these approaches will work with a simple CMS that displays news articles for a company Web site.

Getting started with your CMS
I previously explained two underlying architectures: 3-tier and n-tier. For this article, I will be concerned with the 3-tier architecture. (In later articles, I will explain how to convert the system to an n-tier architecture.)
I'll also discuss database development using Microsoft SQL Server 2000. However, most of what I cover can be applied to almost any database. The biggest difference you'll find is that the data types aren't the same, but you should be able to find an equivalent one for your chosen database.

Fixed data model
Using a fixed data model approach requires a little more upfront work, because you need to make sure that you contain all the Content entities required for your CMS. But once you've completed this legwork, creating the logical database is quite straightforward. Figure A shows an example of a fixed data model for a CMS.

Figure A
Fixed data model

The core table in the fixed data model is the Content table. It contains all the Content entities of your CMS. For my news site, these entities (modeling a newspaper) are Headline, Source, Byline, Teaser, Body, and TagLine.

Because I want the system to support a role-based workflow, the Content table keeps track of all accounts that handle the flow of the content. The workflow of the content will be authoring, editing, approving, and deploying.

You'll probably also want to use version control. I took the easy way out and just kept a complete copy of each version of the content in the database. You may want to explore just saving deltas (i.e., just the changes between versions).

This database design provides flexibility for the data you can store about accounts. It only stores the required data for an account in the Account table. It then stores any additional information about the account as a property associated with the account ID. This way, you can add almost any information about the user that you want to the database. For example, to add a home phone number to your account, you would add to the associated account ID a property of Home Phone with a value of 800-555-1234 (or whatever the phone number is) to the AccountProperty table.

With this design, each account can have multiple roles. For example, an account can be for an author and an editor. We'll be able to use these roles when we add a workflow to the simple CMS. However, you'll want to develop your workflow based on roles instead of accounts, because the roles of an account can change with time.

Another useful design feature of this database is Notes. Since it is very possible that author and editor could be on different continents, communication could be difficult. (Differing time zones can wreak havoc, believe me!) Using the Notes facility allows easy communication between the different roles in the workflow. Content Notes are associated with content ID; they ignore the version of the content.

The design allows you to display the same content in more than one zone on your site. But you could probably remove this feature if you have few content zones. The same content can also be displayed in different zones for different amounts of time. For example, you might have a breaking news zone that will keep a story on the zone for only one day. Instead of losing the story to the archive after one day, however, you could place the story in another zone to be featured for a longer period of time.

Template data model
With the template data model, the Content entities can be determined at a later date—when they need to be implemented. There is no hard coding of the entities as there is in the fixed data model. Still, the template data model bears many similarities to the fixed data model, as you can see in Figure B.

Figure B
Template data model

The major similarity between the two models is how they both handle accounts, or users, as they are called in the template data design. They work in exactly the same way. There is the additional role of template builder, but that is an addition to the contents of the database and not the database itself.

It is also similar in that the Content table is the cornerstone of the database. But, in this case, the content of Content table differs substantially from that of the fixed data model. In this model, the table serves as a linchpin between the Web page Template, the Content entities, Notes, and the Zone for which it will be displayed.

With this design, there is a new role of template builder. This role designs the Web page and then puts placeholders into the template where Content entities will be placed. The workflow for the creation of a template mirrors that of the creation of Content entities for authoring, editing, approving, and deploying.

As was pointed out, Content entities are no longer stored within the Content table; they now have a table all their own called ContentNode. In this model, you can place the same content into multiple templates and in multiple Web pages. This permits greater reusability. It also allows more than one author or editor to work on the same Web page, as each Content entity (node) is self-contained and has its own workflow. However, the approval process will probably be based on a complete Web page and not on its individual nodes—although this is not a requirement.

This is the basic flow for building a template data model Web page:
  • The Web page templates are created, edited, and approved.
  • The author selects a template that contains the correct type and number of content nodes to handle the Web article.
  • The author(s) create all the content nodes.
  • The editor(s) edit the content nodes.
  • When all content nodes are ready for approval, they are forwarded as a completed content page (i.e., template, content nodes, and content controller) for approval.
  • When approved, the content page is deployed.

Wrap up
I've given you a basic idea of how you can design your database. Remember, if you have a fixed number of content nodes, you can use the fixed data model. If the nodes are numerous or change from document to document, the template data model would be a better choice.

In my next article, I'll look at how to implement ADO.NET to access the database data.

Editor's Picks

Free Newsletters, In your Inbox