When we create tables in database for saving data, we should use SQL command and syntax as below.
Syntax for creating database table
CREATE TABLE Table_Name(
Column Column_Type Optioin,
Column Column_Type Optioin,
.
.
);
Now that we have learned how to create tables in a database, Let's create tables for a blog program. You can use any other tools to create and manage tables in a database, such as MySQL Admin or NetBeans. Of course, MySQL or other DBMS should be installed correctly before we create a database and tables.
1. Member Table
- Information of member will be saved in this table.
-SQL Statements
CREATE TABLE member(
seq_num int(11) NOT NULL auto_increment,
id varchar(10) NOT NULL,
passwd varchar(10) NOT NULL,
email varchar(50) NOT NULL,
name varchar(20) NOT NULL,
phone varchar(13) NOT NULL,
reg_date datetime,
PRIMARY KEY(seq_num),
KEY id(id)
);
2. Blog List Table
- If someone register and create blog, ID, name, blog name, blog contents will be saved in this table.
-SQL statements
CREATE TABLE blog_list(
bnum int(11) NOT NULL auto_increment,
user_id varchar(12) NOT NULL,
nick_name varchar(50) NOT NULL,
blog_name varchar(100) NOT NULL,
blog_cont varchar(255),
blog_cdate date,
PRIMARY KEY(bnum),
KEY user_id_idx(user_id)
);
3. Blog_info table
- Image of blog name, profile, Information about color and all information about blog will be saved in this table except for information about blog in blog_list table
- SQL Statements
CREATE TABLE blog_info(
inum int(11) NOT NULL auto_increment,
user_id varchar(12) NOT NULL,
blog_logo char(1) DEFAULT 'N' NOT NULL,
blog_logo_ty varchar(5),
my_profile varchar(255),
title_bytype char(1) DEFAULT '1' NOT NULL,
title_bgcolor varchar(1),
title_bgimg char(1) DEFAULT 'N' NOT NULL,
title_bgimg_ty varchar(5),
box_bgcolor varchar(10),
main_img char(1) DEFAULT 'N' NOT NULL,
main_img_ty_ varchar(5),
main_text text,
main_text_bt text,
view_chk char(1) DEFAULT 'Y' NOT NULL,
PRIMARY KEY(inum)
);
4. Blog category table
- Blogs that users wrote are categorized and saved in this table.
Also, privilege to read or comment will be saved in this table.
- SQL Statements
CREATE TABLE blog_brd_list(
num int(11) NOT NULL auto_increment,
user_id varchar(12) NOT NULL,
brd_title varchar(50),
brd_pow_1 char(1) DEFAULT '3' NOT NULL,
brd_pow_2 char(2) DEFAULT '3' NOT NULL,
brd_wdate date DEFAULT '0000-00-00' NOT NULL,
PRIMARY KEY(num),
KEY user_id_idx(user_id)
);
5. blog Counter Table
- The number of people who visit a blog will be saved in this table.
- SQL Statements
CREATE TABLE blog_visit_count(
vnum int(11) NOT NULL auto_increment,
user_id varchar(12) NOT NULL,
visit_date varchar(8) NOT NULL,
visit_count int(8) DEFAULT '0' NOT NULL,
PRIMARY KEY(vnum),
KEY user_id(user_id),
KEY visit_date(visit_date)
);
6. The table for saving visitors of blog
- Users who visit a blog will be saved in this table.
- SQL Statements
CREATE TABLE blog_visit_member(
mnum int(11) NOT NULL auto_increment,
user_id varchar(12) NOT NULL,
visit_data varchar(8) NOT NULL,
visit_id varchar(12) NOT NULL,
PRIMARY KEY(mnum),
KEY user_id(user_id),
KEY visit_id(visit_id)
);
7. Blog information table for each user
- Basic information for each user will be saved in this table
The table will be created during execution dynamically
- SQL statements
CREATE TABLE bg_user id_t(
num int(6) NOT NULL auto_increment,
brd_list_fk int(11) DEFAULT '0' NOT NULL,
id_fk varchar(12) NOT NULL,
name varchar(30) NOT NULL,
title varchar(200) NOT NULL,
blog_img1 varchar(50),
contents_1 text,
blog_img2 varchar(50),
contents_2 text,
blog_img3 varchar(50),
contents_3 text,
comm_chk char(1) DEFAULT 'Y' NOT NULL,
wdate varchar(8) NOT NULL,
PRIMARY KEY(num)
);
8. The table for saving comments of blog
- All comments of blog will be saved in this table for each user.
This table will be created during execution dynamically
-SQL statements
CREATE TABLE bg_user id_ct(
cnum int(6) NOT NULL auto_increment,
num_fk int(11) DEFAULT '0' NOT NULL,
id_fk varchar(12) NOT NULL,
memo text NOT NULL,
cdate varchar(8) NOT NULL,
PRIMARY KEY(cnum)
);
