create table submitter_strings (
    submitterstrid integer unsigned not null,
    submitter varchar(60) not null,
    submitcounter integer not null
);
alter table submitter_strings add constraint pk_submitter_strings primary key (submitterstrid);
create unique index idu_submitter on submitter_strings (submitter);

create table artist_strings (
    artiststrid integer unsigned not null,
    artist varchar(60) not null
);
alter table artist_strings add constraint pk_artist_strings primary key (artiststrid);
create unique index idu_artist on artist_strings (artist);

create table album_sampler_strings (
    albumsamplerstrid integer unsigned not null,
    album varchar(60) not null
);
alter table album_strings add constraint pk_album_strings primary key (albumsamplerstrid);
create unique index idu_album on album_strings (album);

create table song_strings (
    songstrid integer unsigned not null,
    song varchar(120) not null
);
alter table songs_strings add constraint pk_song_strings primary key (songstrid);
create unique index idu_song on song_strings (song);

create table genre_strings (
    genrestrid integer unsigned not null,
    genre varchar(30) not null
);
alter table genre_strings add constraint pk_genre_strings primary key (genrestrid);
create unique index idu_genre on genre_strings (genre);

create table song2artist ( 
    songstrid integer unsigned not null,
    artiststrid integer unsigned not null,
    playtime integer unsigned not null
);
alter table song2artist add constraint pk_song2artist primary key (songstrid,artiststrid);
alter table song2artist add constraint fk_s2a_songstrid foreign key (songstrid) references song_strings (songstrid);
alter table song2artist add constraint fk_s2a_artiststrid foreign key (artiststrid) references artist_strings (artiststrid);

create table artist2album ( 
    artistid integer unsigned not null,
    albumsamplerstrid integer unsigned not null
);
alter table artist2album add constraint pk_artist2album primary key (artiststrid,albumsamplerstrid);
alter table artist2album add constraint fk_a2a_artiststrid foreign key (artiststrid) references artist_strings (artiststrid);
alter table artist2album add constraint fk_a2a_albumsamplerstrid foreign key (albumsamplerstrid) references album_sampler_strings (albumsamplerstrid);

create table song2artist2album2track (
    songstrid integer unsigned not null,
    artiststrid integer unsigned not null,
    albumsamplerstrid integer unsigned not null,
    track integer unsigned not null
);
alter table song2artist2album2track add constraint pk_song2artist2album2track primary key (songstrid,artiststrid,albumsamplerstrid,track);
alter table song2artist2album2track add constraint fk_s2a2a2t_songstrid foreign key (songstrid) references song_strings (songstrid);
alter table song2artist2album2track add constraint fk_s2a2a2t_artiststrid foreign key (artiststrid) references artist_strings (artiststrid);
alter table song2artist2album2track add constraint fk_s2a2a2t_albumsamplerstrid foreign key (albumsamplerstrid) references album_sampler_strings (albumsamplerstrid);

create table song2artist2genrevotes (
    songstrid integer unsigned not null,
    artiststrid integer unsigned not null,
    genrestrid integer unsigned not null,
    genrevotes integer unsigned not null
);
alter table song2artist2genrevotes add constraint pk_song2artist2genrevotes primary key (songstrid,artiststrid,genrestrid);
alter table song2artist2genrevotes add constraint fk_s2a2gvotes_songstrid foreign key (songstrid) references song_strings (songstrid);
alter table song2artist2genrevotes add constraint fk_s2a2gvotes_artiststrid foreign key (artiststrid) references artist_strings (artiststrid);
alter table song2artist2genrevotes add constraint fk_s2a2gvotes_genrestrid foreign key (genrestrid) references genre_strings (genrestrid);

create table cds (
    cdid integer unsigned not null,
    discid varchar(8) not null,
    albumsamplerstrid integer unsigned not null,
    playtime integer unsigned not null,
    submitterstrid  integer unsigned not null
);
alter table cds add constraint pk_cds primary key (cdid);
create unique index idu_cds on cds (discid,albumsamplerstrid);
alter table cds add constraint fk_cds_albumsamplerstrid foreign key (albumsamplerstrid) references album_sampler_strings (albumsamplerstrid);
alter table cds add constraint fk_cds_submitterstrid foreign key (submitterstrid) references submitter_strings (submitterstrid);

create table cds2year2genrevotes (
    cdid integer unsigned not null,
    year smallint unsigned not null,
    genrestrid integer unsigned not null,
    votes integer unsigned not null
);
alter table cds2year2genrevotes add constraint pk_cds2year2genrevotes primary key (cdid,year,genrestrid);
alter table cds2year2genrevotes add constraint fk_c2y2gvotes_cdid foreign key (cdid) references cds (cdid);
alter table cds2year2genrevotes add constraint fk_c2y2gvotes_genrestrid foreign key (genrestrid) references genre_strings (genrestrid);

create table cdtracks (
    cdid integer unsigned not null,
    songstrid integer unsigned not null,
    artiststrid integer unsigned not null,
    albumsamplerstrid integer unsigned not null,
    track smallint unsigned not null,
    offset integer unsigned not null
);
alter table cdtracks add constraint pk_cdtracks primary key (cdid,songstrid,artiststrid,albumsamplerstrid);
alter table cdtracks add constraint fk_cdtracks_songstrid foreign key (songstrid) references song_strings (songstrid);
alter table cdtracks add constraint fk_cdtracks_artiststrid foreign key (artiststrid) references artist_strings (artiststrid);
alter table cdtracks add constraint fk_cdtracks_albumsamplerstrid foreign key (albumsamplerstrid) references album_sampler_strings (albumsamplerstrid);