Content management – getting fresher…

I’ve been doing some more thinking about content management idea and I’ve figured out how – I think – the database is going to work. Bear with me, because this might get pretty geeky.

Firstly I wanted to have an unlimited number of items; an item could be a page, a blog, a gallery, a forum etc. It doesn’t really matter what type the item is, but it is VERY important that we understand the relationships between them. So the Parent ID thing I mentioned in my original post has immense importance. Without that, there is no system. Let me give you a quick example in case you dont get what I mean.

I have a homepage for my site, which is called “homepage”. It has an ID of 1 and a Parent ID of 0. The ID is 1 because it’s the first item in my database. The Parent ID is 0 because if is the lowest level item.

I have another item called “about” which has an ID of 2 and a Parent ID of 1. The ID of 2 is because it’s the second item to be added to my database. The Parent ID is 1 because it’s a child of the item with ID 1 – homepage. This way we can build up hierarchical relationships between any number of items. Spiffing.

My tables will look more than a little like this:

Table: items

id (auto integer)
item_type (id of the type of item this is, from the item_types table)
status (the id of the status of the item, from the statuses table)
parent_id (the id of the parent of this item)
name (the name for this item)
permalink (the URL-safe version of the name, used for clean URLs)
created_on (datestamp of the create date)
created_by (id of the user that created this item, from the users table)

Table: statuses

id (auto integer)
status (the description of this status)

status values: [draft, public, private, deleted]

Table: item_types

id (auto integer)
item_type (the description of this type, more in this later)

I’ll let you guess what the users table will look like.

So, we have a basic database. Now what we need to do is start populating it with some data. But where? There doesn’t seem to be any fields in the items table to store the page content in, so where does it go? Well, I’m glad you asked, because the answer is ‘it depends’.

What content gets displayed when each item is called depends entirely on the type of that item. We know what type each item is because it’s stored in the database, but what types could we have – and what data can be attached to each type? Here’s a quick list.

Type: page
Data: page_title [text], page_description [text], page_keywords [text], page_body [text], allow_comments [bool]

Type: blog
Data: blog_title [text], blog_description [text], allow_comments [bool]

Type: blog_entry
Data: entry_title [text], entry_body [text], allow_comments [bool], categories [array]

Type: blog_comment
Data: commenter_name [text], commenter_email [text], commenter_website [text], commernter_ip [text], comment_body [text]

Type: gallery
Data: gallery_title [text], gallery_body [text]

Type: photo_set (just to make it clear, I thought each gallery – you could have more than one, of course – could have multiple photo sets)
Data: set_title [text], set_description [text], allow_comments [bool]

Type: photo
Data: photo_name [text], photo_description [text], allow_comments [bool]

Type: forum
Data: forum_title [text], forum_description [text], anonymous_posts [bool]

Type: forum_thread
Data: thread_title [text]

Type: forum_post
Data: post_body [text], poster_name [text], poster_email [text], poster_website [text], poster_ip [text]

So, of course, there could be more types than this. And, of course, quite a few of the types share the same kind of data which could well be normalised in some table somewhere. But hopefully you see that most item types are pretty similar in the data they require. The lesson from all of this is: do we really need different tables for all of these data types? I don’t think we do.

One last note. This system borrows heavily from the ethos of 37signals Ruby on Rails which is ‘convention over configuration’. That makes a lot of sense to me.