As promised, here’s my proposed database schema (using MySQL) for my Spokane Personal Publishing System. It’s long and technical, read on at your own risk.

One thing you’ll notice in all of my tables is that I include a MySQL TIMESTAMP field, named ts. This is updated automatically by MySQL whenever a record is updated. I’m not entirely sure it’s necessary for every table, but I do like the system keeping track of changes to data on this level.

There are also a number of permissions fields. The intent way back when was to use these in conjuction with role-based permissions for users, but I may phase them out. Don’t know for sure yet.

The other thing I’ll mention is that I’m not including all of my table schemas here yet—for instance, you’ll see reference to the user_id field which indexes back to a users table. Those sort of tables are more “standard” tables I tend to use (and re-use) in my Web applications, and I’ll cover them in a later article.

CREATE TABLE channels (
  channel_id int(11) NOT NULL auto_increment,
  parent_channel_id int(11) NOT NULL default '1',
  content_id int(11) NOT NULL default '0',
  user_id int(11) NOT NULL default '0',
  name varchar(255) NOT NULL default '',
  title varchar(255) NOT NULL default '',
  description text NOT NULL,
  keywords text NOT NULL,
  date_created datetime NOT NULL default '0000-00-00 00:00:00',
  sort_order int(11) NOT NULL default '0',
  is_active tinyint(1) NOT NULL default '1',
  permissions varchar(255) NOT NULL default '',
  ts timestamp(14) NOT NULL,
  PRIMARY KEY  (channel_id),
  KEY parent_channel_id (parent_channel_id),
  KEY user_id (user_id),
  KEY name (name),
  KEY content_id (content_id)
) TYPE=MyISAM;

(Note the common set of metadata fields this table shares with the content table, below.) The channels are inteded to be a hierarchical system, each channel can be a “parent” with essentially an unlimited number of children. Everyone’s method of categorizing content is different, but for myself I intend to follow the categorization scheme used by the Open Directory Project, because that’s about ideal to me. YMMV.

And a note about the content_id field there. My intent is to allow a channel to have a “default” content item, should the channel be somehow published without referring to any specific content; if/when that happens, the content item pointed to becomes the published item. Similar in concept to the DirectoryIndex directive in the Apache webserver, which allows you to define what file Apache should serve to the browser when none is specified.

CREATE TABLE content (
  content_id int(11) NOT NULL auto_increment,
  content_node_id int(11) NOT NULL default '0',
  user_id int(11) NOT NULL default '0',
  mime_type varchar(50) NOT NULL default '',
  name varchar(255) NOT NULL default '',
  title varchar(255) NOT NULL default '',
  description text NOT NULL,
  keywords text NOT NULL,
  body mediumtext NOT NULL,
  date_created datetime NOT NULL default '0000-00-00 00:00:00',
  release_date datetime NOT NULL default '0000-00-00 00:00:00',
  expires_date datetime NOT NULL default '0000-00-00 00:00:00',
  sort_order int(11) NOT NULL default '0',
  priority int(11) NOT NULL default '5',
  allow_comments tinyint(1) NOT NULL default '1',
  notify_on_comment tinyint(1) NOT NULL default '1',
  is_draft tinyint(1) NOT NULL default '1',
  is_active tinyint(1) NOT NULL default '1',
  permissions varchar(255) NOT NULL default '',
  ts timestamp(14) NOT NULL,
  PRIMARY KEY  (content_id),
  KEY user_id (user_id),
  KEY name (name),
  KEY content_node_id (content_node_id)
) TYPE=MyISAM;

The backbone of the system. This table contains more fields than I outlined in my original sketch of the system. The release_date and expires_date fields allow you to control the particular publish time of a content item. The priority field is from an idea I picked up somewhere (from Scoble, I think) that would allow the content author to rate their content’s priority relative to other content; it would be up to the application to determine how to handle higher priority content.

The two “comment” metafields are artifacts of the blogging world, but good artifacts when it comes to conversational software. When I decided I wanted to allow users to contribute comments (and perhaps other types of contributions, like links or TrackBacks?), the natural next step for me was to allow a content author to determine on an atomic level whether a content item could warrant comments, and if so, to notify the author when a comment has been added.

CREATE TABLE content_channels (
  content_channel_id int(11) NOT NULL auto_increment,
  channel_id int(11) NOT NULL default '0',
  content_id int(11) NOT NULL default '0',
  ts timestamp(14) NOT NULL,
  PRIMARY KEY  (content_channel_id),
  KEY channel_id (channel_id),
  KEY content_id (content_id)
) TYPE=MyISAM;

This is simple a lookup table that effects a many-to-many relationship between channels and content.

CREATE TABLE content_comments (
  content_comment_id int(11) NOT NULL auto_increment,
  content_id int(11) NOT NULL default '0',
  name varchar(50) NOT NULL default '',
  email varchar(50) NOT NULL default '',
  url varchar(255) NOT NULL default '',
  ip varchar(15) NOT NULL default '',
  date_created datetime NOT NULL default '0000-00-00 00:00:00',
  body text NOT NULL,
  ts timestamp(14) NOT NULL,
  PRIMARY KEY  (content_comment_id)
) TYPE=MyISAM;

This table may need some work. Nothing fancy here, just a standard table storing user comments.

CREATE TABLE content_nodes (
  content_node_id int(11) NOT NULL auto_increment,
  user_id int(11) NOT NULL default '0',
  name varchar(255) NOT NULL default '',
  title varchar(255) NOT NULL default '',
  description text NOT NULL,
  keywords text NOT NULL,
  date_created datetime NOT NULL default '0000-00-00 00:00:00',
  allow_comments tinyint(1) NOT NULL default '1',
  max_comments int(11) NOT NULL default '0',
  is_active tinyint(1) NOT NULL default '1',
  permissions varchar(255) NOT NULL default '',
  ts timestamp(14) NOT NULL,
  PRIMARY KEY  (content_node_id),
  KEY user_id (user_id)
) TYPE=MyISAM;

A “content node” is another method of categorizing content, and for differentiating various types or styles of content—for instance, one node might be the chuggnutt.com blog, another might be beer brewing recipes. In this case, I would want to be able to handle the two content types entirely separately, and have a convenient (especially in SQL terms) means of dealing with them.

Again you’ll notice the “comments” metadata; here, this would give the node owner meta-control over whether to allow user comments at all and to be able to limit the comments (per content item) to some arbitrary limit. I’m honestly not sure this is necessary, so that’s up for grabs.

Spokane schema in a nutshell. Comments?