Content Comparison

...

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_id int not null,
    user_id int not null,
    primary key(role_id, user_id),
    FOREIGN KEY (role_id) REFERENCES role(id) ON DELETE CASCADE,
    FOREIGN KEY (user_id) REFERENCES user(id) ON DELETE CASCADE
) Engine=InnoDB;