How should I model the database for this problem? And which ORM can handle it?
- by Kristof Claes
I need to build some sort of a custom CMS for a client of ours. These are some of the functional requirements:
Must be able to manage the list of Pages in the site
Each Page can contain a number of ColumnGroups
A ColumnGroup is nothing more than a list of Columns in a certain ColumnGroupLayout. For example: "one column taking up the entire width of the page", "two columns each taking up half of the width", ...
Each Column can contain a number ContentBlocks
Examples of a ContentBlock are: TextBlock, NewsBlock, PictureBlock, ...
ContentBlocks can be given a certain sorting within a Column
A ContentBlock can be put in different Columns so that content can be reused without having to be duplicated.
My first quick draft of how this could look like in C# code (we're using ASP.NET 4.0 to develop the CMS) can be found at the bottom of my question.
One of the technical requirements is that it must be as easy as possible to add new types of ContentBlocks to the CMS. So I would like model everything as flexible as possible. Unfortunately, I'm already stuck at trying to figure out how the database should look like.
One of the problems I'm having has to do with sorting different types of ContentBlocks in a Column. I guess each type of ContentBlock (like TextBlock, NewsBlock, PictureBlock, ...) should have it's own table in the database because each has it's own different fields. A TextBlock might only have a field called Text whereas a NewsBlock might have fields for the Text, the Summary, the PublicationDate, ...
Since one Column can have ContentBlocks located in different tables, I guess I'll have to create a many-to-many association for each type of ContentBlock. For example: ColumnTextBlocks, ColumnNewsBlocks and ColumnPictureBlocks.
The problem I have with this setup is the sorting of the different ContentBlocks in a column. This could be something like this:
TextBlock
NewsBlock
TextBlock
TextBlock
PictureBlock
Where do I store the sorting number? If I store them in the associaton tables, I'll have to update a lot of tables when changing the sorting order of ContentBlocks in a Column. Is this a good approach to the problem?
Basically, my question is: What is the best way to model this keeping in mind that it should be easy to add new types of ContentBlocks?
My next question is: What ORM can deal with that kind of modeling? To be honest, we are ORM-virgins at work. I have been reading a bit about Linq-to-SQL and NHibernate, but we have no experience with them. Because of the IList in the Column class (see code below) I think we can rule out Linq-to-SQL, right? Can NHibernate handle the mapping of data from many different tables to one IList?
Also keep in mind that this is just a very small portion of the domain. Other parts are Users belonging to a certain UserGroup having certain Permissions on Pages, ColumnGroups, Columns and ContentBlocks.
The code (just a quick first draft):
public class Page
{
public int PageID { get; set; }
public string Title { get; set; }
public string Description { get; set; }
public string Keywords { get; set; }
public IList<ColumnGroup> ColumnGroups { get; set; }
}
public class ColumnGroup
{
public enum ColumnGroupLayout { OneColumn, HalfHalf, NarrowWide, WideNarrow }
public int ColumnGroupID { get; set; }
public ColumnGroupLayout Layout { get; set; }
public IList<Column> Columns { get; set; }
}
public class Column
{
public int ColumnID { get; set; }
public IList<IContentBlock> ContentBlocks { get; set; }
}
public interface IContentBlock
{
string GetSummary();
}
public class TextBlock : IContentBlock
{
public string GetSummary()
{
return "I am a piece of text.";
}
}
public class NewsBlock : IContentBlock
{
public string GetSummary()
{
return "I am a news item.";
}
}
