Database Organization

Holiday Tables

These tables organize the different holidays and their respective types.  Holiday table for the holidays, type table for the different holiday types, and holiday_type to link the different holidays to their types.

Create database holidays;

create table holiday (
    id int not null auto_increment,
    description varchar(63) not null,
    official_date datetime not null,
    observed_date datetime not null,
    primary key(id, observed_date),
    index off_idx(official_date),
    index obs_idx(observed_date)
) Engine=InnoDB;

create table type (
    id int not null auto_increment,
    description varchar(63) not null,
    primary key(id)
) Engine=InnoDB;

insert into type (description) values ("Federal");
insert into type (description) values ("State");
insert into type (description) values ("UH");

create table holiday_type (
    holiday_id int not null,
    type_id int not null,
    primary key(holiday_id, type_id),
    index type_idx(type_id),
    FOREIGN KEY (holiday_id) REFERENCES holiday(id) ON DELETE CASCADE,
    FOREIGN KEY (type_id) REFERENCES type(id) ON DELETE CASCADE
) Engine=InnoDB;

User Tables

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 int not null auto_increment,
    account_expired boolean not null,
    account_locked boolean not null,
    enabled boolean not null,
    password varchar(255) not null,
    password_expired boolean not null,
    username varchar(255) not null,
    unique(username),
    primary key(id)
) Engine=InnoDB;

create table role (
    id int not null auto_increment,
    authority varchar(63) not null,
    unique(authority),
    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;