Phase three _day02_ database table design and development preparation

Source: Internet
Author: User

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

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.