drop table if exists items;
create table items (
  id                    integer       not null auto_increment,
  title                 varchar(500)  not null,
  title_normal          varchar(500)  null,
  subtitle              varchar(200)  null,
  author                varchar(200)  null,
  place                 varchar(100)  null,
  publisher             varchar(200)  null,
  isbn                  varchar(13)   null,
  callnumber            varchar(100)  null,
  small_cover           varchar(300)  null,
  medium_cover          varchar(300)  null,
  large_cover           varchar(300)  null,
  marc                  text          null,
  primary key(id)
);

drop table if exists subjects;
create table subjects (
  id                    integer       not null auto_increment,
  tag                   varchar(3)    not null,
  value                 varchar(200)  not null,
  normal                varchar(200)  not null,
  thesaurus_id          char(1)       null,
  primary key(id)
);

drop table if exists items_subjects;
create table items_subjects (
  item_id               integer       not null,
  subject_id            integer       not null,
  constraint fk_is_item foreign key (item_id) references items(id),
  constraint fk_is_subject foreign key (subject_id) references subjects(id)
);

drop table if exists subject_subfields;
create table subject_subfields(
  id                    integer       not null auto_increment,
  subject_id            integer       not null,
  position              integer       not null,
  code                  char(1)       not null,
  value                 varchar(100)  not null,
  primary key(id),
  constraint fk_subject foreign key (subject_id) references subjects(id)
);

drop table if exists authorities;
create table authorities(
  id                    integer       not null auto_increment,
  heading_type          char(3)       not null,
  heading               varchar(200)  not null,
  normal                varchar(200)  not null,
  primary key(id)
);

drop table if exists authority_fields;
create table authority_fields(
  id                    integer       not null auto_increment,
  authority_id          integer       not null,
  position              integer      not null,
  tag                   char(3)       not null,
  primary key(id),
  constraint fk_authority foreign key (authority_id) references
    authorities(id)
);

drop table if exists authority_subfields;
create table authority_subfields(
  id                    integer       not null auto_increment,
  authority_field_id    integer       not null,
  position              integer       not null,
  code                  char(1)       not null,
  value                 varchar(100)  not null,
  primary key(id),
  constraint fk_authority_field foreign key (authority_field_id) references
    authority_fields(id)
);