三期_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;
      

由于是單純的小項目。隻用于練習使用架構而練手的。表設計的不合理之處非常多。也沒有想那麼多。 

我是菜鳥。我在路上。