create database flowdemo;

use flowdemo;

--############################
--設定一個用戶上下級關系表
--userId  praentId hasChild

create table Relations
(
	relationId int auto_increment primary key,
	userId varchar(20) not null,
	parentId varchar(20) null,
	hasChild int not null
)

--############################
--一個用戶表
--userId userName userPass

create table Users
(
	userId varchar(20) not null primary key,
	userName varchar(20) not null,
	userPass varchar(20) not null
)

--############################
--一個報銷單表
--expenseId processInstanceId money reason userId

create table Expenses
(
	expenseId int auto_increment primary key,
	processInstanceId varchar(20) ,
	money varchar(20) not null,
	reason varchar(30) not null ,
	userId varchar(20) not null,
	foreign key(userId) references Users(userId)
)

--初始化腳本
--用戶初始化
insert into users(userId,userName,userPass) values('001','employee','employee');

insert into users(userId,userName,userPass) values('002','manager','manager');

insert into users(userId,userName,userPass) values('003','boss','boss');


--關系表初始化
--hasChild字段：1代表有下屬，0代表沒有
insert into relations(userId,parentId,hasChild) values('001','002',0);

insert into relations(userId,parentId,hasChild) values('002','003',1);

insert into relations(userId,parentId,hasChild) values('003','',1);