...
Create database holidays;
create table holiday (
id id int not null auto_increment,
description description varchar(63) not null,
official official_date datetime not null,
observed observed_date datetime not null,
primary primary key(id, observed_date),
index index off_idx(official_date),
index index obs_idx(observed_date)
) Engine=InnoDB;
create table type (
id id int not null auto_increment,
description description varchar(763) not null,
primary primary key(id)
) Engine=InnoDB;
insert into type (description) values ("Bank");
insert into type (description) values ("Federal");
insert into type (description) values ("State");
insert into type (description) values ("UH");
create table holiday_type (
holiday holiday_id int not null,
type type_id int not null,
primary primary key(holiday_id, type_id),
index index type_idx(type_id),
FOREIGN FOREIGN KEY (holiday_id) REFERENCES holiday(id) ON DELETE CASCADE,
FOREIGN FOREIGN KEY (type_id) REFERENCES type(id) ON DELETE CASCADE
) Engine=InnoDB;
...
These tables are similar to the holiday tables in that there is one table for users, one table for user roles (admin and user), and one table connecting users to their role.
create table user (
id id int not null auto_increment,
account account_expired boolean not null,
account account_locked boolean not null,
enabled enabled boolean not null,
password password varchar(255) not null,
password password_expired boolean not null,
username username varchar(255) not null,
unique unique(username),
primary primary key(id)
) Engine=InnoDB;
create table role (
id id int not null auto_increment,
authority authority varchar(25563) not null,
unique unique(authority),
primary primary key(id)
) Engine=InnoDB;
insert into role (authority) values ("ROLE_ADMIN");
insert into role (authority) values ("ROLE_USER");
create table user_role (
role role_id int not null,
user user_id int not null,
primary primary key(role_id, user_id),
FOREIGN FOREIGN KEY (role_id) REFERENCES role(id) ON DELETE CASCADE,
FOREIGN FOREIGN KEY (user_id) REFERENCES user(id) ON DELETE CASCADE
) Engine=InnoDB;