三期_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;
由于是单纯的小项目。只用于练习使用框架而练手的。表设计的不合理之处非常多。也没有想那么多。
我是菜鸟。我在路上。
