数据库的建立，以及索引的创建

小组自发的发起一个活动，由我写服务，自己写过一个简单的数据库，在DBA审核时，被打回去了，这是经过和他商量后建立的数据库，很有借鉴力

#

##创建同城活动数据库
#
create database tong_cheng_db;
use tong_cheng_db;
##创建活动表
create table activity(
     id int not null auto_increment primary key                                   comment '活动id,主键,自增长型'
    ,user_id int unsigned not null default 0                                      comment '创建id'
    ,topic varchar(30) not null                                                   comment '活动话题'
    ,content varchar(200) not null                                                comment '活动内容'
    ,username varchar(20)                                                         comment '发起者的姓名'
    ,state tinyint                                                                comment '该活动是否过期（包括已经删除）'
    ,mobile int not null                                                          comment '发起者所留的电话'
    ,picture varchar(40)                                                          comment '发起者提供该活动的图片（可以为空）'
    ,alipay varchar(40)                                                           comment '发起者的支付宝账号'
    ,area_x float                                                                 comment '发起地的经度'
    ,area_y float                                                                 comment '发起地的维度'
    ,area_name varchar(30)                                                        comment '发起地的名称'
    ,city_id int unsigned not null default 2595                                   comment '城市Id默认为杭州id'
    ,update_time  timeStamp default current_timestamp on update current_timestamp comment'更新时间'
    ,create_time  datetime                                                        comment '创建时间'
    ,key idx_userid(user_id)                           
);
##创建活动的查询表
create table activity_join_stats(
    id int not null auto_increment primary key
    ,activity_id int unsigned not null default 0
    ,active_type  tinyint not null                             comment '该活动的类型'
    ,regist_count  int                                         comment '报名人数'
    ,interest_count int                                        comment '感兴趣人数'
    ,join_count  int                                           comment '参加人数'
    ,begin_time  dateTime not null                             comment '开始时间'
    ,end_time  datetime not null                               comment '截止时间'
    ,update_time  timeStamp default current_timestamp on update current_timestamp  comment'更新时间'
    ,create_time timestamp default current_timestamp
    ,key idx_active_type(active_type)                         
    ,key idx_end_time(end_time desc)                      
    ,key idx_create_time(end_time desc)                       
    ,key idx_activity_id ( activity_id )                       
    ,key idx_begin_end_time(begin_time,end_time)               
)

##创建活动表类型
create table activity_type(
    id int unsigned primary key not null ,
    name varchar(64),
    createtime timestamp default current_timestamp on update current_timestamp
)
##创建用户表
create table user(
    id int not null primary key                                                     comment'参加者的id'
    ,activity_id int unsigned not null default 0                                    comment'活动id,冗余字段'
    ,user_id int unsigned not null default 0                                     
    ,user_name  varchar(20)                                                         comment'参加者的姓名'
    ,mobile int not null                                                            comment'参加者的电话'
    ,state  tinyint                                                                 comment'参加者是否退出'
    ,alipay varchar(20)                                                             comment'参加者的支付宝账号'
    ,key idx_activity_id (activity_id)                                          
    ,key idx_userid(user_id)
);