[MySQL] Database design

[ZephyrWest]

After reading bwh2 say countless times the importance of database design, I've decided to post the database design I'm planning on using for a blog I'm creating. Criticize away because I'd rather not have redo everything later after finding out my design sucks. :)


# Contains blog posts and different information about them.
CREATE TABLE post_db (
post_id SMALLINT UNSIGNED NOT NULL auto_increment,
post_title VARCHAR(30) NOT NULL,
post_body LONGTEXT NOT NULL,
post_excerpt TEXT,
post_status enum('published', 'draft') NOT NULL,
post_date DATETIME NOT NULL,
post_edit_date TIMESTAMP NOT NULL,
post_auth_id TINYINT UNSIGNED NOT NULL,
PRIMARY KEY (post_id) ) TYPE=InnoDB

# Contains catagories and their IDs
CREATE TABLE cat_db (
cat_id TINYINT UNSIGNED NOT NULL auto_increment,
cat_name VARCHAR(20) NOT NULL,
PRIMARY KEY (cat_id) ) TYPE=InnoDB

# Contains info about the different authors
# The authors password is going to be encrypted using
# SHA1 so it is always going to be 40 characters long
CREATE TABLE auth_db (
auth_id TINYINT UNSIGNED NOT NULL auto_increment,
auth_fname VARCHAR(20) NOT NULL,
auth_lname VARCHAR(20) NOT NULL,
auth_email VARCHAR(30) NOT NULL,
auth_uname VARCHAR(20) NOT NULL,
auth_pword CHAR(40) NOT NULL,
auth_post_count SMALLINT UNSIGNED NOT NULL,
PRIMARY KEY (auth_id) ) TYPE=InnoDB

# Database that keeps track of different catagories a post is in
CREATE TABLE post_cat_db (
id INT UNSIGNED NOT NULL auto_increment,
cat_id TINYINT UNSIGNED NOT NULL,
post_id SMALLINT UNSIGNED NOT NULL,
PRIMARY KEY (id) ) TYPE=InnoDB

[foodpk]

One post can only be in one category right? In that case, you should create 1:N cardinality instead of M:N cardinality like you did when you made post_cat_db. If one post only fits in one category, the extra table is redundant. You're better off just adding a cat_id foreign key to post_db. So I would have 3 tables, cat_db, post_db and auth_db and then post_db would have the primary keys of the other two tables so you can know which category it fits in and who the author is.
Have you considered adding comments for each blog post? It's pretty easy, just another table which contains comments and the primary key of its corresponding post as a foreign key.
Which version of MySQL are you using? If you're using MySQL 5+, I would suggest adding foreign key constraints, they're very useful.

[bwh2]

One post can only be in one category right? In that case, you should create 1:N cardinality instead of M:N cardinality like you did when you made post_cat_db. If one post only fits in one category, the extra table is redundant. You're better off just adding a cat_id foreign key to post_db. So I would have 3 tables, cat_db, post_db and auth_db and then post_db would have the primary keys of the other two tables so you can know which category it fits in and who the author is.foodpk has a good point here. if you know for certain that a post will only be in one category and that your system will stay that way, then the extra table is just making things more complex than necessary. if however posts can go into more than one category or you're not sure if that rule will change, keep the post_cat_db table.

btw, i'm getting an error when i run your SQL. i'm on 4.1.13 though.

[ZephyrWest]

Here's the updated design. I took foodpk's advice and added a few foreign keys here and there, but I'm not sure if I used the correctly (I'm still wrapping my head around SQL). And I've decided to keep the post_cat_db since I'm want the ability to file posts under multiple catagories.

I also added an options DB so I can store different settings like how many posts will be displayed per page, the blog title, etc...

What do you guys think?

btw... this is valid SQL so it should run without errors. The code I posted before didn't have the semi-colons at the end of each statement because I never really intended for anyone to run it. :)


CREATE TABLE post_db (
post_id SMALLINT UNSIGNED NOT NULL auto_increment,
post_title VARCHAR(30) NOT NULL,
post_body LONGTEXT NOT NULL,
post_excerpt TEXT,
post_status enum('published', 'draft', 'deleted') NOT NULL,
post_date DATETIME NOT NULL,
post_edit_date TIMESTAMP NOT NULL,
post_auth_id TINYINT UNSIGNED NOT NULL,
PRIMARY KEY (post_id),
FOREIGN KEY (post_auth_id) REFERENCES auth_db (auth_id) ON UPDATE CASCADE
) TYPE=InnoDB;

CREATE TABLE cat_db (
cat_id TINYINT UNSIGNED NOT NULL auto_increment,
cat_name VARCHAR(20) NOT NULL,
PRIMARY KEY (cat_id)
) TYPE=InnoDB;

CREATE TABLE auth_db (
auth_id TINYINT UNSIGNED NOT NULL auto_increment,
auth_fname VARCHAR(20) NOT NULL,
auth_lname VARCHAR(20) NOT NULL,
auth_email VARCHAR(30) NOT NULL,
auth_uname VARCHAR(20) NOT NULL,
auth_pword CHAR(40) NOT NULL,
PRIMARY KEY (auth_id) ) TYPE=InnoDB;

CREATE TABLE post_cat_db (
id INT UNSIGNED NOT NULL auto_increment,
cat_id TINYINT UNSIGNED NOT NULL,
post_id SMALLINT UNSIGNED NOT NULL,
PRIMARY KEY (id),
FOREIGN KEY (post_id) REFERENCES post_db (post_id) ON DELETE CASCADE
) TYPE=InnoDB;

CREATE TABLE options_db (
id TINYINT UNSIGNED NOT NULL auto_increment,
option_name VARCHAR(20) NOT NULL,
option_value TEXT NOT NULL,
PRIMARY KEY (id)
) TYPE=InnoDB;

CREATE TABLE comments_db (
id INT UNSIGNED NOT NULL auto_increment,
post_id SMALLINT UNSIGNeD NOT NULL,
comment_poster VARCHAR(20) NOT NULL,
comment_email VARCHAR(30) NOT NULL,
comment_body TEXT NOT NULL,
PRIMARY KEY (id),
FOREIGN KEY (post_id) REFERENCES post_db (post_id) ON DELETE CASCADE
) TYPE=InnoDB;

[bwh2]

design is looking good dude. nice work.

i'm not sold on the options table though. mostly because with things like admin options, the data isn't really structured. but by putting it into a db, you're sort of forcing it to have structure. for instance, what i typically do is include() a definitions file. in there, i define things like titles and posts per page. i guess my point is that the column should define the structure for the rows, not vice versa. maybe it's just personal preference.

but it also prevents me from having to hit the db constantly to get ordinary things. for instance, you will have to run separate queries for each option you want to pull. so who knows how many additional queries per page that could result in. the only way to avoid running so many queries is to throw a bunch of if statements at a select * result set. either way a lot of work for you and the machines. meanwhile, i can just include one file that has things defined. it also makes changing those definitions very easy.

couple more questions/comments:
- why is post_db.post_edit_date timestamp and not datetime?
- you should add a comment_status column to comments_db so that you can remove comments
- post_cat_db does not need an id column
- if your categories will have a hierarchy, you will need a cat_parent_id column

[ZephyrWest]

Okay, I've taken out options_db and moved all the options to a different file.

- post_db.post_edit_date is timestamp so it will update itself if I ever edit the post. Should I be less lazy and do that manually?
- comments_db.comment_status added.
- id column removed. Dunno why I put one...
- Don't think I need hierarchial catagories.

One question... should I make a class to fetch all the data (like posts, post titles, author names, etc...) or should I just use a bunch of functions?

For example...


<?php
class Blog {
// constructor that connects to mysql and selects db...

function postBody($post_id) {
$query = "SELECT post_body FROM post_db WHERE post_id=$post_id";
$result = $this->query($query);

$post = mysql_fetch_assoc($result);

return $post['post_body'];
}

// other methods for fetching data from the db...
?>

[bwh2]

- post_db.post_edit_date is timestamp so it will update itself if I ever edit the post. Should I be less lazy and do that manually?hmm... still not following you. what will update itself?

One question... should I make a class to fetch all the data (like posts, post titles, author names, etc...) or should I just use a bunch of functions?go with OOP!

[ZephyrWest]

If a field marked TIMESTAMP, it will record the time of the last INSERT or UPDATE operation performed (at least that's how I interpreted the manual). So... if I were to edit a post (UPDATE it) post_edit_date would automagically be set to to the current date. That way I don't have to do it myself.

The question is, should I make post_edit_date DATETIME and set the edit date myself?

[bwh2]

well, you should set it to DATETIME, but you don't need to edit it yourself. just run an update query.

[ZephyrWest]

Ok, I'll take your advice as you're obviously much more experienced than I. :) I'll post the code when I'm done coding the blog and see how I did. :cons:

Thanks for all your help guys. Appreciate it!