三期_day02_数据库表设计和开发准备工作

数据库脚本

drop table crm_user_info;
drop table crm_work_customer_relation;
drop table crm_business;
drop table crm_travel;
drop table crm_contact_log;
drop table crm_order;
drop table crm_order_detail;
drop table crm_gods;
drop table crm_judge;
drop table crm_advice;
drop table crm_message;


--用户基本信息表(包含超级管理员，企业人员，客户)
create table crm_user_info(
       crm_id integer primary key,      /*ID主键*/
       email varchar2(50) not null,     /*email作为登录账号*/
       passwords varchar2(50) not null, /*密码使用加密*/
       cname varchar2(50),              /*用户真实名*/
       phone varchar2(25),              /*电话*/
       sex integer,                      /*性别*/
       age integer,                     /*年龄*/
       address varchar2(200),           /*地址*/
       userlevel integer not null,      /*用户级别0:A   1:B   2:C   3:D */
       pl integer not null,             /*系统权限等级 3 2 1 0*/
       bussiness_id integer not null,   /*企业ID*/
       createdate date );               /*上次登录时间*/

create sequence seq_crm_user_info_seq  INCREMENT BY 1 START WITH 1001;

--员工-客户关系表
create table crm_work_customer_relation(
	 crm_cid integer not null,
	 crm_eid integer not null
);

--企业表
create table crm_business(
       business_id integer primary key,       /*企业ID*/
       business_name varchar2(50) not null,   /*企业名称*/
       business_type varchar2(200) not null ); /*企业经营范围*/  

create sequence seq_crm_business_seq  INCREMENT BY 1 START WITH 101;

--行程表
create table crm_travel(
      t_id integer primary key,          /*行程序列化自增ID*/
      c_id integer not null,             /*客户ID*/
      e_id integer not null,             /*员工ID*/
      state integer not null,            /*状态 finish:0  ready:1 */
      t_time date not null,              /*安排时间*/
      mission varchar2(1000) not null,   /*任务具体*/
      t_type integer not null,           /*类型(电话或者上访) call:0  go:1*/
      address varchar2(200),             /*上访地址*/
      c_remark varchar2(1000) );         /*备注*/

create sequence seq_crm_travel_seq  INCREMENT BY 1 START WITH 1001;

--联系记录表
create table crm_contact_log(
       cl_id integer primary key,         /*记录编号自增1*/
       t_type integer not null,           /*记录类型(电话0或者上访1)*/
       c_id integer not null,            /*客户ID*/
       e_id integer not null,            /*员工ID*/
       c_record varchar2(2000),          /*记录内容*/
       c_result varchar2(200) not null,  /*结果*/
       c_time date not null );           /*时间*/

create sequence seq_crm_contact_log_seq  INCREMENT BY 1 START WITH 1001;

--订单表
create table crm_order(
       o_id integer primary key,         /*订单表ID自增一*/
       c_id integer not null,            /*客户ID*/
       e_id integer not null,            /*员工ID*/
       address varchar2(200) not null );   /*收货地址*/

create sequence seq_crm_order_seq  INCREMENT BY 1 START WITH 1001;

--订单详细表
create table crm_order_detail(
       od_id integer primary key,        /*订单详细表ID自增一*/
       o_id integer not null,            /*订单表ID*/
       g_id integer not null,            /*商品编号*/
       od_num integer not null,          /*订货数量*/
       od_remark varchar2(200) not null, /*交易备注*/
       state integer not null,           /*状态 ok:0  ready:1  cancel:2  undefine:3*/
       odate date not null,              /*下单时间*/
       udate date not null );            /*订单更新时间*/

create sequence seq_crm_order_detail_seq  INCREMENT BY 1 START WITH 10001;

--商品表
create table crm_gods( 
       g_id integer primary key,       /*商品编号*/
       g_name varchar2(45) not null,   /*名称*/
       g_color varchar2(45) not null,  /*颜色*/
       g_size varchar(10) not null,    /*规格*/
       g_price number not null,        /*原价*/
       g_rprice number not null );     /*出售价*/


create sequence seq_crm_gods_seq  INCREMENT BY 1 START WITH 1001;

--对客户的评定表
create table crm_judge(
       j_id integer primary key,
       c_id integer not null,             /*客户ID*/
       e_id integer not null,             /*员工ID*/
       j_comment varchar2(2000) not null );  /*评价内容*/

create sequence seq_crm_judge_seq  INCREMENT BY 1 START WITH 1001;

--建议表
create table crm_advice(
       a_id integer primary key,            /*建议表主键ID，自增一*/  
       a_time date not null,                /*建议时间*/
       e_id integer not null,               /*客户ID*/
       a_advice varchar2(2000) not null,    /*建议内容*/
       business_id integer not null );       /*公司ID*/           

create sequence seq_crm_advice_seq  INCREMENT BY 1 START WITH 1001;

--留言表
create table crm_message(
       m_id integer primary key,
       c_id integer not null,                /*客户ID*/
       business_id integer not null,         /*企业ID*/
       m_message varchar2(2000),    		 /*内容*/
       m_feedback varchar2(2000),   		 /*反馈*/
       m_isfeedback integer not null,        /*是否已经反馈ok:0  ready:1*/
       m_time date not null );                /*留言时间*/ 
                  
create sequence seq_crm_message_seq  INCREMENT BY 1 START WITH 10001;
commit;

因为是单纯的小项目，仅仅用于练习使用框架而练手的。表设计的不合理之处很多，也没有想那么多。 

我是菜鸟，我在路上。