Blogger database design structure
Moderator: General Moderators
Blogger database design structure
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.
- Chris Corbyn
- Breakbeat Nuttzer
- Posts: 13098
- Joined: Wed Mar 24, 2004 7:57 am
- Location: Melbourne, Australia
For a basic blogging system I'd have at least the following 3 tables. Users, Blogs, Blog_replies.
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.
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
)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 ;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.
-
AbsoluteBreeze
- Forum Newbie
- Posts: 3
- Joined: Fri Nov 11, 2005 6:55 am
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.
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.
-
AbsoluteBreeze
- Forum Newbie
- Posts: 3
- Joined: Fri Nov 11, 2005 6:55 am