Results 1 to 15 of 15
  1. #1
    Join Date
    May 2005
    Posts
    13

    Question Database Newbie - Blogging Engine

    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.

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    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
    George
    Home | Blog

  3. #3
    Join Date
    May 2005
    Posts
    13
    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?

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    May 2005
    Posts
    13
    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?

    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?

    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.

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    May 2005
    Posts
    13
    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?

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    SET is non-standard

    also, i'm not sure if a SET column can be searched efficiently
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Quote Originally Posted by r937
    the reasoning behind my ideas is 20 years of SQL experience
    Wasn't that Mikes arguement?
    George
    Home | Blog

  10. #10
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    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
    Last edited by healdem; 09-20-07 at 08:02.
    I'd rather be riding on the Tiger 800 or the Norton

  11. #11
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    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"
    George
    Home | Blog

  12. #12
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  13. #13
    Join Date
    May 2005
    Posts
    13
    Thanks to everyone's help so far.

    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.

    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.
    Last edited by pico; 09-20-07 at 20:10.

  14. #14
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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!!
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  15. #15
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    Originally Posted by r937
    the reasoning behind my ideas is 20 years of SQL experience
    Originally Posted by georgev
    Wasn't that Mikes arguement?
    Nope - mike never used that as an arguement - though perhaps he did feel that way. I've no doubt you'll feel the same way in another 18 years

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •