April 23, 2011 - MySQL Webcomics Database Tutorial

Caveman Agent doesn't have a lot of words, and I also don't really have a lot to blog about. I was asked how I designed the Caveman Agent website, since I'm not using wordpress. So in this post I'll explain how I set up the mysql database to work well for a webcomic. At some point in a later post I might talk about the PHP side of things. Disclaimer: I'm not a programmer or an expert at database design. My goal was to make a simple, small setup that only does what I need and not anything else.

The first thing I need is a table to keep all my comics in. I call this table 'comics'. A row from this table looks like:

id url comic_name comic_text date
1 1.jpg Sold This is the first comic! enjoy reading. 2010-10-12
2 2.jpg Camp 2010-10-22


Pretty simple. One row for each comic. When you're browsing through the comics, the variable in the URL selects the right comic. So if the URL variable is comic=5, the page will go grab comic number 5.

Now that the comics are set, I need a way to post blog entries. I do this with the 'blog' table, which looks like:

id title content date
2001 Blog Title 1 This is my first blog post. 2011-04-01
2002 Blog Title 2 This is my second blog post. 2011-04-02


Also really simple. Just a title, the content, and the date. The main blog page displays the latest 10 posts or so, and if you click on an individual post, the URL variable sets the correct post. So if the URL variable is post=2002, you'll be looking at the second blog post. The id's for blog posts start at 2000, not 0. The reason for this is explained later.

So I have my comics and blog, but I need a way for people to post comments. So I have anothe table for comments called........... 'comments'. Here's what it looks like.

id post_id name date email content website
1 3 Mr. Fuzzy 2011-04-01 0c34d613dcfc7aea85529f Hey your comic is OK! www.mrfuzzy.com
2 2001 Sir Bob 2011-04-04 bear This is a boring blog post. www.sirbob.com


Comments are a little more complicated than the comics and blog talbes. All the comments on Caveman Agent go in this one table. The 'post_id' field determines where the comment goes. If the comment is for 'Camp' (the second comic with an id of 2), then the comment's post_id will be 2. If the comment is for my second blog post (with an id of 2002), then the post_id will be 2002. This is why the id for comics starts at 1, and the id for blogs start at 2001, so there is no overlap. Email addresses are not stored, just the hash of the email address. This is used to display the gravatar of the commenter.

I added the option to comment as one of the characters from my comic. When you comment, you can click on one of the character icons (monkey, bear, dino, or farmer). If you comment as bear, then the 'email' field won't have the hash of your email, but will contain 'bear'. When this comment is loaded on the page, the bear icon will be displayed instead of a gravatar.

The final table is for my RSS feed. Because I have blog posts and comics to include in my RSS feed, I need a way to sort them in the correct order. I probably could just grab everything from comics and blog and sort by date, but I was having a lot of trouble setting up the RSS feed and decided to make an explicit table. Every time a blog post or comic is posted to Caveman Agent, a record gets added to the RSS table. So it's just a chronological list of all comics and blogs in one place.

id rss_type rss_id
1 comic 1
2 blog 2001
3 comic 2


The first row is 'Sold', the first comic. 'rss_type' tells the RSS feed whether the content should be formatted as a comic or blog post, and the 'rss_id' field tells the RSS feed the ID of the comic/blog post. To generate my RSS feed, I grab the ten most recent rows from the rss table, and all my comics and blog posts end up in the correct order.

That's it. I only have these four tables for Caveman Agent. This setup is simple and pretty limited, but it works for this site since the comic is pretty simple.

Here's a confusing picture of the four tables for the Caveman Agent website:

Back to blog




Discuss

Oh my, this is pretty impressive. It's a bit beyond me but more accessible than most descriptions. Meaning I didn't want to soak my head in the tub even one time while reading it.

hovvit - May 14, 2011 - Reply to this comment
Sorry if it's a little unclear. This is the first 'tutorial' I've tried to write... I'm planning on writing a couple more, but about less broad topics.

Post a comment
Name: (Optional)
Website: (Optional)
Email*: (Optional, or select from the right)          
 * Your email address is used to display your gravatar. Your email address is not saved.
Comment:

    Post links with the format: [url]%name%