Database Newbie - Blogging Engine

pico · #1 (**permalink**) 09-19-07, 08:12

First off all, I apologize if these are some stupid questions. I feel like I'm the world's biggest newbie to databases.

I've been designing web site's layouts for several years now and I just recently decided to create my own blog that'll be hosted on my LAMP (Linux, Apache, MySQL & PHP) server. I have everything setup and to my knowledge, it appears to be secure (although I'll double check before I go live).

I've barely touched the server side of things (just a little PHP) and I have absolutely no knowledge about databases other then a few exercises from a class textbook using Microsoft Access (from many years ago) and a bit of reading I have recently done on the Internet.

I know there are already many blogging engines out there but I would prefer to learn how to create my own then to use an existing one.

Anyway, I've planned to have the blogging system as simple as possible. Only using two tables. Here's what I have so far...

Code:

BlogEntries
DatePosted	TIMESTAMP	Primary Key
Categories	SET
Subject		TINYTEXT
Status		ENUM		(Used for static pages.)
AllowComments	BOOLEAN
Content		TEXT		Required

Code:

Comments
CommentTo	TIMESTAMP	Linked to BlogEntries->DataPosed
DataPosted	TIMESTAMP	Required
Name		TINYTEXT
Approved	BOOLEAN		(Posts will need to be approved before they're visible.)
IPAddress	INT		Required
Content		TEXT		Required

I got most of my starting points from looking at WordPress's database system. Let me know if there is anything I should do to tweak my tables above.

Here's are my questions;

I've noticed that in a lot of tutorials (as well as WordPress's database) that they used a number field (usually called the ID) as the primary key. Is this due to speed issues? Will there be any disadvantage for using a TIMESTAMP?
Does everything table require a primary key? My comments table above doesn't need one, but should add one?
Should I store the blog's settings in a database too? WordPress seems to do this, however someone said that settings they won't be changed regularly should be stored in a standard file.
WordPress uses a complex system for the blog's categories. I'm simply using a SET. Is this sufficient for a simply blog?

Hopefully these questions aren't too stupid.
Huge thanks to any replies.

gvee · #2 (**permalink**) 09-19-07, 10:11

An incrementing integer primary key is a surrogate key which should only be used when there is no other key available (ty to Rudy

)! A primary key is an attribute of a record that uniquely identifies that record. Will your timestamp be unique for every entry? If so that's a perfectly acceptable primary key. However I would comment that DatePosted should probably be DateTimePosted

pico · #3 (**permalink**) 09-19-07, 17:35

Thanks to your reply, georgev. So do you think that other then the DateTimePosted field, the table is alright?

I'm the only one to be using this so the chances of me writing multiply entries a second would be pretty rare.

I've noticed in WordPress's database, they pre-cache the amount of comments for each post using a BIGINT. I was planning to use SQL's count function in my blog for this. I guess that it's a speedup, but just how fast of one? Even will a million comments for each entry, I wouldn't expect there to be much of a difference, right?

r937 · #4 (**permalink**) 09-19-07, 21:02

the only disadvantages of using a timestamp as the primary key are the slightly increased space requirements (negligible, i would say), and the slightly more difficult process of assigning values

yes, every table needs a primary key

now hold on to your hat because i'f going to suggest something radical: combine your two tables

regarding storing settings: i would store them in the database

SET is a bad move, in my opinion -- i would have a category table, and a many-to-many relationship table to link entries to categories

pre-calculating the comment count is often a good idea -- yes it's "redundant" (calculable) but you need to consider update frequency versus read frequency

pico · #5 (**permalink**) 09-19-07, 21:46

Quote:

the only disadvantages of using a timestamp as the primary key are the slightly increased space requirements (negligible, i would say), and the slightly more difficult process of assigning values

That doesn't make sense to me. Are you saying that by adding another field for the primary key, I'll save space?

Quote:

now hold on to your hat because i'f going to suggest something radical: combine your two tables

I'm a really newbie I admit, but I don't see how this would benefit me. Since certain fields would only be used with certain entries (such as Approved, IPAddress, Name, AllowComments, etc). I guess this could be faster, but in the long run, wouldn't it waste a lot of disk space over time?

Quote:

SET is a bad move, in my opinion -- i would have a category table, and a many-to-many relationship table to link entries to categories

The idea of so many tables and links doesn't seem right to me. I guess that I'm primary looking for a setup that's fast and simple. After all, it only a very simple blog with simple features.

Anyway thanks to your reply, r937. Could you let me know the reasoning behind your ideas? Thanks.

r937 · #6 (**permalink**) 09-19-07, 23:11

the reasoning behind my ideas is 20 years of SQL experience

i do appreciate the benefits of simplicity, so by all means, keep it simple

pico · #7 (**permalink**) 09-20-07, 00:26

I didn't mean to sound rude or anything. Thanks for your advice and all.

It's just that I fail to see how adding more fields could help the database in either speed or simplicity.

You mentioned that using a SET is a bad move. Why is this?

r937 · #8 (**permalink**) 09-20-07, 06:10

SET is non-standard

also, i'm not sure if a SET column can be searched efficiently

gvee · #9 (**permalink**) 09-20-07, 06:57

Quote:

Originally Posted by r937

the reasoning behind my ideas is 20 years of SQL experience

Wasn't that Mikes arguement?

healdem · #10 (**permalink**) 09-20-07, 06:58

if you use Timestamp as your key then you will not be able to allow users to edit their posts by directly altering the post, you will have to store those changes or the revised post in another table.
Timestamp changes each time the record is edited, so in my view its not a very good candidate key, if for no other reason any child table referring to that key will have to also have its FK changed

if that's not a problem in you application then fine use it.. but it just doesn't sound 'right' to me, and if it don't sound right to me that usually a sign that its not a good idea

besides which having two columns both Timestamp is redundant, why wouyld you wan to duplicate the date time a record was last changed, or do you actually mean a Datetime value

Details of Timestamp form MySQL site
Details from the MySQL website

gvee · #11 (**permalink**) 09-20-07, 07:50

Good point Healdem!
What if the tmiestamp was replaced with a datetime DEFAULT GetDate()?
Would that be sufficient enough to solve the problem?

Perhaps the timestamp could be "dateTimeOfLastRevision"

r937 · #12 (**permalink**) 09-20-07, 09:36

Quote:

Originally Posted by georgev

Perhaps the timestamp could be "dateTimeOfLastRevision"

what about just LastRevised

i mean, gee, you don't call your columns stringOfLastname, do you?

pico · #13 (**permalink**) 09-20-07, 19:05

Thanks to everyone's help so far.

Quote:

if you use Timestamp as your key then you will not be able to allow users to edit their posts by directly altering the post, you will have to store those changes or the revised post in another table.

I've going to leave the blog's updating straight to the software. The primary TIMESTAMP field will never be changed once set. (I'll switch off the ON UPDATE feature for it).

For updates, I'm planning to have some XML straight in blog's TEXT field (such as an <update> tag. I'll write some PHP code to deal with it. I realize that parsing the TEXT's input (without using a database) may slow it down a little, but I don't expect to use updates very often.

Quote:

SET is non-standard

also, i'm not sure if a SET column can be searched efficiently

Yea, I did a bunch of reading on it and I've decided to drop the SET.

Here's my new database design. The names aren't final, just placeholders for now.

Code:

BlogEntries
DateTimePosted	TIMESTAMP Primary Key.
Subject		TINYTEXT Can be NULL.
Status		ENUM('Normal', 'Static', 'Hidden') Default will be normal. Will be an index.
AllowComments	BOOLEAN Default true.
Content		TEXT Required.
CommentCount	SMALLINT Updated by the blogging software. Required. Unsigned.

Code:

Comments
ID		SMALLINT Primany key. Auto increment.
CommentTo	TIMESTAMP Foreign key to BlogEntires->DateTimePosted. Will also be an index.
DataTimePosted	TIMESTAMP Required.
Name		TINYTEXT Can be NULL.
Approved	BOOLEAN Will be an index. Default will be false.
IPAddress	INT Required. Unsigned.
Content		TEXT Required.

Code:

Categories
Name		TINYTEXT Primany key.
CategoryCount	SMALLINT Updated by the blogging software. Required. Unsigned.

Code:

LinkedCategories
LinkedTo	TIMESTAMP This is a foreign key to BlogEntires->DateTimePosted. Also an index.
Category	TINYTEXT This is a foreign key to Categories->Name.

I was trying to find out something last night, but I never got a straight answer. In the LinkedCategories tabel, is it possible for both fields be to primary keys? So on the database level, it would be impossible for two exactly the same fields to be placed in it?

Also foreign keys are linked straight by their data, right? I kind of always thought of them as a programming pointer (found in C, C++, etc). If this isn't the case, that would mean text strings are going to be duplicated. So in the long run, it'll be better to use SMALLINTs as ID primary keys, right? (to save space).

Thanks again.

r937 · #14 (**permalink**) 09-20-07, 19:30

Quote:

Originally Posted by pico

In the LinkedCategories tabel, is it possible for both fields be to primary keys? So on the database level, it would be impossible for two exactly the same fields to be placed in it?

yes!!