010 Database Design-BOS

/*==============================================================*/
/* DBMS Name:mysql 5.0 */
/* Created ON:2017/8/15 16:19:44 */
/*==============================================================*/

drop table if exists bc_decidedzone;

drop table if exists bc_region;

drop table if exists bc_staff;

drop table if exists Bc_subarea;

/*==============================================================*/
/* Table:bc_decidedzone */
/*==============================================================*/
CREATE TABLE Bc_decidedzone
(
ID varchar (+) NOT NULL,
Name varchar (30),
staff_id varchar (32),
Primary KEY (ID)
);

/*==============================================================*/
/* Table:bc_region */
/*==============================================================*/
CREATE TABLE Bc_region
(
ID varchar (+) NOT NULL,
Province varchar (50),
City varchar (50),
District varchar (50),
Postcode varchar (50),
Shortcode varchar (30),
Citycode varchar (30),
Primary KEY (ID)
);

/*==============================================================*/
/* Table:bc_staff */
/*==============================================================*/
CREATE TABLE Bc_staff
(
ID varchar (+) NOT NULL,
Name varchar (20),
Telephone varchar (20),
Haspda char (1),
Deltag char (1),
Station varchar (40),
Standard varchar (100),
Primary KEY (ID)
);

/*==============================================================*/
/* Table:bc_subarea */
/*==============================================================*/
CREATE TABLE Bc_subarea
(
ID varchar (+) NOT NULL,
decidedzone_id varchar (32),
region_id varchar (32),
Addresskey varchar (100),
Startnum varchar (30),
Endnum varchar (30),
Single char (1),
Position varchar (255),
Primary KEY (ID)
);

ALTER TABLE Bc_decidedzone add constraint fk_decidedzone_staff foreign key (staff_id)
References Bc_staff (ID) on the delete restrict on update restrict;

ALTER TABLE Bc_subarea add constraint fk_area_decidedzone foreign key (decidedzone_id)
References Bc_decidedzone (ID) on the delete restrict on update restrict;

ALTER TABLE Bc_subarea add constraint fk_area_region foreign key (region_id)
References Bc_region (ID) on the delete restrict on update restrict;

010 Database Design-BOS