人力资源管理系统表格设计

-- Create table
create table H_BASIC
(
  yuangongid      VARCHAR2(10) not null,
  name            VARCHAR2(20) not null,
  sex             VARCHAR2(3) not null,
  height          NUMBER(3),
  cardid          NUMBER(18) not null,
  xueli           VARCHAR2(15) not null,
  depart          VARCHAR2(30) not null,
  birthday        DATE not null,
  school          VARCHAR2(50) not null,
  schooldate      DATE not null,
  zhuanye         VARCHAR2(30) not null,
  telphone        NUMBER(11) not null,
  minzu           VARCHAR2(12),
  zhuangtai       VARCHAR2(6) not null,
  zhengzhimianmao VARCHAR2(6),
  techang         VARCHAR2(21),
  address         VARCHAR2(80) not null,
  createtime      DATE,
  jibie           NUMBER(1) not null,
  "E-MAIL"        VARCHAR2(30),
  mima            VARCHAR2(10) not null,
  bankcard        NUMBER(20),
  contractid      VARCHAR2(10)
)
tablespace USERS
  pctfree 10
  initrans 1
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );
-- Add comments to the columns 
comment on column H_BASIC.yuangongid
  is '员工编号(规则)（主键）';
comment on column H_BASIC.name
  is '员工姓名';
comment on column H_BASIC.sex
  is '年龄';
comment on column H_BASIC.height
  is '身高';
comment on column H_BASIC.cardid
  is '身份证号';
comment on column H_BASIC.xueli
  is '学历';
comment on column H_BASIC.depart
  is '部门';
comment on column H_BASIC.birthday
  is '生日';
comment on column H_BASIC.school
  is '毕业院校';
comment on column H_BASIC.schooldate
  is '毕业时间';
comment on column H_BASIC.zhuanye
  is '专业';
comment on column H_BASIC.telphone
  is '电话';
comment on column H_BASIC.minzu
  is '民族';
comment on column H_BASIC.zhuangtai
  is '状态(1：在职/2：兼职/3：离职)';
comment on column H_BASIC.zhengzhimianmao
  is '政治面貌';
comment on column H_BASIC.techang
  is '特长';
comment on column H_BASIC.address
  is '住址';
comment on column H_BASIC.createtime
  is '创建时间';
comment on column H_BASIC.jibie
  is '级别';
comment on column H_BASIC."E-MAIL"
  is '邮件';
comment on column H_BASIC.mima
  is '密码';
comment on column H_BASIC.bankcard
  is '银行卡号';
comment on column H_BASIC.contractid
  is '合同编号';
-- Create/Recreate primary, unique and foreign key constraints 
alter table H_BASIC
  add constraint PK_YGID primary key (YUANGONGID)
  using index 
  tablespace USERS
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );
alter table H_BASIC
  add constraint FK_CONTRACTID foreign key (CONTRACTID)
  references H_CONTRACT (CONTRACTID);
-- Create/Recreate check constraints 
alter table H_BASIC
  add constraint SEX
  check (SEX='男'or SEX='女');

-- Create table
create table H_CONTRACT
(
  yuangongid      NUMBER(6) not null,
  contractid      VARCHAR2(10) not null,
  starttime       DATE not null,
  overtime        DATE not null,
  shiyongtime     VARCHAR2(15) default 3,
  shiyongmoney    NUMBER(6,2) not null,
  zhuanzhengmoney NUMBER(6,2) not null,
  contracttype    VARCHAR2(9)
)
tablespace USERS
  pctfree 10
  initrans 1
  maxtrans 255;
-- Add comments to the columns 
comment on column H_CONTRACT.yuangongid
  is '员工工号';
comment on column H_CONTRACT.contractid
  is '合同编号(主键)';
comment on column H_CONTRACT.starttime
  is '合同开始时间';
comment on column H_CONTRACT.overtime
  is '合同结束时间';
comment on column H_CONTRACT.shiyongtime
  is '试用期';
comment on column H_CONTRACT.shiyongmoney
  is '试用期工资';
comment on column H_CONTRACT.zhuanzhengmoney
  is '转正后工资';
comment on column H_CONTRACT.contracttype
  is '合同类型(1：签合同/2：续合同)';
-- Create/Recreate primary, unique and foreign key constraints 
alter table H_CONTRACT
  add constraint PK_CONTRACTID primary key (CONTRACTID)
  using index 
  tablespace USERS
  pctfree 10
  initrans 2
  maxtrans 255;

-- Create table
create table H_HUSER
(
  userid    VARCHAR2(21) not null,
  password  VARCHAR2(21) not null,
  username  VARCHAR2(15) not null,
  zhuangtai VARCHAR2(1)
)
tablespace USERS
  pctfree 10
  initrans 1
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );
-- Add comments to the columns 
comment on column H_HUSER.userid
  is '登录名';
comment on column H_HUSER.password
  is 'µÇ½ÃÜÂë';
comment on column H_HUSER.username
  is '̞';
comment on column H_HUSER.zhuangtai
  is '人员状态(1：使用2：停用3：离职)';
-- Create/Recreate primary, unique and foreign key constraints 
alter table H_HUSER
  add constraint PK_USERID primary key (USERID)
  using index 
  tablespace USERS
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );

-- Create table
create table H_JIANGCHENG
(
  yuangongid NUMBER(10) not null,
  type       VARCHAR2(3) not null,
  time       DATE not null,
  title      VARCHAR2(21) not null,
  neirong    VARCHAR2(80),
  jcid       VARCHAR2(4) not null
)
tablespace USERS
  pctfree 10
  initrans 1
  maxtrans 255;
-- Add comments to the columns 
comment on column H_JIANGCHENG.yuangongid
  is '员工工号';
comment on column H_JIANGCHENG.type
  is '奖惩类别';
comment on column H_JIANGCHENG.time
  is '奖惩时间';
comment on column H_JIANGCHENG.title
  is '标题';
comment on column H_JIANGCHENG.neirong
  is '奖惩内容';
comment on column H_JIANGCHENG.jcid
  is '奖惩编号(主键)';
-- Create/Recreate primary, unique and foreign key constraints 
alter table H_JIANGCHENG
  add constraint PK_YUANGONG primary key (JCID)
  using index 
  tablespace USERS
  pctfree 10
  initrans 2
  maxtrans 255;

-- Create table
create table H_WORKING
(
  yuangongid NUMBER(6) not null,
  company    VARCHAR2(50) not null,
  depart     VARCHAR2(30) not null,
  worktype   VARCHAR2(30) not null,
  score      VARCHAR2(50) not null,
  starttime  DATE not null,
  overtime   DATE not null,
  workid     VARCHAR2(4) not null
)
tablespace USERS
  pctfree 10
  initrans 1
  maxtrans 255;
-- Add comments to the columns 
comment on column H_WORKING.yuangongid
  is '员工工号';
comment on column H_WORKING.company
  is '公司名称';
comment on column H_WORKING.depart
  is '部门';
comment on column H_WORKING.worktype
  is '工作内容';
comment on column H_WORKING.score
  is '业绩';
comment on column H_WORKING.starttime
  is '开始时间';
comment on column H_WORKING.overtime
  is '结束时间';
comment on column H_WORKING.workid
  is '履历id号';
-- Create/Recreate primary, unique and foreign key constraints 
alter table H_WORKING
  add constraint PK_WORDID primary key (WORKID)
  using index 
  tablespace USERS
  pctfree 10
  initrans 2
  maxtrans 255;