Phase three _day02_ database table design and development preparation

Database Scripts

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;--user basic information table (contains Super admin, Enterprise person, customer) CREATE table Crm_user_info (crm_id integer primary key,       /*id PRIMARY KEY */email VARCHAR2 (() not NULL,/*email as login account */passwords varchar2 (+) NOT NULL,/* Password use encryption */                      CNAME VARCHAR2 (50),/* User real name */Phone VARCHAR2 (25),/* phone */sex Integer, /* Gender */Age Integer,/* ages */address VARCHAR2 (200),/* addresses */userlevel Integer NOT NULL,/* User level 0:A 1:b 2:c 3:d */PL integer NOT NULL,/* System permission Level 3 2 1 0*/Buss               iness_id integer NOT NULL,/* Enterprise id*/createdate date); /* Last login time */create sequence seq_crm_user_info_seq INCREMENT by 1 START WITH 1001;--Employee-Customer Relationship Table CREATE table crm_work_customer_relation (Crm_cid integer NOT null, Crm_eid integer NOT NULL);--Enterprise table Create Table crm_business (business_id Integer PRIMARY key,/* Enterprise id*/business_name varchar2 () NOT NULL,/* Company Name * * Business_type VARCHAR2 ($) not null); /* Business scope */create sequence Seq_crm_business_seq INCREMENT by 1 START with 101;--itinerary table CREATE TABLE Crm_travel (t_id in             Teger primary KEY,/* stroke serialization self-increment id*/c_id integer NOT NULL,/* client id*/e_id integer NOT NULL,              /* Employee id*/State integer NOT NULL,/* Status finish:0 ready:1 */t_time date NOT NULL, /* Schedule Time */Mission VARCHAR2 (+) NOT NULL,/* Task specific */T_type integer NOT NULL,/* Type (phone or petition) call:0 g         o:1*/address VARCHAR2 (200),/* Petition addresses */C_remark VARCHAR2 (1000)); /* Remarks */create sequence seq_crm_travel_seq INCREMENT by 1 START with 1001;--Contact record form CREATE TABLE Crm_contact_log (cl_id Integer primary key,/* record number auto 1*/T_type integer NOT NULL,/* record type (phone 0 or petition 1) */c_id integer n OT NULL,/* client id*/e_id integer NOT NULL,/* Employee id*/C_record VARCHAR2 (2000),/* record           Contents */C_result VARCHAR2 ($) NOT NULL,/* result */c_time date not NULL); /* Time */create sequence seq_crm_contact_log_seq INCREMENT by 1 START with 1001;--order form CREATE TABLE Crm_order (o_id inte            GER primary KEY,/* Order table ID self-increment */c_id integer NOT NULL,/* client id*/e_id integer NOT NULL,   /* Employee id*/Address VARCHAR2 (+) not NULL); /* Delivery address */create sequence Seq_crm_order_seq INCREMENT by 1 START with 1001;--Order Detail table CREATE table Crm_order_detail (od_i d Integer PRIMARY key,/* Order Detail table ID self-increment */o_id integer NOT NULL,/* Order table id*/g_id integer NOT NULL ,/* Product number */Od_num integer NOT NULL,/* Order quantity */Od_remark varchar2 ($) NOT NULL,/* TRADE Notes */      State integer is not NULL,/* status ok:0 ready:1 cancel:2 undefine:3*/odate date not NULL,            /* Order time */udate date not NULL); /* Order update time */create sequence Seq_crm_order_detail_seq INCREMENT by 1 START with 10001;--commodity table CREATE TABLE Crm_gods (g_i d Integer PRIMARY key,/* Product Code */G_name VARCHAR2 NOT NULL,/* name */g_color VARCHAR2 () not NULL,/ * Color */g_size varchar (+) NOT NULL,/* specification */G_price number NOT NULL,/* original price */g_rprice number not     NULL); /* out selling price */create sequence seq_crm_gods_seq INCREMENT by 1 START with 1001;--rating table for customer CREATE TABLE Crm_judge (j_id intege R primary key, c_id integer NOT NULL,/* client id*/e_id integer NOT NULL,/* Employee id*/J  _comment VARCHAR2 (+) not NULL);  /* Rating content */create sequence seq_crm_judge_seq INCREMENT by 1 START with 1001;--recommendation table for CREATE TABLE Crm_advice (a_id integer       Primary key,/* Recommended table primary key ID, self-increment one */  A_time date NOT NULL,/* Suggested time */e_id integer NOT NULL,/* Client id*/a_advice varchar       2 (+) NOT NULL,/* Suggested content */business_id integer NOT NULL);       /* Company id*/Create sequence Seq_crm_advice_seq INCREMENT by 1 START with 1001;--message form CREATE TABLE Crm_message (         m_id Integer primary key, c_id integer NOT NULL,/* client id*/business_id integer NOT NULL, /* Enterprise id*/m_message varchar2 (2000),/* contents */M_feedback VARCHAR2 (2000),/* feedback */M_isfeedback intege                R NOT NULL,/* has feedback ok:0 ready:1*/m_time date not null); /* Message time */create sequence Seq_crm_message_seq INCREMENT by 1 START with 10001;commit;

Because it is a simple small project, it is only used to practice using the framework and practiced hand. The design of the table is a lot of unreasonable, but also did not think so much.

I'm a rookie, I'm on my way.

Copyright NOTICE: This article for Bo Master original article, without Bo Master permission not reproduced.

Phase three _day02_ database table design and development preparation