William Jiang

JavaScript,PHP,Node,Perl,LAMP Web Developer – http://williamjxj.com; https://github.com/williamjxj?tab=repositories

mysql.sql

-- create database vanabc_mysql
-- used for English Version of http://www.vanabc.com

use vanabc_mysql;

/* grant all on vanabc_mysql.* to vanabc_mysql identified by 'password'; */
/*
 * There are 2 types of tables: static and dynamic:
 * static tables include: category, item, email. Each category includes several items.
 * dynamic tables include: post, post_calculate, post_picture.
 * usage: number -- static, count -- dynamic
 */

/*
 * 1. static parts. The exactly same with chinese version: vanabc.sql.
 */
DROP TABLE IF EXISTS category;
CREATE TABLE category
(
    category_id TINYINT(3) UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
    category_name VARCHAR(40) NOT NULL,     -- ignore binary: used for compared.
    category_comment VARCHAR(255) NULL,                -- category_name explain
    category_count     MEDIUMINT(4) UNSIGNED NOT NULL DEFAULT 0,       -- total post items
    update_datetime    TIMESTAMP NOT NULL,    -- latest update datetime
    create_date DATETIME
);

DROP TABLE IF EXISTS item;
CREATE TABLE item
(
    item_id TINYINT(3) UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
    item_name  VARCHAR(40) NOT NULL,
    item_comment VARCHAR(255) NULL,            -- item_name explain.
    category_name VARCHAR(40) NOT NULL,
    item_count MEDIUMINT(4) UNSIGNED NOT NULL DEFAULT 0,
    update_datetime  TIMESTAMP NOT NULL,
    create_date  DATETIME
);

DROP TABLE IF EXISTS visitor_info;
CREATE TABLE visitor_info
(
	v_id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
    v_addr VARCHAR(15) NULL,
	v_hits MEDIUMINT(4) UNSIGNED NOT NULL DEFAULT 0,
    v_time TIMESTAMP NOT NULL
);

DROP TABLE IF EXISTS post;
CREATE TABLE post
(
    post_id INT(6) UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
    item_name  VARCHAR(40) NOT NULL,
    category_name VARCHAR(40) NOT NULL,
    account_name VARCHAR(50) NOT NULL,
    title  VARCHAR(255) NOT NULL,
    contact VARCHAR(100) NOT NULL, 
    email    VARCHAR(50) NOT NULL,
    phone VARCHAR(20) NULL,
    address   VARCHAR(50) NULL,
    website  VARCHAR(50)  NULL,
    content TEXT NOT NULL,
    price  FLOAT(8,2) NULL,
	type	ENUM('S', 'B') NOT NULL DEFAULT 'B',
    add_picture  ENUM('Y','N') NOT NULL DEFAULT 'N',
	post_count		INT(4)	UNSIGNED NOT NULL DEFAULT 0,
    stop_mark     ENUM('Y','N') NOT NULL DEFAULT 'N',
    del_mark     ENUM('Y','N') NOT NULL DEFAULT 'N',
    create_date DATETIME,
    update_datetime  TIMESTAMP NOT NULL,
    expire_date  DATE,
	times INT(4) UNSIGNED NOT NULL DEFAULT '0',
    ranks TINYINT(1) UNSIGNED NOT NULL DEFAULT '0'
);

DROP TABLE IF EXISTS reply_post;
CREATE TABLE reply_post
(
   reply_post_id INT(4) UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
   post_id INT(6) UNSIGNED NOT NULL,
   account_name VARCHAR(50) NOT NULL, 
   contact  VARCHAR(100) NOT NULL,
   email VARCHAR(100) NULL,
   content TEXT NOT NULL,
   create_date DATETIME NOT NULL
);

/*
DROP TABLE IF EXISTS bbs;
create table bbs
(
    bbs_id INT(6) UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
    account_name VARCHAR(50) NOT NULL,
    email    VARCHAR(50) NOT NULL,
    title  VARCHAR(255) NOT NULL,
    contact VARCHAR(100) NOT NULL, 
    content TEXT NOT NULL,
	reply_times TINYINT UNSIGNED NOT NULL DEFAULT 0,
    click_times MEDIUMINT UNSIGNED NOT NULL DEFAULT 0,
    create_time DATETIME,
    update_datetime TIMESTAMP NOT NULL,
    expire_date  DATE,
	paste_mark enum('Y', 'N') not null default 'N'
);

DROP TABLE IF EXISTS bbs_reply;
create table bbs_reply
(
    bbs_reply_id INT(6) UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
	bbs_id INT(6) UNSIGNED NOT NULL,
    account_name VARCHAR(50) NOT NULL,
    email    VARCHAR(50) NOT NULL,
    title  VARCHAR(255) NOT NULL,
    contact VARCHAR(100) NOT NULL, 
    content TEXT NOT NULL,
    create_time DATETIME,
	deep_level VARCHAR(40) NOT NULL,
    expire_date  DATE,
	paste_mark enum('Y', 'N') not null default 'N'
);

-- for upload jpg or gif. available for multi-pics.
DROP TABLE IF EXISTS bbs_picture;
CREATE TABLE bbs_picture
(
	bp_id INT(4) UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
	bbs_id	INT(6) UNSIGNED NOT NULL,
	bbs_reply_id	INT(6) UNSIGNED NULL,
    account_name VARCHAR(50) NOT NULL,
	link_url	TINYTEXT NULL,
    create_date DATETIME NOT NULL
);
*/

DROP TABLE IF EXISTS picture;
CREATE TABLE picture
(
	picture_id INT(4) UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
	post_id	INT(6) UNSIGNED NOT NULL,
    account_name VARCHAR(50) NOT NULL,
	filename  VARCHAR(50) NOT NULL,
	remark	TEXT NULL,
	datetime TIMESTAMP NOT NULL
);

DROP TABLE IF EXISTS feedback;
CREATE TABLE feedback
(
 feedback_id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
 contact VARCHAR(100) NOT NULL,
 email VARCHAR(100) NULL,
 telephone VARCHAR(20)  NULL,
 content TEXT NOT NULL,
 del_mark ENUM('Y', 'N') NOT NULL DEFAULT 'N',
 create_date DATETIME NULL,
 expire_date DATE
);

DROP TABLE IF EXISTS content_feedback;
CREATE TABLE content_feedback
(
 cf_id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
 post_id INT(6) UNSIGNED NOT NULL,
 item_name  VARCHAR(40) NOT NULL,
 category_name VARCHAR(40) NOT NULL,
 account_name VARCHAR(50) NOT NULL,
 title  VARCHAR(255) NOT NULL,
 contact VARCHAR(100) NOT NULL,
 email VARCHAR(50) NULL,
 telephone VARCHAR(20)  NULL,
 content TEXT NOT NULL,
 del_mark ENUM('Y', 'N') NOT NULL DEFAULT 'N',
 create_date DATETIME NULL,
 expire_date DATE
);

DROP TABLE IF EXISTS search;
CREATE TABLE search
(
	search_id INT(4) UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
	search_str VARCHAR(255) NOT NULL,
	times INT(4) UNSIGNED NOT NULL DEFAULT '0',
	update_datetime TIMESTAMP NOT NULL
);


DROP TABLE IF EXISTS account;
CREATE TABLE account
(
    account_id INT(4) UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
    account_name VARCHAR(50) NOT NULL,
    email VARCHAR(50) NOT NULL,
    career VARCHAR(100) NULL,
    address VARCHAR(50) NULL,
    phone VARCHAR(20) NULL,
    interest VARCHAR(255) NULL,
	website VARCHAR(50) NULL,
	messege VARCHAR(255) NULL,
	words VARCHAR(100) NULL,
	count TINYINT(0) UNSIGNED NOT NULL DEFAULT "0",
    rank TINYINT(2) UNSIGNED NOT NULL DEFAULT "0",
    register_time TIMESTAMP NOT NULL,
    expiration DATE NULL DEFAULT "0000-00-00"
);


insert into category values (NULL, 'Secondhand Market', 'Second-hand free goods and trade marketing, including Commodity,Garage Sale,office equip, special goods etc', 0, NULL, Now() );

insert into category values (NULL, 'Housing', 'Housing buy-sell, renting, all the topic related to housing.', 0, NULL, Now());

insert into category values (NULL, 'Distinctive Ads', 'Special advertisements.', 0, NULL, Now() );

insert into category values (NULL, 'SME Business', 'Business Advertisements.', 0, NULL, Now() );

insert into category values (NULL, 'Auto / Traffic', 'Vehicle buy-sell, all the topic related to vehicle.', 0, NULL, Now());

insert into category values (NULL, 'Individual,Society',  'Personal stuff, community affairs', 0, NULL, Now());

insert into category values (NULL, 'Job Opportunity', 'Job related topics.', 0, NULL, Now() );


/* Secondhand Market */
insert into item values (100, 'Commodity', 'Second-hand marketing', 'Second-hand Market', 0, null, now());
insert into item values (null, 'Office Merchandise', 'Office Merchandise, equipments', 'Secondhand Market', 0, null, now());
insert into item values (null, 'Other used goods', 'Other used goods ads', 'Secondhand Market', 0, null, now());

/* Housing / House Property */
insert into item values (null, 'Short-term Living', 'Short-term renting, for travelers', 'Housing / House Property', 0, null, now());
insert into item values (null, 'Long-term Living', 'Long-term renting, homestay.', 'Housing / House Property', 0, null, now());
insert into item values (null, 'Buy / Sell', 'Housing buy-selling', 'Housing / House Property', 0, null, now());
insert into item values (null, 'Maintenance', 'Housing maintenance', 'Housing / House Property', 0, null, now());
insert into item values (null, 'Other Messages on Housing', 'Other Messages on Housing.',  'Housing / House Property', 0, null, now());

/* Distinctive Ads */
insert into item values (null, 'Interesting Ads', 'Special, interesting Advertisements', 'Distinctive Ads', 0, null, now());
insert into item values (null, 'Miscellaneous Distinctive Ads', 'Miscellaneous Distinctive advertisements', 'Distinctive Ads', 0, null, now());

/* SME Business */
insert into item values (null, 'Business Information', 'Business Information', 'SME Business', 0, null, now());
insert into item values (null, 'Convenient Service', 'Convenient Service', 'SME Business', 0, null, now());
insert into item values (null, 'Education and Training', 'Education and Training.', 'SME Business', 0, null, now());
insert into item values (null, 'Tourism', 'Tourism', 'SME Business', 0, null, now());
insert into item values (null, 'SOHO / SME', 'SOHO / SME', 'SME Business', 0, null, now());
insert into item values (null, 'Adult Erotic', 'Adult Erotics', 'SME Business', 0, null, now());
insert into item values (null, 'Miscellaneous Business Opportunity', 'Miscellaneous Business Opportunity.', 'SME Business', 0, null, now());

/* Auto / Traffic */
insert into item values (null, 'Auto Trade', 'Auto / Traffic', 'Auto / Traffic', 0, null, now());
insert into item values (null, 'Auto Renting', 'Auto-mobile Renting', 'Auto / Traffic', 0, null, now());
insert into item values (null, 'Auto Accessory / Maintenance', 'AUto Accessory and Maintenance.', 'Auto / Traffic', 0, null, now());
insert into item values (null, 'Miscellaneous Relative Service', 'Miscellaneous Relative Service.', 'Auto / Traffic', 0, null, now());

/*
 * Individual / Society (`item_id`, `item_name`, `item_comment`, `category_name`, `post_count`, `update_datetime`, `create_date`)
 */
insert into item values (null, 'Personal Topics', 'Personal Stuff',  'Individual / Society', 0, null,now());
insert into item values (null, 'Romance / Friendship', 'Romance, Friendship',  'Individual / Society', 0, null,now());
insert into item values (null, 'Public Events', 'Public Events',  'Individual / Society', 0, null, now());
insert into item values (null, 'Misc romance', 'Misc Romance',  'Individual / Society', 0, null, now());
insert into item values (null, 'Ask and Help', 'Ask and Help',  'Individual / Society', 0, null, now());

/* Job Opportunity */
insert into item values (null, 'Volunteer', 'Volunteer', 'Job Opportunity', 0, null, now());
insert into item values (null, 'Temporary Job', 'Temporary Job Opportunity',  'Job Opportunity', 0, null, now());
insert into item values (null, 'Part-time Job', 'Part-time Jobs',  'Job Opportunity', 0, null, now());
insert into item values (null, 'Full-time Job', 'full-time.',  'Job Opportunity', 0, null, now());
insert into item values (null, 'Resume Center', 'Resume Center.', 'Job Opportunity', 0, null, now());
insert into item values (null, 'Misc Job Opportunity', 'Other Job Opportunity',  'Job Opportunity', 0, null, now());

One response to “mysql.sql

  1. powercashadvance.com 10/26/2011 at 3:52 pm

    We highly appreciate your site post. You will find so many tactics we could put it to decent use with a minimum of effort in time and money. Thank you very much regarding helping have the post answer many concerns we have got before now.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: