Not that anyone is anxiously awaiting the next installments, but, sorry for the delay in posts. I got caught up in a few projects and did not have time to dedicate to this over the past few weeks.

Bloxer?!?!:
I think I'm going with Bloxer for the "official" name from here on out. Because, all one-off projects need a name, right?! That, or I'm too lazy to type Jaxer-based Blog over and over.

Disclaimer/Credit:
I would be remiss if I did not mention that much of the feature set and underlying data model is inspired by and taken from Raymond Camden's BlogCFC (a wonderful and open-source ColdFusion-powered blog application). With Ray's permission, I am using his application as my base for this project. Assuming what I create here isn't a piece of crap, it'll be available as a open-source application for any and all to use.

Database Server:
I'm using MySQL 5.0.x as my database. All SQL editing and transactions are being run in Aptana Studio's Database Explorer Perspective (this is a feature of Aptana Studio that, in my opinion, needs many features added (e.g., you cannot view stored procedures, triggers and some other basic DB functionality). However, for what we are doing, it'll do the job and do it well.

This perspective (Database Explorer) contains three views: Databases, SQL History View, and the SQL Execute View. Of course, it comes with an SQL editor for working with SQL scripts.

Aptana Database Explorer Screen Shot:


DB Conventions:
  1. All table names are lower case with underscores (_) separating words (i.e., my_table).
  2. Each table will have a primary key named 'id' (even if it's just a look up) which will be an integer and set to auto_increment (+1).
  3. I will append table_name_ to a table's primary key (named 'id') as the column name when generating foreign keys. Our tables will primary use the MyISAM engine (for FULL TEXT indexes) in MySQL, which means Foreign Keys are technically ignored in your Create Table statements but we'll enforce what we need at the code level.
  4. Table names are plural (i.e., blog_entries rather than blog_entry). I am choosing this because this blog will be migrated to a Rails learning project I am working on (and later still, a PHP project using the Code Igniter framework) and Rails' convention dictates plural table names.
Tables:
  • blogs: The "daddy" table. It holds general information about each blog running in the application context.
  • blog_categories: Each blog can have many categories (one-to-many) and each category can have many listings (many-to-many).
  • blog_entries: A blog is really nothing more than a series of entries (aka posts) afterall!
  • blog_entry_categories: A lookup table for entries and their related categories (i.e., used when a reader wants to see all entries in a particular category.
  • blog_entry_types: This is a static table that holds information on the three standard entry templates: (1) photo entry, which uses a jQuery plug-in called Thickbox to display entry images; (2) technical, which handles code samples inserts and display as well as source code downloads, and (3) standard, which is a text-only (inline images added via the Rich Text Editor are allowed) entry.
  • blog_entry_comments: Contains all reader comments, each comment is tied to a specific entry.
  • blog_related_entries: Holds relationships between two or more entries.
  • blog_searches: Records all blog search terms by date.
  • blog_subscribers: Email addresses for updates on any/all postings.
  • blog_entry_subscribers: Email addresses for updates on a specific post and its comments.
  • blog_trackbacks: Holds trackback links (still learning about implementing this, so it's sort of a stubbed table for now).
  • blog_code_blocks: Table holds code blocks used in tutorials or other technical posts.
  • blog_galleries: Table contains image references for a photo entry.
  • blog_users: Holds security details for blog administrators and users.
Install the Database:
The SQL script (download link below) is for MySQL 5+. If you are new to this or haven't done it in a while, following are some links to helpful articles:
Next Up:
With the data model in place, I'm going to outline the plan of attack and generate my models (or beans, if you prefer) and DAOs for the web application. Time to get codin'!

Downloads:
Download the DDL source file (baddogs_blog.sql).

Comments