Page 1 of 1

Blogger database design structure

Posted: Mon Nov 07, 2005 9:14 am
by ljCharlie
I'm trying to host my own blogger site. Will anyone tell me where I can find out some of the database design structure for a blogger website? How do many tables and fields do I need? I realized a lot of these will depend on much information or flexibility I want to allow the user but I need to know the basic requirements. Any help is appreciated.

Posted: Mon Nov 07, 2005 9:33 am
by Chris Corbyn
For a basic blogging system I'd have at least the following 3 tables. Users, Blogs, Blog_replies.

Code: Select all

create table users (
    id int auto_increment primary key,
    username varchar(50),
    password char(32),
    surname varchar(50),
    forename varchar(50)
)

create table blogs (
    id int auto_increment primary key,
    userid int,
    title varchar(255),
    subtitle varchar(255),
    body text,
    date_posted datetime
)

create table blog_replies (
    id int auto_increment primary key,
    userid int,
    blogid int,
    title varchar(255),
    body text,
    date_posted datetime
)
Probably best to just sit down and think what you want your blog to do/store, then decide what info you need to know and then you can start to create some tables to hold that info.

Posted: Mon Nov 07, 2005 10:30 am
by timvw
Here is a dump from wordpress

Code: Select all

-- --------------------------------------------------------

-- 
-- Table structure for table `wp_categories`
-- 

CREATE TABLE `wp_categories` (
  `cat_ID` bigint(20) NOT NULL auto_increment,
  `cat_name` varchar(55) NOT NULL default '',
  `category_nicename` varchar(200) NOT NULL default '',
  `category_description` longtext NOT NULL,
  `category_parent` int(4) NOT NULL default '0',
  PRIMARY KEY  (`cat_ID`),
  KEY `category_nicename` (`category_nicename`)
) TYPE=InnoDB AUTO_INCREMENT=27 ;

-- --------------------------------------------------------

-- 
-- Table structure for table `wp_comments`
-- 

CREATE TABLE `wp_comments` (
  `comment_ID` bigint(20) unsigned NOT NULL auto_increment,
  `comment_post_ID` int(11) NOT NULL default '0',
  `comment_author` tinytext NOT NULL,
  `comment_author_email` varchar(100) NOT NULL default '',
  `comment_author_url` varchar(200) NOT NULL default '',
  `comment_author_IP` varchar(100) NOT NULL default '',
  `comment_date` datetime NOT NULL default '0000-00-00 00:00:00',
  `comment_date_gmt` datetime NOT NULL default '0000-00-00 00:00:00',
  `comment_content` text NOT NULL,
  `comment_karma` int(11) NOT NULL default '0',
  `comment_approved` enum('0','1','spam') NOT NULL default '1',
  `comment_agent` varchar(255) NOT NULL default '',
  `comment_type` varchar(20) NOT NULL default '',
  `comment_parent` int(11) NOT NULL default '0',
  `user_id` int(11) NOT NULL default '0',
  PRIMARY KEY  (`comment_ID`),
  KEY `comment_approved` (`comment_approved`),
  KEY `comment_post_ID` (`comment_post_ID`)
) TYPE=InnoDB AUTO_INCREMENT=78 ;

-- --------------------------------------------------------

-- 
-- Table structure for table `wp_linkcategories`
-- 



-- --------------------------------------------------------

-- 
-- Table structure for table `wp_options`
-- 

CREATE TABLE `wp_options` (
  `option_id` bigint(20) NOT NULL auto_increment,
  `blog_id` int(11) NOT NULL default '0',
  `option_name` varchar(64) NOT NULL default '',
  `option_can_override` enum('Y','N') NOT NULL default 'Y',
  `option_type` int(11) NOT NULL default '1',
  `option_value` longtext NOT NULL,
  `option_width` int(11) NOT NULL default '20',
  `option_height` int(11) NOT NULL default '8',
  `option_description` tinytext NOT NULL,
  `option_admin_level` int(11) NOT NULL default '1',
  `autoload` enum('yes','no') NOT NULL default 'yes',
  PRIMARY KEY  (`option_id`,`blog_id`,`option_name`),
  KEY `option_name` (`option_name`)
) TYPE=InnoDB AUTO_INCREMENT=94 ;

-- --------------------------------------------------------

-- 
-- Table structure for table `wp_post2cat`
-- 

CREATE TABLE `wp_post2cat` (
  `rel_id` bigint(20) NOT NULL auto_increment,
  `post_id` bigint(20) NOT NULL default '0',
  `category_id` bigint(20) NOT NULL default '0',
  PRIMARY KEY  (`rel_id`),
  KEY `post_id` (`post_id`,`category_id`)
) TYPE=InnoDB AUTO_INCREMENT=427 ;

-- --------------------------------------------------------

-- 
-- Table structure for table `wp_postmeta`
-- 

CREATE TABLE `wp_postmeta` (
  `meta_id` bigint(20) NOT NULL auto_increment,
  `post_id` bigint(20) NOT NULL default '0',
  `meta_key` varchar(255) default NULL,
  `meta_value` text,
  PRIMARY KEY  (`meta_id`),
  KEY `post_id` (`post_id`),
  KEY `meta_key` (`meta_key`)
) TYPE=InnoDB AUTO_INCREMENT=20 ;

-- --------------------------------------------------------

-- 
-- Table structure for table `wp_posts`
-- 

CREATE TABLE `wp_posts` (
  `ID` bigint(20) unsigned NOT NULL auto_increment,
  `post_author` int(4) NOT NULL default '0',
  `post_date` datetime NOT NULL default '0000-00-00 00:00:00',
  `post_date_gmt` datetime NOT NULL default '0000-00-00 00:00:00',
  `post_content` longtext NOT NULL,
  `post_title` text NOT NULL,
  `post_category` int(4) NOT NULL default '0',
  `post_excerpt` text NOT NULL,
  `post_status` enum('publish','draft','private','static','object') NOT NULL default 'publish',
  `comment_status` enum('open','closed','registered_only') NOT NULL default 'open',
  `ping_status` enum('open','closed') NOT NULL default 'open',
  `post_password` varchar(20) NOT NULL default '',
  `post_name` varchar(200) NOT NULL default '',
  `to_ping` text NOT NULL,
  `pinged` text NOT NULL,
  `post_modified` datetime NOT NULL default '0000-00-00 00:00:00',
  `post_modified_gmt` datetime NOT NULL default '0000-00-00 00:00:00',
  `post_content_filtered` text NOT NULL,
  `post_parent` int(11) NOT NULL default '0',
  `guid` varchar(255) NOT NULL default '',
  `menu_order` int(11) NOT NULL default '0',
  PRIMARY KEY  (`ID`),
  KEY `post_name` (`post_name`),
  KEY `post_status` (`post_status`)
) TYPE=InnoDB AUTO_INCREMENT=433 ;

-- --------------------------------------------------------

-- 
-- Table structure for table `wp_users`
-- 

CREATE TABLE `wp_users` (
  `ID` bigint(20) unsigned NOT NULL auto_increment,
  `user_login` varchar(60) NOT NULL default '',
  `user_pass` varchar(64) NOT NULL default '',
  `user_firstname` varchar(50) NOT NULL default '',
  `user_lastname` varchar(50) NOT NULL default '',
  `user_nickname` varchar(50) NOT NULL default '',
  `user_nicename` varchar(50) NOT NULL default '',
  `user_icq` int(10) unsigned NOT NULL default '0',
  `user_email` varchar(100) NOT NULL default '',
  `user_url` varchar(100) NOT NULL default '',
  `user_ip` varchar(15) NOT NULL default '',
  `user_domain` varchar(200) NOT NULL default '',
  `user_browser` varchar(200) NOT NULL default '',
  `user_registered` datetime NOT NULL default '0000-00-00 00:00:00',
  `user_level` int(2) unsigned NOT NULL default '0',
  `user_aim` varchar(50) NOT NULL default '',
  `user_msn` varchar(100) NOT NULL default '',
  `user_yim` varchar(50) NOT NULL default '',
  `user_idmode` varchar(20) NOT NULL default '',
  `user_activation_key` varchar(60) NOT NULL default '',
  `user_status` int(11) NOT NULL default '0',
  `user_description` longtext NOT NULL,
  PRIMARY KEY  (`ID`),
  UNIQUE KEY `user_login` (`user_login`)
) TYPE=InnoDB AUTO_INCREMENT=4 ;

Posted: Thu Nov 10, 2005 1:10 pm
by ljCharlie
Thank you SO much for the code! I was thinking of using Wordpress...but as I read on, Wordpress is not a hosting software. At first I was looking for a software or service that will allow me to host my own blog community on my own website for unlimited users but I found that it's either too expensive or the software doesn't support it. If I want to create this myself, any suggestion on sources that will make this process easier. I'm very new to bloging and the requiremens, bells and whistles.

Posted: Fri Nov 11, 2005 7:00 am
by AbsoluteBreeze
Word press can be used as a multi hosting system.... http://mu.wordpress.org

You also have plogworld.net as an alternative and probably much more stable that the wordpress multiuser release(at this time)

However saying that - designing your own one would be much more fun :)

All the best of luck with it.

Posted: Fri Nov 11, 2005 8:49 am
by ljCharlie
Thank you for the suggestion. I looked at wordpress multi-user but there is basically not documentation and support. It is not even in the beta stage yet. So any idea on what is better between plogworld.net, nucleuscms.org, and blogcms.com?

Posted: Fri Nov 11, 2005 8:52 am
by AbsoluteBreeze
I havent tried the others you have mentioned - but I am looking for something like this so will test them over the next couple of weeks :)